I have a data set with multiple columns and rows (including text, numbers, dates etc). I want the following to happen:

1. Create a txt file for each row.
2. If a Column 1 has duplicate values, create one txt file for all rows with that value.
3. Check Column 2, create files based on column 2 joining onto column 1
4. Date format should be in DD/MM/YYYY
5. Exclude the first two columns
6. Don't need headings
7. The files created are named with Column 1 and 2 content

Example as below. Below is raw information table.

Raw Information Table

The query will create 8 Files, one for each employee.

• ABC will have 2 files with only one row in each file because his Department is different

• Benjamin will have 1 file named Benjamin Finance

• Timothy will have one file with BOTH rows named Timothy Marketing

• Charlene will have 1 file

• Tommy will have 1 file

• Jerry will have 1 file

• Lucy will have 1 file with BOTH rows

  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")));
  

  }

CodePudding user response：

In your script, in order to achieve your goal, how about the following modification?

From:

var csvBalanceArray = [, ...values.reduce((m, r) => m.set(r[0], m.has(r[0]) ? [...m.get(r[0]), r] :     [r]), new Map()).values()];

To:

var csvBalanceArray = [...values.reduce((m, [h1, h2, ...r]) => {
  var h = h1   h2;
  var temp = r.map(e => e instanceof Date ? Utilities.formatDate(e, Session.getScriptTimeZone(), "dd/MM/yyyy") : e);
  return m.set(h, m.has(h) ? [...m.get(h), temp] : [temp]);
}, new Map()).values()];

• When this modified script is used, the 1st 2 columns are removed. And, the date object is converted to dd/MM/yyyy. And, in this case, the header row is not included.

• From your reply of however, ABC should have 2 files created because although in Column 1 he is included twice, but in column 2 he is in Finance in row 1 and in Marketing in last row. So he should have two files., I updated the above script.