Export spreadsheet as pipe-delimited file instead of csv

Question

I am trying to automate a Google Sheets -> pipe-delimited text file download. Basically, I want to create a time-driven script to run on a Google spreadsheet to save it as a .txt with | delimiters.

The spreadsheet itself is a single worksheet that uses IMPORTRANGE to pull data from my main file for the end-user to access.

Right now I manually download it as .csv, open it in notepad, "Replace all" commas with |'s, and save as .txt, and then upload that file where it needs to go (the upload is irrelevant, that has to be manual). I want to automate this for myself but also for another user to get that final .txt file via script when I am unavailable. Most importantly, I want this to be able to be run without user input or active workstations (it's supposed to be run on a Sunday morning) so it has to be in a time-triggered Google Apps Script.

I have very little JavaScript experience, so the only solution I can think of is two nested for loops (sheetID and sheetName withheld):

  var ss= SpreadsheetApp.openById(sheetID)
  var pipeDelimitedString = ""
  var sheet = ss.getSheetByName(sheetName)
  var lastRow = sheet.getLastRow()
  var lastColumn = sheet.getLastColumn()
  var fileName = "PIPE DELIMITED TEST FILE"
  for (var i=1;i<=lastRow;i++){
        for(var j=1;j<=lastColumn;j++){
            if (j==lastColumn){
                 pipeDelimitedString = pipeDelimitedString + "|" + sheet.getRange(i,j).getValue()+"/n"
            } else {
                 pipeDelimitedString = pipeDelimitedString + "|" + sheet.getRange(i,j).getValue()
            }
        }
}
newFile = DriveApp.createFile(fileName,pipeDelimitedString);

And then a small script to email the newFile to the other user (haven't wrote that part yet but I see plenty of solutions on google so don't worry about the emailing portion)

This has to run on a sheet of 66 columns and up to 200 rows (>10k total cells). I tested it on a dataset of only ~ 50 rows and I had to stop it. Even if it does work it would eat up far too much of my Google Apps Script quota for triggered scripts.

I'm sure I've gone the absolute wrong direction with this. Please help? Even just where to begin would be greatly appreciated.

I can't share the actual file, only a dummy copy, but I'm pretty sure the solution shouldn't be content-specific? Aside from the fact that there are no comma's or pipes anywhere in the original file data, only a mix of text and numbers if that helps?

Answer 1

If you don't like nested loops you can use more fancy tools:

var sheetID = "";
var sheetName = "";

function myFunction() {

  var ss         = SpreadsheetApp.openById(sheetID);
  var sheet      = ss.getSheetByName(sheetName);
  var lastRow    = sheet.getLastRow();
  var lastColumn = sheet.getLastColumn();
  var range      = sheet.getRange(1,1,lastRow,lastColumn).getValues(); <-- get all values

  var pipeDelimitedString = range.map(r => r.join("|")).join("\n"); <-- instead of loop

}

Or even shorter, without lastRow and lastColumn variables:

function myFunction() {

  var ss    = SpreadsheetApp.openById(sheetID);
  var sheet = ss.getSheetByName(sheetName);
  var range = sheet.getDataRange().getValues();
  var pipeDelimitedString = range.map(r => r.join("|")).join("\n");

}

The main point is to use getRange().getValues() for all cells at once.

UPDATE

To remove empty lines and cells outside the table you can apply replace() method to the string variable:

pipeDelimitedString = pipeDelimitedString.replace(/\|+\s*\|+/g, "");

This is a simpliest solution. But beware, the replace() method will get you wrong result in case your table has emply cells inside. 1| |A will turn into 1A. It's need a more complicated algorithm to keep inner empty cells intact. Implementation heavy depends on your data.

In:

Out:

Answer 2

function myFunction() {
  const ss = SpreadsheetApp.openById(gobj.globals.ssid);//Change your id
  let s = ""
  const sh = ss.getSheetByName('Sheet1');//change your sheet name
  let vs = sh.getRange(1,1,sh.getLastRow(),sh.getLastColumn()).getValues();//getting all of the data on the sheet
  vs.forEach(r=>{r.forEach(c=>{s+="|" + c;});s+='\n';});//two loops
  newFile = DriveApp.createFile("PIPE DELIMITED TEST FILE", s);//you could add a folder
}

The ascii file looks like this:

|COL1|COL2|COL3|COL4|COL5|COL6|COL7|COL8|COL9|COL10
|6|2|4|9|5|0|5|9|4|0
|5|1|5|7|1|2|5|7|0|0
|6|8|7|2|4|7|6|3|0|9
|7|5|7|6|3|2|7|7|0|0
|3|1|9|4|2|2|5|6|1|8
|7|8|8|3|2|7|1|6|3|0
|4|5|2|4|6|8|5|4|6|2
|9|3|6|7|5|7|9|9|0|6
|3|2|3|0|1|4|3|6|8|0
|9|0|0|2|8|0|0|2|4|7

For this sheet:

COL1	COL2	COL3	COL4	COL5	COL6	COL7	COL8	COL9	COL10
6	2	4	9	5	0	5	9	4	0
5	1	5	7	1	2	5	7	0	0
6	8	7	2	4	7	6	3	0	9
7	5	7	6	3	2	7	7	0	0
3	1	9	4	2	2	5	6	1	8
7	8	8	3	2	7	1	6	3	0
4	5	2	4	6	8	5	4	6	2
9	3	6	7	5	7	9	9	0	6
3	2	3	0	1	4	3	6	8	0
9	0	0	2	8	0	0	2	4	7