Prevent “invoked too many times per second” error in Google Apps Script

Question

I have a Google spreadsheet consisting of different months on each tab. Every month, I add a new tab and another 50-75 rows. There are many tabs. There is a cell on each row which calls a custom function, like this:

=myFunction(A1)

The problem I am having is that I am getting the following error:

error: Script invoked too many times per second for this Google user account.

What can I do to prevent this error from occurring?

Is the problem because there are too many tabs, or too many rows per tab?

There must be a way to prevent the function from having to run more than once.

---

Here is a shortened version of the function that is called:

function myFunction(expense) {
if (
  InStr(1,expense,"CPC SCP") ||
  InStr(1,expense,"CPC/SCP") ||
  InStr(1,expense,"xxxx")
  )
  { category = "5530 - Postage Expense" } 

  else if (
  InStr(1,expense,"PRE-AUTHORIZED PAYMENT") ||
  InStr(1,expense,"xxx ")
  )
  { category = "9999 - Payment" } 

  else if (
  InStr(1,expense,"PURCHASE INTEREST") ||
  InStr(1,expense,"RETURNED PAYMENT FEE") ||
  InStr(1,expense,"xxxx")
  )
  { category = "5070 - Bank Charges" } 

else { category = "Not Sure" }

return category;  

} 

Answer 1

Better is to prepare a result for a range in stead of dealing with individual cell. When you open the file, it needs to process all the cells individually. This, in the end, evoked the error. Therefore I created the below code.

Code

function catExp_1(range) {
  var output = [], arr = JSON.parse(createArr());
  for(var j=0, jLen=range.length; j<jLen; j++) {
    var result = "Not Sure";
    for(var i=0, iLen=arr.length; i<iLen; i++) {      
      for(var k=0, kLen=arr[i][1].length; k<kLen; k++) {        
        if(range[j][0].indexOf(arr[i][1][k]) != -1) {
          result = arr[i][0];
        }
      }      
    }
    output.push([result]);
  }
  return output;
}

function createArr() {
  // check for availability
  var cache = CacheService.getPublicCache();
  var cached = cache.get("accounts");
  if (cached != null) {  
    return cached;
  }

  // create array
  var arr = [    
    [["5070 - Bank Charges"],["PURCHASE INTEREST", "xxxx"]],
    [["5530 - Postage Expense"],["CPC SCP","xxxx"]],
    [["9999 - xxxxxxx"],["xxxxxxxx"]]
  ];

  // add to cache
  var jsonData = JSON.stringify(arr);
  cache.put("accounts", jsonData, 21000);
  return jsonData;
}

Explained

The first script will create an output array and collect the lookup data. This is either stored in the cache or prepared on the fly (and stored in the cache for the next time). The iteration start with a category Not Sure. If an match is found, then the corresponding value will be push into the output array. Else the Not Sure will be added.

Usage

=catExp_1(B2:B133)

Note

You might want to consider adding the categories, rather than looking them up. This eliminates any delay from the script. You can easily create a menu item that does this.