Are there any possibilities for optimization group function Google Docs(App Script)?-CodePudding

I'm quite new in automating documents using app script. I have a dynamic spreadsheet (4000 rows) that updates every morning and have to re-group data with special conditions after that. So I have a script and setup time trigger. But it takes a lot of time for running this script( ~30 min) and I'm afraid if the amount of data will grow - it will be a lot of failures(because of timeout). I leave my script below. Could you please give me some advice, how can I optimize this script, so it'll work faster? Thank you in advance!

    function removeAllGroups() {
  let sheet = SpreadsheetApp.getActive().getSheetByName("sheet1");
  let lastRow = sheet.getDataRange().getLastRow();
  
  for (let row = 1; row < lastRow; row  ) {
    let depth = sheet.getRowGroupDepth(row);
    if (depth < 1) continue;
    sheet.getRowGroup(row, depth).remove();
  }
}

function groupRows() {
  const sheetName = "sheet1";
  
 
  const rootSheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  const levels = rootSheet.getRange("A2:A"   rootSheet.getLastRow()).getValues();
  
  
  levels.forEach(([a], i) => rootSheet.getRange(i   2, 1).shiftRowGroupDepth(a));
}

CodePudding user response：

Try this:

function removeAllGroups() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("sheet1");
  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 groupRows() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet1');
  const levels = sh.getRange(2, 1, sh.getLastRow() - 1).getValues().flat();
  levels.forEach((e, i) => sh.getRange(i   2, 1).shiftRowGroupDepth(e));
}

CodePudding user response：

I believe your goal is as follows.

You want to reduce the process cost of the functions of removeAllGroups and groupRows.

I thought that in your script when I saw your script, the process costs of sheet.getRowGroup(row, depth).remove() and rootSheet.getRange(i 2, 1).shiftRowGroupDepth(a) might be high. In this case, I would like to propose using Sheets API. I thought that when Sheets API is used, the process cost might be a bit reduced. When Sheets API is reflected in your script, it becomes as follows.

Sample script:

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

function removeAllGroups() {
  const sheetName = "sheet1";
  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 groupRows() {
  const sheetName = "sheet1";
  const ss = SpreadsheetApp.getActive();
  const rootSheet = ss.getSheetByName(sheetName);
  const levels = rootSheet.getRange("A2:A"   rootSheet.getLastRow()).getValues();
  const sheetId = rootSheet.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());
}

Note:

When the above-modified scripts are not useful, can you provide the sample Spreadsheet? By this, I would like to modify the script.

Sample script:

Note:

References: