CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsrequest-timed-out
cate
cate

Reputation: 141

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

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);
      }

Upvotes: 12

Views: 21143

Answers (5)

Da Ni
Da Ni

Reputation: 196

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();

Upvotes: 11

Mohsin
Mohsin

Reputation: 1

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.

Upvotes: -1

user211309
user211309

Reputation: 119

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!

Upvotes: 1

Atiq Zabinski
Atiq Zabinski

Reputation: 133

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.

Upvotes: 4

Kessy
Kessy

Reputation: 2014

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

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

Upvotes: 4

Related Questions