10

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);
      }
Marios
  • 26,333
  • 8
  • 32
  • 52
cate
  • 121
  • 1
  • 1
  • 4
  • 1
    Is your issue the same as [this one](https://issuetracker.google.com/issues/156530211)? If so, I recommend you star the issue to receive updates. – ZektorH Oct 26 '20 at 10:51
  • Yes the issue is similar, but I don't see any solution there. I will star it anyway, thanks – cate Oct 26 '20 at 14:08

3 Answers3

7

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
SpreadsheetApp.flush();

Da Ni
  • 146
  • 1
  • 8
5

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

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

Kessy
  • 1,894
  • 1
  • 8
  • 15
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.

Atiq Zabinski
  • 113
  • 8