Google Script Trigger Collapse Group Problem

Question

I have a problem with time triggers. I want groups in the sheets to collapse every 30 minutes. I record the macro and set the trigger in script editor.

This is my code:

function zwin() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A:H').activate();
  spreadsheet.getActiveSheet().getColumnGroup(9, 1).collapse();
};

I set the time trigger (every 1 minute to check if it works), and keep getting this problem:

Exception: A column group does not exist with index 9 and group depth 1

As you can see, the group index is correct: https://i.stack.imgur.com/VS9kG.png

But when I try to run the script manually, It works perfectly. It collapses the group I created. I keep getting this problem in every sheet with groups I create, the only difference is the group index (which I change in the code addording to its position).

I tried to delete the group and create it again, change the range in the code - nothing works.

What am I doing wrong and how to make it work?

[EDIT] All I had to do is to manually define the spreadsheet where I want to trigger the script. Added code:

 function zwin() {
  var spreadsheet = SpreadsheetApp.openById("myspreadsheetid");
  spreadsheet.getSheetByName("thesheetname").getColumnGroup(9, 1).collapse();
};

but I got this message because I just simply misspelled the id:

Unexpected error while getting the method or property openById on object SpreadsheetApp

Make sure you don't misspell the id which you can find here:

The id is the xxx part in https://docs.google.com/spreadsheets/d/XXX/edit#gid=0

openByUrl works as well:

function zwin() {
      var spreadsheet = SpreadsheetApp.openByUrl("myspreadsheeturl");
      spreadsheet.getSheetByName("thesheetname").getColumnGroup(9, 1).collapse();
    };

And it works!

One problem could be that ranges like 'A:H' tend to create nulls in google apps scripts between last row and max rows. Also it could be that running it from a trigger causes it to open the spreadsheet only on the server where there is no user interface. Why do you want to run it on a trigger when you not even looking at it. Perhaps it would make more sense to run it from an onOpen(). — Cooper
Acheived the result I wanted by manually defining the spreadsheet where I want to trigger the sript :) — Klaudia Stadnik

ziganotschka ziganotschka · Accepted Answer · 2021-04-19T10:44:22

When you run your function on time-based trigger, there is no active spreadsheet

A time-based trigger runs independtly of the spreadsheet
It also runs when the spreadsheet is closed (and thus there is no active spreadsheet)
Thus, you need to manually define on which spreadsheet and sheet you want to run the trigger
You can do it by e.g. calling the spreadsheet by its id and the sheet by its name

Sample:

function zwin() {
  var spreadsheet = SpreadsheetApp.openById("YOUR-SHEET-ID");
  spreadsheet.getSheetByName("NAME_OF_THE_SHEET").getColumnGroup(9, 1).collapse();
};

Google Script Trigger Collapse Group Problem

1 Answers

When you run your function on time-based trigger, there is no active spreadsheet