Non-stop exceptions

[Jonathan A]

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]

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]

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.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAANZsGPS2pAt6%3D3d1sXgabVA5KyJCBGRw0LVUau%3Doe0vUQuk2Q%40mail.gmail.com.

[Jamie Schild]

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]

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!

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/a199be1e-34a6-4c82-9a9a-43bfd3eb3248n%40googlegroups.com.

[Mahmoud Fattouh]

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.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAFKgK%2BFTLqC63ky09XDmAJcYfaEBOmHZtGq%2BBTcLtJuqR-ipHQ%40mail.gmail.com.

[Jamie Schild]

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]

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!

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/bded2587-6d5b-4b09-baad-46318c327388n%40googlegroups.com.

[Jamie Schild]

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 ..."

[Mj Singh]

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]

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.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/d0fc4b06-fc95-4f77-8636-30d4415655ean%40googlegroups.com.