Group rows with appscript-CodePudding

I have a sheet with more than 3000 rows and I want to group those rows by a parameter in ColA. So all rows having '1' in colA should be grouped under the row above with '0' in colA. Once groups are created I want them to collapse.

Since the script will be triggered daily upon data update, I also need the before-created groups to be removed so the new ones can be properly created.

I have several scrips doing what I need but it takes forever for them to go through all the rows. Is it possible to optimize them in some way or perhaps a different approach can be used for my needs? Thanks in advance for your help!

    function removeAllGroups1() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Pipeline");
  const rg = sh.getDataRange();
  const vs = rg.getValues();
  vs.forEach((r, i) => {
    let d = sh.getRowGroupDepth(i   1);
    if (d >= 1) {
      sh.getRowGroup(i   1, d).remove()
    }
  });
}

function groupRows1() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Pipeline');
  const levels = sh.getRange(2, 1, sh.getLastRow() - 1).getValues().flat();
  levels.forEach((e, i) => sh.getRange(i   2, 1).shiftRowGroupDepth(e));
}

function collapse() {
 const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Pipeline');
   let lastRow = sh.getDataRange().getLastRow();
  
  for (let row = 1; row < lastRow; row  ) {
    let depth = sh.getRowGroupDepth(row);
    if (depth < 1) continue;
    sh.getRowGroup(row, depth).collapse();
  }
}

Data Sample: https://docs.google.com/spreadsheets/d/10BNrnAyQw89gy-Sj3CLiz4AgVtFI0AjXTvc0REGGTfY/edit#gid=113574154

CodePudding user response：

I believe your goal is as follows.

You want to group the rows with high process speed.
You want to delete all groups with high process speed.
You want to collapse all groups with high process speed.

From the above goal, I thought that when Sheets API is used, the process cost can be reduced. And, Sheets API can group rows, delete all groups and collapse all groups.

Sample script:

Before you run this script, please enable Sheets API at Advanced Google services.

function removeAllGroups2() {
  const sheetName = "sample";
  const ss = SpreadsheetApp.getActive();
  const ssId = ss.getId();
  const sheetId = ss.getSheetByName(sheetName).getSheetId();
  const n = Sheets.Spreadsheets.get(ssId, { ranges: [sheetName] }).sheets[0].rowGroups.reduce((n, { depth }) => n < depth ? depth : n, 0);
  const requests = Array(n).fill("").map(_ => ({ deleteDimensionGroup: { range: { sheetId, dimension: "ROWS" } } }));
  Sheets.Spreadsheets.batchUpdate({ requests }, ssId);
}

function groupRows2() {
  const sheetName = "sample";
  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getSheetByName(sheetName);
  const levels = sheet.getRange("A2:A"   sheet.getLastRow()).getValues();
  const sheetId = sheet.getSheetId();
  const requests = levels.flatMap(([a], i) => Array(a).fill("").map(_ => ({ addDimensionGroup: { range: { sheetId, startIndex: i   1, endIndex: i   2, dimension: "ROWS" } } })));
  Sheets.Spreadsheets.batchUpdate({ requests }, ss.getId());
}

function collapse2() {
  const ss = SpreadsheetApp.getActive();
  const requests = Sheets.Spreadsheets.get(spreadsheetId, {ranges: ["sample"]}).sheets[0].rowGroups.map(r => {
    r.collapsed = true;
    return { updateDimensionGroup: { fields: "*", dimensionGroup: r }};
  });
  Sheets.Spreadsheets.batchUpdate({ requests }, ss.getId());
}

Note:

I tested these sample scripts using your sample Spreadsheet. So when the structure of the Spreadsheet is different from your sample Spreadsheet, these scripts might not be able to be used. Please be careful about this. At first, please test them using your sample script.

Sample script:

Note:

References: