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?
If you don't like nested loops you can use more fancy tools:
Or even shorter, without
lastRow
andlastColumn
variables: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: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 into1A
. It's need a more complicated algorithm to keep inner empty cells intact. Implementation heavy depends on your data.In:
Out: