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

Answer 1

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

Answer 2

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

Answer 3

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.

Answer 4

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

I have two spreadsheets, owned by my personal , [email protected], 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,, [email protected]. 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.

Answer 5

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.