Google script custom function errors workaround

Question

I am using custom functions in spreadsheets for various operations. I have used properties service which values is cached. Also, I have send range of values to function instead of calling that function for hundred of times.

I am using function for one column with 100 rows and growing. I tried to take two column at ones but it exceeds 30 sec custom function limit.

Nevertheless, I am getting some error messages:

"Error: Internal error executing the custom function."

because the same custom function 50 times simultaneously is calculating answers for those 100 rows.

I am getting this message about 9 times of 50. It is not strange, because the function is counting a lot of stuff.

The problem would be solved if it would be possible start the same custom function in different time or even better if custom function would run again after error message. Maybe there is another way too.

I have tried applying Exponential Backoff but I guess it's not possible to do this with custom functions.

I am new to JavaScript and I tried to find workaround for this but it was not successful.

This is the image of spreadsheet that is calling this custom function:

This is the code of my custom function:

// Names - range of names e.g. A4:A100
// function is returning number if it meets conditions 
function VSApmokyti(Names, date, place ) {

// if date and place is not arrays
if ( !Array.isArray(date) ) {
  return Names.map (function (d) { 
  return process (d[0], date, place)   
})

}
// if date and place is arrays
else {
return Names.map (function (d) { 
 return date[0].map (function (k, h) {
       return process (d[0], k, place[0][h])   
       })
       })
}
// this function can calculate no matter if date or place is arrays or values
function process(teacher, Vdate, school) {

  if (Vdate=="") {
    return null;
  }
  if (teacher=="") {
    return null;
  }



      // Taking from CACHE
  var cache = CacheService.getScriptCache();

  var teachersL = cache.get("TeachersL");
  teachersL = JSON.parse(teachersL);

  var teachers1 = cache.get("Teachers1");
  teachers1 = JSON.parse(teachers1);  

  var teachers2 = cache.get("Teachers2");
  teachers2 = JSON.parse(teachers2);

  var teachers3 = cache.get("Teachers3");
  teachers3 = JSON.parse(teachers3);  

  var teachers4 = cache.get("Teachers4");
  teachers4 = JSON.parse(teachers4);

  var dates = cache.get("Dates");
  dates = JSON.parse(dates);  

  var Schools = cache.get("Schools");
  Schools = JSON.parse(Schools);

  var number = cache.get("NumberScholars");
  number = JSON.parse(number);   



    if (!number) {

        // WRITING to CACHE
      var TeachersL = PropertiesService.getScriptProperties().getProperty('TeachersL');
      cache.put('TeachersL', TeachersL);
      teachersL = JSON.parse(TeachersL);


      var Teachers1 = PropertiesService.getScriptProperties().getProperty('Teachers1');
      cache.put('Teachers1', Teachers1);
      teachers1 = JSON.parse(Teachers1);


      var Teachers2 = PropertiesService.getScriptProperties().getProperty('Teachers2');
      cache.put('Teachers2', Teachers2); 
      teachers2 = JSON.parse(Teachers2);


      var Teachers3 = PropertiesService.getScriptProperties().getProperty('Teachers3');
      cache.put('Teachers3', Teachers3); 
      teachers3 = JSON.parse(Teachers3);


      var Teachers4 = PropertiesService.getScriptProperties().getProperty('Teachers4');
      cache.put('Teachers4', Teachers4); 
      teachers4 = JSON.parse(Teachers4);


      var Dates = PropertiesService.getScriptProperties().getProperty('Dates');
      cache.put('Dates', Dates); 
      dates = JSON.parse(Dates);


      var Schools = PropertiesService.getScriptProperties().getProperty('Schools');
      cache.put('Schools', Schools); 
      Schools = JSON.parse(Schools);


      var NumberScholars = PropertiesService.getScriptProperties().getProperty('NumberScholars');
      cache.put('NumberScholars', NumberScholars); 
      number = JSON.parse(NumberScholars);

    }


  // converting date from spreadsheet cell to be able check if condition
  Vdate = Vdate.toJSON();

        for(var y = 0; y < Schools.length; y++) { 
       if(Vdate==dates[y] && school==Schools[y]) { 
        if ((teacher==teachersL[y]) || (teacher==teachers1[y]) || (teacher==teachers2[y]) || (teacher==teachers3[y]) || (teacher==teachers4[y]))  {

          return number[y];

        }
      }
    }

}
}

Answer 1

Short answer

Instead of a custom function that return a single value, make it return an array of values.

Explanation

Custom functions could return a single value or an array of values. If you will apply the custom function to contiguous cells, instead of applying a formula that returns a value to each cell resulting in multiple calculations you could use a single formula that returns an array of values but bear in mind that custom functions have a 30 secs time execution limit. See Optimization

If your function exceeds the 30 secs limit, then use a "regular" function that will be called by a custom menu, dialog, sidebar, trigger or from the Google Apps Script Editor. This kind of functions has a 6 minutes execution time limit for regular accounts and 30 minutes for G Suite accounts that signed up to Early Access Program.

Answer 2

There are a few ways to handle errors in Apps-script. One way that is not too difficult is to use try and catch. For example the code below will write a log to a sheet of any errors that occur while a function is running. Also try and catch is none blocking so function will not stop even if there are errors. Here is an example that will log errors into a Google sheet.

function somefunction(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var logSheet = ss.getSheetByName('Sheet1');

  try {
    myFunctionThatMightHaveErrors()
  } 

  catch(e){
    var now = new Data();
    var error = JSON.stringify(e);
    logSheet.appendRow([now, error]);
  }

}

FYI: Apps-script will run any function for up to 6 minutes, unless your domain is has a Gsuite buisness account and app maker. But there are ways around the time limit if needed.