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.
CodePudding user response：
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.
You can report the issue here.
CodePudding user response：
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.