Exception: Service Spreadsheets timed out while accessing document with id {}

1,081 views
Skip to first unread message

Sunny Tran Thi Thuy

unread,
Jul 10, 2021, 11:06:50 PM7/10/21
to Google Apps Script Community
Hi there,
I am trying to custom sheet format, but I got error: "Exception: Service Spreadsheets timed out while accessing document with id {}" at line "var txtRange  = "A2:Z" + sheet.getLastRow();"
Before It used to run well for long time, but got this error in recent days. 

Do you have any ideas about this?  Many thanks.

Script:
"
function doGet(e) {
 return HtmlService.createHtmlOutput("Received request");
}
//function clearActiveSheet(){
 //SpreadsheetApp.getActiveSpreadsheet().getSheetByName("nirmil").clear();
 //SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").clear();
 //SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2").clear();
//this is a function that fires when the webapp receives a POST request
function doPost(e) {
  //var params = JSON.stringify(e.postData.contents);
  //params = JSON.parse(params);
  
  var myData = JSON.parse(e.postData.contents);
    
  var tab_name="EDU";

  Logger.log(typeof(tab_name))
  var itt = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(tab_name);

  if (!itt) {

   SpreadsheetApp.getActiveSpreadsheet().insertSheet(tab_name);
  }else {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(tab_name);
  }
    
  //auto resize all columns
  sheet.autoResizeColumns(1, 23);

  //hidden default to below column
  sheet.hideColumns(3);
  sheet.hideColumns(4);
  sheet.hideColumns(8);
  sheet.hideColumns(9);
  sheet.hideColumns(10);
  sheet.hideColumns(11);
  sheet.hideColumns(14);
  sheet.hideColumns(15);
  sheet.hideColumns(16);
  sheet.hideColumns(17);
  sheet.hideColumns(18);
  sheet.hideColumns(19);
  sheet.hideColumns(20);
  sheet.hideColumns(21);
  sheet.hideColumns(22);
  sheet.hideColumns(23);

  //sort column CREATED_AT
  var txtRange  = "A2:Z" + sheet.getLastRow();
  sheet.getRange(txtRange).sort({column: 1, ascending: true});

  SpreadsheetApp.flush(); 

  return HtmlService.createHtmlOutput("post request received");  
}
"

Harish IDC

unread,
Aug 10, 2023, 4:25:05 PM8/10/23
to Google Apps Script Community
Team,

I am also facing this issue of Spreadsheets timed out while accessing document when I am inserting  a new sheet and hiding it in the immediate step via script.
Can someone one guide me how to get out of this issue.


Thanks
Harish

Lisa Pettis -CO

unread,
Aug 11, 2023, 6:00:09 PM8/11/23
to Google Apps Script Community
Hi, 

I am also getting the Exception: Service Spreadsheets failed while accessing document with id error randomly.  It has failed 5 times since July 26th and the script runs on an hourly trigger.  I'm at a lost on what to check since it does run perfectly 99% of the time. 

Jose Guadarrama

unread,
Jun 5, 2025, 11:03:22 AM (8 days ago) Jun 5
to Google Apps Script Community
did you fix that?  i have the same problem

Ignacio Dominguez-Ramirez

unread,
Jun 9, 2025, 6:34:19 PM (3 days ago) Jun 9
to Google Apps Script Community
Hi Jose,

Looking through the thread, my guess is that it might be too many API calls at once. Looking at the OP's example, this part here:

  //hidden default to below column
  sheet.hideColumns(3);
  sheet.hideColumns(4);
  sheet.hideColumns(8);
  sheet.hideColumns(9);
  sheet.hideColumns(10);
  sheet.hideColumns(11);
  sheet.hideColumns(14);
  sheet.hideColumns(15);
  sheet.hideColumns(16);
  sheet.hideColumns(17);
  sheet.hideColumns(18);
  sheet.hideColumns(19);
  sheet.hideColumns(20);
  sheet.hideColumns(21);
  sheet.hideColumns(22);
  sheet.hideColumns(23) ...

And then the function before that, we don't know how much information is being received and processed. I use to encounter this error a lot for other APIs like Calendar when deleting events subsequently without any break in between. What we did at the time, which is NOT advisable, is we put Utilities.sleep(25) in between calls so that we didn't hit the API as hard, but that is a bandaid not a solution. It would be best to try and minimize the amount of API calls in the script especially if you anticipate receiving a lot of data into your doGet(e) function. One easy solution for this example is the sequential tabs that need to be hidden.

If we know for sure we need to hide particular columns (i.e. they aren't randomly selected), then for instance, we can minimize API calls by writing something like this:

  //hidden default to below column
  sheet.hideColumns(3);
  sheet.hideColumns(4);
  sheet.hideColumns(8);
  sheet.hideColumns(9);
  sheet.hideColumns(10);
  sheet.hideColumns(11);

// Because we know they are sequential we consolidate into two API calls
// Notice we are using .hideColumn NOT .hideColumns in order to hide a range
// This will start at column 14 and continue 10 places to column 23 and hide them
  sheet.hideColumn(sheet.getRange(1,14,1,10));

It is things like that that will help minimize timeouts. I hope that makes sense, and just be aware that this is only an example of the optimization that can be done to the script, there is also the function before this that I'm sure can be optimized for reduced API calls.
Reply all
Reply to author
Forward
0 new messages