Split array values based on cumulative values in another column Google app script-CodePudding

I'm trying to find way with Google App Script to split column A of this spreadsheet when we reach cumulative value = 50 in column B. If the cumulative is not exact match, we need to stop each split at the last row in which we have cumulative value lower than 50. I added in column C the expected splitted arrays result.

Here is the sample spreadsheet : https://docs.google.com/spreadsheets/d/1_8ZRTxd64qbxCHrhwDoo4ugWHy7jG1VIKv8hHjtp3Bw/edit#gid=0

The final goal would be to store the values of each result Array in a text file, and upload in a Drive folder.

Thanks in advance for your help,

expected Array1 as example in a txt file

==========================================

Edit, from @Tanaike scripts, I updated the script like the following with no luck:

function test2() {
  var raw_values = SpreadsheetApp.getActive().getSheetByName("Sheet1").getRange("A2:J").getValues();
  var values = raw_values.map((x) => [x[0], x[2], x[4]])
  var destFolderID = "1Qq52QRpYYG_T2AxNWDz0rykZbAGhdpoe";
  var fileName = "sample";
  createTsv_new(values, destFolderID, fileName)
}

function createTsv_new(values, destFolderID, fileName) {
  const folderId = destFolderID; // Please set the folder ID you want to put the created files.
  const { res } = values.reduce((o, [s, a, b], i, v) => {
    o.tempC  = b;
    o.tempAr.push([s, a]);
    if (o.tempC   (v[i   1] ? v[i   1][4] : 0) > 50 || i == v.length - 1) {
      o.res.push(o.tempAr);
      o.tempAr = [];
      o.tempC = 0;
    }
    return o;
  }, { res: [], tempAr: [], tempC: 0 });
  if (res.length == 0) return;
  res.forEach((e, i) => DriveApp.getFolderById(folderId).createFile(fileName   (i   1)   ".tsv", e.join("\t")));
}

For now, the expected splitted file result is the following : updated expected file result

CodePudding user response：

In your situation, how about the following sample script?

Sample script:

function myFunction1() {
  const folderId = "root"; // Please set the folder ID you want to put the created files.

  // 1. Retrieve values from Spreadsheet.
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  const range = sheet.getRange("A1:B"   sheet.getLastRow());
  const [header, ...values] = range.getValues();
  
  // 2. Create an array including the separated rows.
  const { res } = values.reduce((o, [a, b], i, v) => {
    o.tempC  = b;
    o.tempAr.push(a);
    
    // Here, the rows are separated.
    if (o.tempC   (v[i   1] ? v[i   1][1] : 0) > 50 || i == v.length - 1) {
      o.res.push(o.tempAr);
      o.tempAr = [];
      o.tempC = 0;
    }
    return o;
  }, { res: [], tempAr: [], tempC: 0 });
  
  // 3. Create text files using the created array.
  if (res.length == 0) return;
  res.forEach((e, i) => DriveApp.getFolderById(folderId).createFile(`Array${i   1}.txt`, [header[0], ...e].join("\n")));
}

When this script is run for your provided Spreadsheet, the values are retrieved from the columns "A" and "B". And, create an array including the separated rows. And, using the array, the text files are created.
From your showing image, the filenames are like Array1.txt, Array2.txt,,,.
From your showing image, the header row is put to each text files. If you don't want to include the header, please modify [header[0], ...e].join("\n") to e.join("\n").

Note:

This sample script is for your provided Spreadsheet. So, when you change the Spreadsheet, this script might not be able to be used. Please be careful about this.

References:

Added:

About your following 2nd new question,

can you please help again, I change a bit the disposition of columns in the spreadsheet : docs.google.com/spreadsheets/d/…. I would like to include column A and column C in each final file, and the cumul is now based on column E. The final file would be a .TSV file. Can you please help again?

The sample script is as follows.

Sample script:

function myFunction2() {
  const folderId = "root"; // Please set the folder ID you want to put the created files.

  // 1. Retrieve values from Spreadsheet.
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  const range = sheet.getRange("A1:E"   sheet.getLastRow());
  const [header, ...values] = range.getValues();

  // 2. Create an array including the separated rows.
  const { res } = values.reduce((o, [a, , c, , e], i, v) => {
    o.tempC  = e;
    o.tempAr.push([a, c].join("\t"));

    // Here, the rows are separated.
    if (o.tempC   (v[i   1] ? v[i   1][4] : 0) > 50 || i == v.length - 1) {
      o.res.push(o.tempAr);
      o.tempAr = [];
      o.tempC = 0;
    }
    return o;
  }, { res: [], tempAr: [], tempC: 0 });

  // 3. Create text files using the created array.
  if (res.length == 0) return;
  res.forEach((e, i) => DriveApp.getFolderById(folderId).createFile(`Array${i   1}.txt`, [[header[0], header[2]].join("\t"), ...e].join("\n")));
}