I'm recieving a server error from google-apps-script, how do I figure out what is causing it?

Question

I'm teaching myself app-script, and created a simple function to delete a row if there was an error. Here is the code:

//GLOBALS
 
var SS = SpreadsheetApp.openById("the_sheet_ID"); //just a placeholder because I didn't want to post the real ID
var SHEET = SS.getSheetByName("300x250");
var RANGE = SHEET.getDataRange();
 
 
var DELETE_VAL = "#N/A";
var COL_TO_SEARCH = 2; // The column to search for the DELETE_VAL (Zero is first)
 
  
function deleteEachRow(){
  
  var rangeVals = RANGE.getValues();
  
  //Reverse the 'for' loop.
  for(var i = rangeVals.length-1; i >= 0; i--){
    if(rangeVals[i][COL_TO_SEARCH] === DELETE_VAL){
      
      SHEET.deleteRow(i+1); 
    };
  };
};

I have been receiving e-mails from app script telling me a server error occurred. Below is a copy of the e-mail:

Your script, Deleting_Rows, has recently failed to finish successfully. A summary of the failure(s) is shown below. To configure the triggers for this script, or change your setting for receiving future failure notifications, click here.

4/16/20 1:27 AM deleteEachRow We're sorry, a server error occurred. Please wait a bit and try again. time-based 4/16/20 1:27 AM

Sincerely,

Google Apps Script

The function is timed to run every minute, so it usually fixes itself. I've seen this issue for triggers from deleted functions, or for the broken click here link, but my function is not deleted, and the link works. I haven't been able to find any information about what is causing the error, how to fix it, if the error has to do with my code or google's servers. Any information or insight anyone has would be super helpful!

There's a good chance that the error is from a short term quota limit. You can catch the error, wait, and then try again, or you could get all the data from all the rows, delete "rows" (elements) from the array, and then if the code completes to process the array, delete all the values in the range and set the new values. — Alan Wells
When you testing you code you can set your trigger to respond immediately — Cooper

Andres Duarte Andres Duarte · Accepted Answer · 2020-04-17T08:13:05

As it's an sporadic server error, you could workaround it by catching the error with a try/catch statement and, in case there's an error, retry to execute the code after waiting a bit with sleep function, the code would look this:

function deleteEachRow(){
  var i= 0;
  while(i < 3) {
    try {
      //GLOBALS
      var SS = SpreadsheetApp.openById("1e-JtL0KacshxjWMRSIwwCzHeg0oj7OtzIw_-m_VV8HQ"); //just a placeholder because I didn't want to post the real ID
      var SHEET = SS.getSheetByName("300x250");
      var RANGE = SHEET.getDataRange();

      var DELETE_VAL = "#N/A";
      var COL_TO_SEARCH = 2; // The column to search for the DELETE_VAL (Zero is first)
      var rangeVals = RANGE.getValues();

      //Reverse the 'for' loop.
      for(var i = rangeVals.length-1; i >= 0; i--){
        if(rangeVals[i][COL_TO_SEARCH] === DELETE_VAL){

          SHEET.deleteRow(i+1); 
        };
      };
      // If it gets here, there were no errors
      break;
    }
    catch(e) {
      sleep(5000);
      i++;
    }     
  }
};

I'm recieving a server error from google-apps-script, how do I figure out what is causing it?

1 Answers