Google Apps Script timing out when accessing sheet, but accomplishes tasks

David- 0
Viewed 56

I have a google script running that takes data from about 5 different spreadsheets and adds them to one centralized spreadsheet. The script gives a timeout error for accessing the centralized sheet after the approximate 5-6 minutes it takes for GAS to time itself out. The weird thing is; although it gives a timeout error when adding certain values to certain tabs, it still completes the task consistently. This is the error message:

Exception: Service Spreadsheets timed out while accessing document with id ##########.

with # signifying the ID of the centralized spreadsheet.

google-sheets google-apps-script javascript
2 Answers

I had this issue before with different sheets and for 3 different reasons:

The first one:

I had reached the actual limit for Google Sheets, so this made the sheet super slow and took some time to load, so I guess that when the script was run, it was not able to access the sheet information; creating the error message.

I tried to fix this by creating a new copy, and it did work for a little while. However, after a few days, it started to happen once more. To fully solved the issue, I had to split the data into 2 new sheets.

The second one:

The sheet didn't have as many cells as the other one, but it did a large amount of import ranges formulas, it took a while for the sheet to load when I opened it manually, and I got the same error with the script.

Making a copy of this issue actually worked for me this time, you can also try adding a Utilities.sleep(300000); after calling the URL of the Google Sheet to allow it to load and get the data, and after that reads the rest of the code.

The last one:

It was an actual bug and got fixed after a while. I found issue trackers about this.

Issue tracker 1

Issue tracker 2

Issue tracker 3

You can report the issue here.

Giselle Valladares0

Since we have to guess because you did not supply a script for us to evaluate I would guess that you are using a range like this sheet.getRange("A1:Z") which works fine in formulas but in google apps script that places a bunch of nulls between the last row in the sheet all the way to maxrows. So to correct this always use of these forms sheet.getRange(1,1,sheet.getLastRow(),26) or sheet.getRange("A1:Z" + sheet.getLastRow()); Of course this is just a guess. If you wish a better answer the please add more details to your question.

user30hu0
Related