Export multiple google sheet rows to multiple txt files based on criteria-CodePudding

If Column A has unique value, export that row to txt file, however, if Column A has duplicate values, export all those rows to ONE txt file.

For example,

Name       Department      Salary
Benjamin    IT     50000
Timothy      Finance      100000
Charlene    HR     80000
Timothy    Supply Chain   120000
Tom      Finance       60000

I'd like the query to build 4 txt files (1 for Benjamin, 1 for Timothy, 1 for Charlene and 1 for Tom). The file for Timothy will have 2 rows as Timothy is in the table twice.

Would appreciate any help. Thanks

I've tried the following but it only creates multiple txt files for ALL rows in the table. How can I add a criteria?

var folder = DriveApp.getFolderById('###')
var files = folder.getFiles();
while (files.hasNext()) files.next().setTrashed(true);
csvBalanceArray.forEach(function (row, index) {
  folder.createFile("row"   index   ".txt", row.join(" "));
});

CodePudding user response：

In your situation, how about the following modification?

Modified script:

Please copy and paste the following script to the script editor of Spreadsheet, and please set the sheet name and folder ID. And, save the script.

function myFunction() {
  // Retrieve values from Spreadsheet.
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); // Please set your sheet name.
  var [, ...values] = sheet.getDataRange().getValues();
  
  // Create an array.
  var csvBalanceArray = [...values.reduce((m, r) => m.set(r[0], m.has(r[0]) ? [...m.get(r[0]), r] : [r]), new Map()).values()];

  // Create text files.
  var folder = DriveApp.getFolderById('###'); // Please set your folder ID.
  var files = folder.getFiles();
  while (files.hasNext()) files.next().setTrashed(true);
  csvBalanceArray.forEach((row, index) => folder.createFile("row"   index   ".txt", row.map(r => r.join(" ")).join("\n")));
}

When this script is run, the values are retrieved from the Spreadsheet. And, an array for creating the text files is created. And then, the text files are created using the array to the folder.

Reference:

reduce()