javascriptarraysgoogle-apps-scriptgoogle-sheetsgoogle-sheets-custom-function

Google Sheets Custom Function Timing Out


So I have been having erratically getting an error: "Internal Error Executing the Custom Function" when running my custom function on a decently sized range of cells.

Google specifies "A custom function call must return within 30 seconds. If it does not, the cell will display an error: Internal error executing the custom function."

My custom function is this:

function ConcatLoop(rangeString, concatString, isPrefix, isOneColumn) {
  //var rangeString = "A1:A10,B1:B10,C1:C10";
  //var concatString = "1x ";
  //var isPrefix = "true";
  //var isOneColumn = "true";  

  var rangeStringArray = rangeString.split(',');
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var rangeValues=[];

  //GRABBING THE VALUES FROM EACH RANGE
  for(i=0;i<rangeStringArray.length;i++)
  {
    var range = sheet.getRange(rangeStringArray[i]);
    rangeValues.push(range.getValues());
  }

  if(isOneColumn){var newRangeValues = [[]]};

  //REMOVES EMPTY STRINGS AND ARRAYS OR CONCATENATES THE STRINGS
  for (i = 0; i < rangeValues.length; i++) {
    for (j = 0; j < rangeValues[i].length; j++){
      if (rangeValues[i][j] == "") 
      {         
        rangeValues[i].splice(j, 1);
        j--;
      }
      else if(isPrefix == "true")
      {
        rangeValues[i][j] = concatString + rangeValues[i][j];
        if(isOneColumn){newRangeValues[0].push(rangeValues[i][j])};
      }
      else
      {
        rangeValues[i][j] = rangeValues[i][j] + concatString;
        if(isOneColumn){newRangeValues[0].push(rangeValues[i][j])};
      }
    }
    if (rangeValues[i] ==""){
      rangeValues.splice(i,1);
      i--;
    }
  }

  //LOG WHILE TESTING
  //if(isOneColumn){Logger.log(JSON.stringify(newRangeValues))}
  //else{Logger.log("range values after concat: " + rangeValues)}

  //RETURN WHILE RUNNING
  if(isOneColumn){return newRangeValues}
  else{return rangeValues};
}

When I have 1000 values plugged into the function, it takes quite a while to pull all the values. It runs fine when I test it in GOogle Scripts, because there isn't a time constraint there.

If there is anything I can do to work around this or make this more efficient, could someone let me know? Thank you so much!


Solution

  • Thanks to the incredible individuals who commented, I did find an answer earlier, so I'm going to post it for anyone who might need it.

    Google sets a limitation of 30 seconds for the execution time of any custom function on a spreadsheet. Anything above that will give you an internal error. Although there is a limitation of 5 minutes for the execution time in the Script Editor, there is probably an issue if your script takes over 30 seconds to execute. In my case, I had too many separate ranges I needed to pull data out of. So I was calling "getValues(range)" quite a few times within a for-loop.

    The solution was to pull each entire sheet in the spreadsheet as one range. So rather than having 7 ranges for each of my 27 sheets, I had 1 range for each of my 27 sheets. This caused me to have an excess amount of unnecessary information in memory, but also caused the execution time to go from around 45 seconds to 10 seconds. Solved my problem entirely.

    Thank you!