Google Sheet Error: Service Spreadsheets timed out while accessing document with id

Question

I get this error "Service Spreadsheets timed out while accessing document with id ..." every time I run a very simple code, in which I am basically copying data from one google sheet to another using getValues() and setValues().

I don't think it is because of 5M cells limit, because the same exact function is working perfectly fine in another Google Sheet with even bigger size. So I really don't understand where the problem is.

I have tried to create an empty GS and run the function, so I am only pulling data without any other calculation, but still, it gives me the same error.

Any idea what the reason could be?

Here the code as reference:

   function MyFunction(){
      var pm_ss_0 = SpreadsheetApp.openById('...');
      var pm_tab_0 = pm_ss_0.getSheetByName('...');
      var pm_data_0 = pm_tab_0.getDataRange().getValues();
      var target_ss_0 = SpreadsheetApp.getActiveSpreadsheet();
      var target_tab_0 = target_ss_0.getSheetByName('...');
      target_tab_0.clearContents();
      var target_data_0 = target_tab_0.getRange(1, 1, pm_data_0.length,   
      pm_data_0[0].length).setValues(pm_data_0);
      }

Heliotropism

Is your issue the same as this one? If so, I recommend you star the issue to receive updates. – Arhat

Yes the issue is similar, but I don't see any solution there. I will star it anyway, thanks – Heliotropism

Answer 1

I solved the issue inserting a flush before and after the line where the error appeared.

SpreadsheetApp.flush();
ss.insertSheet("Report "+fogl.getName(), ss.getNumSheets()); //line with the error in my code<br>
SpreadsheetApp.flush();

Thompson

TBH I didn't expect this to fix it. Thanks! – Trimming

This worked for me as well. I have a very long script with multiple functions and I just put the flush at the start of each function. – Douro

Answer 2

This issue has also been reported on Google's Issue tracker

Go there and star the issue so you get the updates on it.

Omniscience

Answer 3

This problem is more random than 95% of the commentary on the Web about it attests to. I just had this happen to me for the first time, and it even affected a Macro that did absolutely nothing but hide the Active Tab. I couldn't do anything with Script Editor.

I tried simply duplicating the document. BION, that was the end of the problem for me. Or at least, so far.

Scattering

I starred the issue. I tried flush, sleep and copy the sheet. None is working. – Hushaby

You duplicated the entire document and not just the tab? – Scattering

This worked for me. Made a copy of the entire Google Sheet and the function runs no issue. – Douro

Answer 4

As pointed out above, the problem appears quite randomly (copying the spreadsheet and running the same script is an easy way to fix the issue but might not be an option when the url is shared with external parties, for example)

For anyone struggling with the timeout error, I was able to fix a broken script by inserting Utilities.sleep(1000) into a for loop that was calling the Google Sheets API a lot. This worked where SpreadsheetApp.flush(); didn't.

Hope this helps someone!

Harbard

Answer 5

If you have alot of formulas/functions running in sheet which work based of data, then this sometimes also overloads it. set a flag for formulas to not work when you're updating datasets. this worked for me.

Koressa

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Straggle