Google Apps script setValues() issue: timing out intermittently

Question

I have a Google Apps script that has been running without issues for 4 years. However, since 3 weeks I have this problem: the script is running for a very long time and failing. This happens every 3 out of 10 runs. The error message is “Service Spreadsheets timed out while accessing spreadsheet with id [spreadsheet id here]”.

The actual script, which is elaborate (thousands of lines) and runs on hundreds of spreadsheets takes the data using fetchUrl() and populates the sheet with setValues(). This actual script used to work fine on spreadsheets with 10 sheets and could update the 180k cells in each sheet without a problem for the past 4 years. Now, I can't update even one sheet.

The script below replicates this issue: it copies 1300 rows by 140 columns from Sheet1 to Sheet2 using .getValues() and .setValues().The script starts to fail when the number of rows is increased above 800. When it runs fine the execution logs show it takes 8 seconds. When it fails the logs show run times of up to 900 seconds. During that time, you can’t access the spreadsheet for more than 10 minutes, if you try to load the spreadsheet in a different tab it doesn’t load at all.

I have opened an issue with Google Support, I got no timeline, but profuse apologies for the inconvenience. This happens on all domains I have tried the script on, not only mine. You need to try running the script 10 times to see the failures.

I would greatly appreciate if someone could suggest a workaround or provide some insight about this issue.

Here is the link to the spreadsheet replicating the issue: https://docs.google.com/spreadsheets/d/1jea15rtjv85YIZumABMfFKESb2_QmX0-7zC-KchWeDc/edit?usp=sharing

function myFunction() {
  var row1 = 1;
  var col1 = 1;
  var row2 = 1300;
  var col2 = 140;
  console.log({numrows:row2, numcols:col2} );
  var rng = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange(row1,col1,row2,col2);
  var values_to_set = rng.getValues();
  var rng2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2").getRange(row1,col1,row2,col2);
  rng2.setValues(values_to_set);
  console.log('done');
  
}

Hmm you are copying 182k values. I guess it might take some time but I would expect it to finish within the time limit. If the file is already big enough it can impact the overall performance. — Marios, Nov 23 '20 at 22:08
Thank you for your comment, @Marios! It used to work fine on much larger worksheets, with 10 sheets of 180k cells each. Do you know of a quota on the number of cells that can be set with setValues()? The issue is not low performance, but **script failure** and the spreadsheet being inaccessible for more than 10 minutes. — Ninca Tirtil, Nov 23 '20 at 22:18
A workaround rather than an actual solution would simply be to create a copy of your file and use that instead. Hopefully this will fix the issue for some time. — Marios, Nov 23 '20 at 22:19
@Marios, I think it might not be clear from my explanation, but this is just a dummy script that replicates the issue. The actual code is much more elaborate and works on hundreds of spreadsheets. Maybe I should clarify that in my original question. — Ninca Tirtil, Nov 23 '20 at 22:23
I had similar results. I guess I probably would avoid doing this with a spreadsheet. — Cooper, Nov 23 '20 at 23:29
What are your intentions when running this function? I.e when copying your values from one sheet to another do you need to only copy them when you run this function or is this in a trigger that updates them on a certain time interval? What is stopping you from using [IMPORTRANGE](https://support.google.com/docs/answer/3093340) instead of an Apps Script script? — Mateo Randwolf, Nov 24 '20 at 09:11
Thank you for commenting, @MateoRandwolf. The actual script gets the data using fetchUrl and then populates the sheet. I wrote this script as an example of where the issue is, at setValues(). The data is much smaller than the 50 MB limit for fetchUrl and I never face an issue there. I can't use importrange instead of instead of fetchUrl, and the issue happens with setValues(). — Ninca Tirtil, Nov 24 '20 at 10:47
Is it possible you corrupted your spreadsheet somehow? Try a new spreadsheet perhaps? — TheMaster, Nov 24 '20 at 15:21
So in your sheet you are not copying a range from one sheet to another but rather importing with fetch in both sheets? So ```values_to_set = rng.getValues();``` would not come from a sheet but rather from a fetch ```values_to_set = FetchUrl.fetch()...````? — Mateo Randwolf, Nov 25 '20 at 10:29
Yes, @MateoRandwolf, I am using the UrlFetchApp class from Google Apps Script. I didn't want to share links to my db that's why I wrote this dummy script that replicates the problem with setValues(). I have a problem with setValues() only. — Ninca Tirtil, Nov 25 '20 at 12:37

score 10 · Accepted Answer · answered Nov 27 '20 at 08:22

According to this comparison of read/write methods, using advanced services to write is faster than setValues().

Using the following modified version of your original snippet worked for your sample spreadsheet:

function myFunction() {
  var row1 = 1;
  var col1 = 1;
  var row2 = 1300;
  var col2 = 140;
  Logger.log({numrows:row2, numcols:col2} );
  var rng = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange(row1,col1,row2,col2);
  var values_to_set = rng.getValues();
  var rng2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2").getRange(row1,col1,row2,col2);
  //rng2.setValues(values_to_set);
  // Based on https://developers.google.com/apps-script/advanced/sheets
  var request = {
    'valueInputOption': 'USER_ENTERED',
    'data': [
      {
        'range': 'Sheet2!' + rng2.getA1Notation(),
        'majorDimension': 'ROWS',
        'values': values_to_set
      }
    ]
  };
  Sheets.Spreadsheets.Values.batchUpdate(request, SpreadsheetApp.getActiveSpreadsheet().getId());
  Logger.log('done');
}

Thank you for your answer, @mshcruz . This has been the most helpful workaround so far. I didn't know I could make the Sheets API work from Google Apps Script, your example has been very helpful. I will test my code some more and if there are no other helpful answers I will check this as the answer. — Ninca Tirtil, Nov 27 '20 at 18:18

TheMaster · Answer 2 · 2022-11-03T14:40:42.873

This issue is already reported to Google in Issuetracker. Add a star(on top left) and +1(on top right) to the issue to request Google developers to prioritize the issue and fix it.

In the mean time, Consider using Advanced Google services using google-sheets-api to do massive operations on a spreadsheet.

The problem seems to stem from set* methods. Another alternative in your specific case would be to use range.copyTo(instead of getValues() and setValues()), which works without issues (tested upto 15 times)

/**@OnlyCurrentDoc*/
function myFunction() {
  var row1 = 1;
  var col1 = 1;
  var row2 = 1300;
  var col2 = 140;
  console.log({numrows:row2, numcols:col2} );
  var rng = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange(row1,col1,row2,col2);
  /*var values_to_set = rng.getValues();*/
  var rng2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2").getRange(row1,col1,row2,col2);
  /*rng2.setValues(values_to_set);*/
  /*Added*/rng.copyTo(rng2, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)
  console.log('done');
}


function test_myFunction(i=15){
  while(i--){
    myFunction();
  }
}

Thank you for your suggestions, @themaster . I did star the issue before I came here to look for a workaround. I also submitted a support request with Google, but after a couple of weeks of no news they told me they can't replicate it. I can't use the workaround suggested in your answer because I get my data from fetchUrl in production, not from another sheet. I added that edit to my original post. — Ninca Tirtil, Nov 30 '20 at 14:30
@NincaTirtil I see. My first suggested workaround is still using sheets api + advanced Google services, which was elaborated in the other answer. — TheMaster, Nov 30 '20 at 20:46

score 1 · Answer 3 · edited Nov 27 '20 at 16:16

1

There seems to be a problem with google v8 engine. I recently experienced same problem. after disabling v8 engine its working fine now.

Go to tools->script editor. In the script editor window, Click Run then disable v8 engine. See the attachment.

edited Nov 27 '20 at 16:16

TheMaster

45,448
6
62
85

answered Nov 27 '20 at 10:49

Mahendra Geete

11
1

According to [comment#4](https://issuetracker.google.com/issues/156530211#comment4) in the tracker, this also happens in [tag:rhino] engine. – TheMaster Nov 27 '20 at 16:11
Thank you, @Mahendra Geete. Unfortunately, this happens in both engines for us, too. – Ninca Tirtil Nov 27 '20 at 18:14
I got an improvement by disabling v8, so this answer is relevant. I still get the error occasionally, but less frequently than under v8. However, v8 provides other significant benefits, particularly a more recent version of javascript, so I've restored it and will have to persevere to fix the timeout problem there. Noting, however, that it is still (December 2020) an unresolved bug in https://issuetracker.google.com/issues/156530211 – Stephen Hosking Dec 08 '20 at 22:56

score 0 · Answer 4 · answered Mar 18 '22 at 17:44

The issue is not the code itself, is the type of account that runs it.

I have two spreadsheets, owned by my personal , p............@gmail.com, account. The script wrote for this 2 sheets to interact, is very simple, and consist on getting the range and values from sheet 1 (about 150000 cells) and setting the values on sheet 2. Now, if i try to run the script using my personal account, i get the "service spreadsheets timed out while accessing document with id ..." error. The error shows at about 140 seconds.

At work , i have been issued a corporate paid account , that uses my work email,, p.....@c..mi.com. I shared sheets 1 and 2, with editing privileges with this account,. When i run this same script, with this work paid account, the error never shows up, and the script finishes successfully after about 50 seconds.

That has not been the case for me. I have tried my personal account and my (paid) work account. The issue is intermittent, too, so to catch it you would have to try it tens of times. Last time I checked it was failing about 30% of the time. We haven't had this issue since we implemented @mshcruz 's solution above, it has been a life saver. — Ninca Tirtil, Mar 21 '22 at 13:57

score -1 · Answer 5 · answered Mar 24 '21 at 06:25

I had this same problem with inserting a dozen records into a sheet with 150k rows. It would read the first file, insert and then fail on the second or third. I had several tabs in this sheet doing some very complicated nested queries and arrayformula vlookups and had set the sheet settings to update every minute and on change.
My solution- which fixed the problem, was to make a copy of my original sheet with the complex queries and replace the source data tab with an importRange (pulling the data from the raw data sheet). I then removed all the other tabs from my raw data sheet. Keeping the raw data sheet isolated from the dynamic calculations. Short answer: remove tabs with complex queries, arrayformulas and vlookup to another sheet.

Google Apps script setValues() issue: timing out intermittently

5 Answers5

Linked

Related