Create grouping in Google Sheets

Question

I want to implement Groups in Google Sheets via API(appscript) as the direct method doesn't works dynamically. I have a column named levels(0-8) and then two more columns(other info). I want to write a script to make the groups. It will check the first column which has levels and if the next row has level more than the current i level, it will make a group of those rows until a row comes which has the same level or less than the i level. For example, levels are: 1,2,3,4,1,0,3,4. In this it will start from 1 and make the group of 2,3,4 as they are greater than 1. Skip 1,0 as they are equal or less than that and then make a group of 3,4. It will then run for 2 and do the same, make a group for 3,4 and skipping 1,0 and then make a group for 3,4.

Here is the link: https://docs.google.com/spreadsheets/d/1Ejbkl2imgEFi2mVwQ81xF5OkC97IXc4UcQIC3dxwPh4/edit?usp=sharing

Here's the code:

function myFunction() {
    const rootSheet = SpreadsheetApp.getActive().getActiveSheet();
    var r = rootSheet.getLastRow();
    for (var i = 3; i <= r; i++) {
        var t = 0;
        do {
            rootSheet.getRange(i,6).shiftRowGroupDepth(1);
            t = t + 1;
        } while (SpreadsheetApp.getActiveSheet().getRange(i,1).getValue() == t)

          }
          
          }

Here's how manually I have achieved grouping as per the pictures: https://drive.google.com/file/d/1JthF2ZJXgj5--0IOnW1LCM5Pneo9XUxJ/view?usp=sharing https://drive.google.com/file/d/1JthF2ZJXgj5--0IOnW1LCM5Pneo9XUxJ/view?usp=sharing

Tanaike Tanaike · Accepted Answer · 2020-09-14T08:56:10

Modification points:

In your script, it seems that the values from the column "A" in "Sheet1" are not used for the group depth.
In this case, I would like to propose the following flow.
1. Retrieve values from the column "A".
2. Set the group depth using the retrieved values.

Modified script:

function myFunction() {
  const sheetName = "Sheet1";
  
  // 1. Retrieve values from the column "A".
  const rootSheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  const levels = rootSheet.getRange("A2:A" + rootSheet.getLastRow()).getValues();
  
  // 2. Set the group depth using the retrieved values.
  levels.forEach(([a], i) => rootSheet.getRange(i + 2, 1).shiftRowGroupDepth(a));
}

Result:

When above script is used for your shared Spreadsheet, the following result is obtained.

Reference:

shiftRowGroupDepth(delta)