I would like to create a script, to be executed in a Google Sheets environment, that grabs all the contents of several columns within a specific tab, stacks the contents of each columns into a specific text file, and names the file after the header of each column.

For instance:

The working tab would be"Text_Files". It has several columns, for instance, 3, but there could be more: A:A, B:B and C:C. The value of the header for column A:A (cell A1) is "1". (Same as the column number) The value of the header for column B:B is "2". The value of the header for column C:C is "3".

Then, the script should grab the contents of cells A2:A, stack them into a text file, name the file "1.jsx", do the same for range B2:B, name this file "2.jsx" and for range C2:C name the file "3.jsx". (If there were more columns in the tab, the script should run until the very last column).

After having gathered all the data, it should pop up the dialog box asking the folder where to save all the .jsx files, in a similar ways as when we use the Pdf download option in he menu.

That's it. Thanks so much.

CodePudding user response：

It can be done this way:

function save_columns_into_files() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var folder = DriveApp.getFolderById('###');

  for (var column in data[0]) {
    var file_name = data[0][column];
    var file_body = data.slice(1).map(x => x[column]).join('\n')
    var txt_file = DriveApp.createFile(file_name   '.jsx', file_body, MimeType.PLAIN_TEXT);
    var pdf_file = DriveApp.createFile(file_name   '.pdf', file_body, MimeType.PDF);
    txt_file.moveTo(folder);
    pdf_file.moveTo(folder);
    SpreadsheetApp.getUi().alert('Files saved in folder '   folder.getName());
  }
}

No dialog box, though. Folder ID is wired in the script.

Add a proper dialog to select folders is a rather tricky task. Here is the official example: https://developers.google.com/apps-script/guides/dialogs#dialog.html

As a simpler variant it can be just a prompt dialog with text input area where you should type a name of desired output folder every time you run the script (keep in mind, Drive can have many folders with the same name, so there can be collisions). Let me know if you really need it.