Export Google spreadsheet to text file using script

AlexSell picture AlexSell · Apr 21, 2016 · Viewed 7.9k times · Source

I have a Google spreadsheet file containing just one sheet with one column. Everytime it is changed, it should be saved as a simple text (*.txt) file on my Google drive. So I would use the "on edit" trigger to run the script.

I found a few scripts that supposedly once did exactly what I need, but they seem to use outdated references, like this one which was posted two years ago. So my question is, how to export a sheet to text file now, in 2016?

Answer

user3717023 picture user3717023 · Apr 21, 2016

Here is a simple export function that works for me: it exports the current sheet as tab-delimited text file, whose name includes the date of creation.

You can set it to be triggered on edit using Resources > Current Project's Triggers in the Script Editor.

function export() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var values = sheet.getDataRange().getValues();
  var text = values.map(function (a) {return a.join('\t');}).join('\n');
  DriveApp.createFile('Data as of ' + new Date(), text);
}

Depending on your editor/OS, you may want join('\r\n') instead of join('\n') here.