Dynamic grouping in AppSript

Question

I have a Spreadsheet with about 3000 rows which are grouped by order number. I'm trying to build an macro to:

• remove all groups

• multisort all rows

• recreate groups

• collapse all groups marked as finished orders (optional - have no idea how to achieve this )

  SHEET_NAME = "PLAN";
  SORT_DATA_RANGE = "A2:CJ";
  GROUP_DATA_RANGE = "BQ2:BQ";
  SORT_ORDER = [
  {column: 40, ascending: false},
  {column: 2, ascending: true},  
  {column: 4, ascending: true} 
  ];
  
  function Sortowanie() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    removeAllGroups();
    multiSortColumns();
    groupRows();
    ss.toast('Zakończono.');
  }
  function multiSortColumns(){
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName(SHEET_NAME);
    var range = sheet.getRange(SORT_DATA_RANGE + sheet.getLastRow());
    range.sort(SORT_ORDER);
    ss.toast('Sortowanie zakończone.');
  }
  function removeAllGroups() {
    const ss = SpreadsheetApp.getActive();
    const ssId = ss.getId();
    const sheet = ss.getSheetByName(SHEET_NAME);
    const sheetId = sheet.getSheetId();
          sheet.expandAllRowGroups();
    const n = Sheets.Spreadsheets.get(ssId, { ranges: [SHEET_NAME] }).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);
    ss.toast('Usuwanie grup zakończone.');
  }
  function groupRows() {
    const ss = SpreadsheetApp.getActive();
    const sheet = ss.getSheetByName(SHEET_NAME);
    const levels = sheet.getRange(GROUP_DATA_RANGE + getLastRowSpecial()).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());
    ss.toast('Ponowne grupowanie zakończone.');
  }
  function getLastRowSpecial() {
    const ss = SpreadsheetApp.getActiveSpreadsheet()
    const sheet = ss.getSheetByName(SHEET_NAME);
    const lastRow = sheet.getRange(GROUP_DATA_RANGE).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
    return lastRow
  };
  

Everything works perfectly when I limit range to 1000 rows. When I try to run this for a whole range or more than 1000 and debuging, I'm getting this:

HttpResponseException: Response Code: 413. Message: response too large.

Without debugger after a while I'm getting this:

The JavaScript runtime has unexpectedly terminated.

Most bizarre thing is that script is creating these groups for a whole range and then error comes out after a while.

I think that 3K rows is not a big range for that, maybe someone has an idea what is wrong?

Here it is sample data sheet: https://docs.google.com/spreadsheets/d/1DLXxZVyrhDxrBe1AX3iy54nQTFVJkoIpeos7M9mEaIo/edit?usp=sharing

Answer 1

One of you problems but probably not the only problem

Range specified like this generate a lot of nulls between last row and max rows

"BQ2:BQ"

Try rewriting them like this:

"BQ2:BQ" + sheet.getLastRow()

Answer 2

Issue:

If I understand your situation correctly:

• You are able to remove existing groups and sort the rows according to the groups you want to create.
• The groups are defined by a column with 0s and 1s, so that 0 refers to group headers and continguous 1s should be grouped under the same group.
• I assume here that, after the column header (first row), all values in your column are 0 or 1.

Solution:

• Use slice and findIndex to find the successive group headers, looking iteratively for the values different than 1, so that all the rows in between are part of the same group.
• For each iteration, use the indexes of the current and the next header to build each request.

Code sample:

const FIRST_ROW = 2;
function groupRows() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(SHEET_NAME);
  const levels = sheet.getRange(GROUP_DATA_RANGE + getLastRowSpecial()).getValues().flat();
  const sheetId = sheet.getSheetId();
  const requests = [];
  let currentHeader = FIRST_ROW;
  while (currentHeader < levels.length) {
    let groupLength = levels.slice(currentHeader-FIRST_ROW+1).findIndex(l => l !== 1);
    if (groupLength < 0) groupLength = levels.length - currentHeader + 1;
    const nextHeader = groupLength + currentHeader+1;
    const request = { addDimensionGroup: { range: { sheetId, startIndex: currentHeader, endIndex: nextHeader-1, dimension: "ROWS" } } };
    requests.push(request);
    currentHeader = nextHeader;
  }
  Sheets.Spreadsheets.batchUpdate({ requests }, ss.getId());
  ss.toast('Ponowne grupowanie zakończone.');
}

Note:

Your other functions are not displayed here, but you should use them to remove existing rows and to make sure your data is sorted according to your preferences.