How to export specific columns to text files in Google Sheets

Question

I am trying to figure out how can I export the text from a specific column from Google Sheets to a Google Drive .txt file. I am trying to export all the text from all the columns so there would be a different .txt file on google drive for each column. Also would want to skip first 2 rows. I found this:

function saveToTextfile() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getActiveSheet();
  var range = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn());
  var rows = range.getValues();
  var folder = DriveApp.getFoldersByName("folderName").next();
  var files = folder.getFiles();
  while(files.hasNext()) files.next().setTrashed(true);
  rows.forEach(function(row, index) {
    folder.createFile("row" + index + ".txt", row.join(", "));
  });
}

This function is doing a text file for all the rows instead of columns and also adds a lot of "," in the resulted txt file. I can't figure it out how to change so it will do the columns, basically I want the function to create a txt file for columns (example: D3 to D100, E3 to E100, F3 to E100 and so on). Also want this to update the text files when any changes are made on the sheet.

Thanks :)

Answer 1

Transposing the array:

getValues() returns a 2D array where each element in the outer array corresponds to a row. You want to have a 2D array where the outer array elements correspond to columns instead. That is, you need to transpose the 2D array.

An easy way to do that can be found in this answer:

array[0].map((_, colIndex) => array.map(row => row[colIndex]));

Other issues:

• If you want to skip the first two rows, the starting row in your range (defined in getRange) should be 3 instead of 1, and correct the number of rows accordingly (check code sample below).
• It adds a lot of , because you wrote it that way (see row.join(", ")). If instead you want each value to occupy a new line, use \n instead (check code sample below).
• Updating the text files would require you to install an onEdit trigger which would fire the function saveToTextfile every time the spreadsheet is edited. Since this function requires authorization, it cannot be a simple trigger, but an installable one. If you have problems installing it, please post a new question (each question should deal with a specific issue, not with several of them).

Code sample:

function saveToTextfile() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getActiveSheet();
  var firstRow = 3; // Skip first two rows => start at 3rd
  var range = sheet.getRange(firstRow, 1, sheet.getLastRow() - firstRow + 1, sheet.getLastColumn());
  var rows = range.getValues();
  var columns = rows[0].map((_, colIndex) => rows.map(row => row[colIndex]));
  var folder = DriveApp.getFoldersByName("folderName").next();
  var files = folder.getFiles();
  while(files.hasNext()) files.next().setTrashed(true);
  columns.forEach(function(column, index) {
    folder.createFile("column" + index + ".txt", column.join("\n")); // New line
  });
}

Reference:

• Transposing a 2D-array in JavaScript