Google Apps Script: Google Sheet Grouping

Question

How do I stop the for loop from continuing once the script (below) reaches the last row with data?

How do I get each group to collapse, instead of only the first one?

I'm not sure how to incorporate the .getLastRow()-1 into the script. I need the numRows as 179 because that is the number of rows I want to group together, but just not sure how to finish the script. I modified one of the new "recorded macros" and worked backwards a bit. Additionally, the first group collapses as intended once it has been grouped, but the others are not. Any help would be appreciated!

function test2() {
var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getActiveSheet();
var currentRow = 3;
var startColumn= 1;
var numRows = 179;
var numColumns= sheet.getMaxColumns();
for (i = 0; i < numRows; i++) {
  sheet.getRange(currentRow, startColumn, numRows, numColumns).activate()
  .shiftRowGroupDepth(1);
  spreadsheet.getActiveSheet().getRowGroup(3, 1).collapse();
currentRow = currentRow+180
  }
};

I figured out how to fix the collapsing each group issue: spreadsheet.getActiveSheet().getRowGroup(currentRow, 1).collapse(); — N.O.Davis

N.O.Davis N.O.Davis · Accepted Answer · 2018-06-07T01:23:24

Ok so for anyone else that comes across this, here is what I found. I feel like there has to be an easier way than specifying the exact number of groups I ultimately want to make, but it works.

function test2() {
var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getActiveSheet();
var currentRow = 3;
var startColumn= 1;
var numRows = 179;
var numColumns= sheet.getMaxColumns();
var numGroups = 28;

for (i = 0; i < numGroups; i++) {
  sheet.getRange(currentRow, startColumn, numRows, numColumns).activate()
  .shiftRowGroupDepth(1);
  spreadsheet.getActiveSheet().getRowGroup(currentRow, 1).collapse();
  currentRow = currentRow+180
 }
};

Google Apps Script: Google Sheet Grouping

1 Answers