Export spreadsheet as pipe-delimited file instead of csv

2.1k views Asked by At

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?

2

There are 2 answers

5
Yuri Khristich On BEST ANSWER

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:

enter image description here

Out:

enter image description here

0
Cooper On
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