Optimizing Google Apps Script to Collapse Groups in Google Sheets?-CodePudding

I recorded a Macro on Google Sheets to have a button expand all selected groups. However, it takes a few seconds for the script to run, and I would prefer it to be instantaneous. Is there a way to make the script quicker?

function ExpandAll() {
  var spreadsheet = SpreadsheetApp.getActive();
  var ss = spreadsheet.getActiveSheet();
  spreadsheet.getRange('J1').activate();
  ss.getColumnGroup(12, 1).expand();
  ss.getColumnGroup(19, 1).expand();
  ss.getColumnGroup(30, 1).expand();
  ss.getColumnGroup(39, 1).expand();
  ss.getColumnGroup(59, 1).expand();
  ss.getColumnGroup(62, 1).expand();
};

I've looked up that calling the sheet might slow down the script, but I am not sure how to do this any other way.

CodePudding user response：

You can expand all groups with a single call, like this:

function expandAllGroups() {
  SpreadsheetApp.getActiveSheet().expandAllColumnGroups();
}

CodePudding user response：

Expand all:

function ExpandAll() {
  var sh = SpreadsheetApp.getActiveSheet();
  spreadsheet.getRange('J1').activate();
  [12,19,39,59,62].forEach(r => sh.getColumnGroup(r,1).expand())
};