How can I prevent my Apps Script code from timing out?

Question

I have the following code:

function onOpen() {
  const ss = SpreadsheetApp.getActive();
  ss.getSheets().forEach((sheet, index) => {
    if (index < 9) {
      const lastRow = sheet.getLastRow();
      const numRowsToHide = sheet.getMaxRows() - lastRow;
      if (numRowsToHide) sheet.hideRows(lastRow + 1, numRowsToHide);
      sheet.setActiveSelection('B4');
    } else {
      sheet.setActiveSelection('A1');
    }
    SpreadsheetApp.flush();
  });
  ss.getRange('Active(Date)!B4').activate();
}

It runs through 9 of 11 sheets of the spreadsheet and hides all the rows that don't have data and positions the active cell. It then sets the active sheet as the first one and sets the active cell. It works fine except the time runs around 29.5 sec give or take, so it occasionally times out (over 30 sec). Is there anything I can do to make it more time efficient?

doubleunary · Accepted Answer · 2025-04-21 08:43:50Z

1

The code quoted in the question is prone to time out, because it takes a lot of time to iterate through all tabs in a spreadsheet, and the onOpen(e) function is a simple trigger that is limited to a maximum of 30 seconds of runtime.

To increase the limit to six minutes, rename onOpen(e) to installableOnOpen(e) and add an installable "on open" trigger to run the function.

See Current limitations. To improve spreadsheet performance, see these optimization tips.

edited Apr 21 at 8:43

answered Apr 21 at 6:19

doubleunary

19.8k10 gold badges35 silver badges76 bronze badges

Add a comment |

Doc · Accepted Answer · 2025-04-21 00:02:46Z

0

Ok, I figured it. I went to the spreadsheet and went to each sheet. Some had 71 rows of data, but a little over 1000 total rows. This was pretty consistent along all the sheets. So I went to each one and deleted the majority of the empty rows. They are filled in with an importrange command, so as more is added they will fill in more rows, but for now the execution time went from 30+ sec and timing out, to 11.1 secs.

answered Apr 21 at 0:02

Doc

536 bronze badges

1

You can also use installable triggers
– Cooper
Commented Apr 21 at 2:26
I don't think that this solution answers the question as put. A significant element was that the script hides all the rows that don't have data. This answer resolves this by manually deleting most of the rows that don't have any data. Also, much of the answer appears to introduce new information that should have been included in question. Most importantly, no changes whatsoever would have been needed if, as both @doubleunary (and Cooper) point out, execution time was extended to six minutes simply by using an installable trigger.
– Tedinoz
Commented Apr 23 at 10:53

Add a comment |

Collectives™ on Stack Overflow

How can I prevent my Apps Script code from timing out?

2 Answers 2

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Related