Non-stop exceptions

157 views
Skip to first unread message

Jonathan A

unread,
Nov 27, 2023, 2:40:54 AM11/27/23
to Google Apps Script Community
Hello, 
I get non-stop exceptions of this kind:

Exception: Service Spreadsheets failed while accessing document with id [my sheet ID].

The important part is that sometimes the code runs without an issue, and sometimes it fails with this exception. I run this code every 1 minute (time-driven trigger) because it tries to fill in gaps in the data sheet.

If it always failed - it would mean my code is wrong, but it works fine some of the time, and some of the time it fails. Also - this only started lately. The same code ran many times without an issue before. My only guess is that my spreadsheet is large with a lot of data (9000 rows), and this causes an issue somehow?

function fill_in_agent_group_names() {
  var main_sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("income");
  var last_row = main_sheet.getLastRow();
  var last_row_str = last_row.toString();
  console.log("Last row: " + last_row_str);
  while(main_sheet.getRange(last_row, AGENT_GROUP_COL).getValue()===""){
    var income = main_sheet.getRange(last_row, INCOME_COL).getValue();
    var expenses = main_sheet.getRange(last_row, EXPENSES_COL).getValue();
    var currency = main_sheet.getRange(last_row, CURRENCY_COL).getValue();
    if(income || expenses){
      main_sheet.getRange(last_row, AGENT_GROUP_COL).setFormula("=IFERROR(VLOOKUP($Q" + last_row_str + ", 'info'!A:B, 2, FALSE), " + '"")');
      main_sheet.getRange(last_row, AGENT_SN_COL).setFormula("=IFERROR(VLOOKUP($Q" + last_row_str + ", 'info'!A:C, 3, FALSE), " + '"")');
      if (currency !== "ILS"){
        var now = new Date();
        main_sheet.getRange(last_row, ILS_COL).setFormula('=INDEX(GOOGLEFINANCE("CURRENCY:' + currency + 'ILS", "price", DATE(' + now.getFullYear().toString() + ',' + (now.getMonth()+1).toString() + ',' + now.getDate().toString() + ')), 2, 2) * E' + last_row_str);
      }
      else{
        main_sheet.getRange(last_row, ILS_COL).setValue(income);
      }
      var epoch = main_sheet.getRange(last_row, ORIGINAL_DATE_TIME_COL).getValue();
      if(Number.isInteger(epoch)){
        var timestamp = new Date(parseInt(epoch));
        main_sheet.getRange(last_row, ORIGINAL_DATE_TIME_COL).setValue(timestamp);
        main_sheet.getRange(last_row, ORIGINAL_DATE_TIME_COL).setNumberFormat("dd/MM/yyyy hh:mm:ss");
      }
    }
    last_row--;
  }
}

Ed Sambuco

unread,
Nov 27, 2023, 5:18:54 PM11/27/23
to google-apps-sc...@googlegroups.com
This exception hit me too recently.  I just reran the job and all was well.  The job runs nightly and almost always runs successfully.  When it doesn't  ther reason is usually some internal Google server error.

SO .. I think this is a Google system issue, and I would contact Google support.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/616f4862-5d63-4a49-9dad-4f6d995021c5n%40googlegroups.com.

George Ghanem

unread,
Nov 27, 2023, 10:43:07 PM11/27/23
to google-apps-sc...@googlegroups.com
Agree with Ed on this.

However, I would also advise you against having the code running every minute. That seems excessive. Try using an onEdit trigger instead to only make updates to you spreadsheet when something changes.


Jamie Schild

unread,
Mar 13, 2024, 9:09:09 AMMar 13
to Google Apps Script Community
New here in the appscript world and having this same issue. 
I'm copying over data between sheets, almost as a importrange work around. 
When the dataset is small, it works everytime. 
But with larger tabs, the script will fail 25% of the time. 
Is there best practices on the size tabs // datasets to pull across with these get & set formulas?

Or to automatically run again until they don't fail?  

Thanks for your help!  

Keith Andersen

unread,
Mar 13, 2024, 12:18:39 PMMar 13
to google-apps-sc...@googlegroups.com
Jamie,
How many rows and columns are there when it fails?

Keith 

Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!

Mahmoud Fattouh

unread,
Mar 13, 2024, 12:39:37 PMMar 13
to google-apps-sc...@googlegroups.com
Hi Keith,

I noticed that it's mostly happening with dates that either start or end on 1st of Jan or 31 december, I will have to recheck for better answers. Will let you know in a couple of hours.

Jamie Schild

unread,
Mar 17, 2024, 11:59:53 AMMar 17
to Google Apps Script Community
Hi Keith, 
The tab I'm copy'ing and pasting to another file is 5 columns and upwards of 10,000 rows.  
I moved the trigger to daily and the scripts seem to be working now.  But I'm such a newbie, I'd like to learn why here.  
The first 1,200 rows paste across super quickly, then the rest can take upwards of 5 minutes when it works.

Thanks for the note and sorry for the delay.

Keith Andersen

unread,
Mar 17, 2024, 3:16:47 PMMar 17
to google-apps-sc...@googlegroups.com
Jamie,
Can you post your copy/paste script?

The script in this thread is not a cop/paste script.

Tks
Keith 

Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!

Jamie Schild

unread,
Mar 19, 2024, 2:32:01 PMMar 19
to Google Apps Script Community
Hey Keith, 
Thanks for the help here!  Loving this community.

Here's the script I'm using; the source sheet is 3500 rows by 5 columns:

function updateEKOSSnoPass() {
copyData ('source sheet id', 'Sno Pass', 'destination sheet Id', 'EKOS'); // <- leaving off spreadsheetID's
}

function copyData (sourceSpreadsheetId, sourceSheetName, destinationSpreadsheetId, destinationSheetName) {
// Get the source spreadsheet
const sourceSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetId);
const sourceSheet = sourceSpreadsheet.getSheetByName(sourceSheetName);

if (!sourceSheet) {
console.error('Source sheet not found.');
return;
}

// Get the data range from the source sheet
const sourceRange = sourceSheet.getDataRange();
const sourceValues = sourceRange.getDisplayValues();

// Get the destination spreadsheet
const destinationSpreadsheet = SpreadsheetApp.openById(destinationSpreadsheetId);
const destinationSheet = destinationSpreadsheet.getSheetByName(destinationSheetName);

if (!destinationSheet) {
console.error('Destination sheet not found.');
return;
}

// Clear the contents of the destination sheet to ensure a fresh copy
destinationSheet.clearContents();

// Get the destination range in the destination sheet
const destinationRange = destinationSheet.getRange(1, 1, sourceValues.length, sourceValues[0].length);

// Copy the data to the destination sheet
destinationRange.setValues(sourceValues);
}

Here are the last 7 days of triggers from this script with yesterday failing and this error message"

"Mar 18, 2024, 12:43:36 AM
Error
Service Spreadsheets timed out while accessing document with id ..."
Screenshot 2024-03-19 at 12.31.03 PM.png

Mj Singh

unread,
Mar 22, 2024, 3:20:41 AMMar 22
to Google Apps Script Community
Hi Community,

Hope you all are doing fantastic and your codes would be too. I am here with a issue I am facing.


I am facing issues accessing ```pathInfo``` and ```parameters``` the code is not reading them. In the code mode it don't have any in test and publish mode it says unable to read e.perameter in line XYZ in code.gs

can someone please help me with this.

I want to make an alert window as well but in code.gs alert don't work, it works in script file however my issue is with e.pram


Any help please...

Keith Andersen

unread,
Mar 22, 2024, 10:54:21 AMMar 22
to google-apps-sc...@googlegroups.com
MJ Singh

Please post in a new thread.

Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!
--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
Reply all
Reply to author
Forward
Message has been deleted
0 new messages