2

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;  

} 
  • Thanks for the suggestion. Now I just need to figure out how to implement that suggestion. Do you have any ideas how I could run the function once, but then loop all of the rows in the tab with one function call? – DanielAttard May 1 '14 at 20:21
  • Thanks for the suggestion to use a lookup, but I still don't understand how that will solve my original problem. Wouldn't I simply be replacing one function with another and still run into "too many times per second" error? If I have 20 tabs, with 50 function calls per tab, does that mean the function is called 1000 times, whether I view the tab or not? – DanielAttard May 2 '14 at 13:25
  • Sorry, but still not following you. If I have 1000 cells that need to be updated, I don't know how to do that without using 1000 function calls, one in each cell, regarding of how the function itself has been coded. – DanielAttard May 2 '14 at 14:00
  • 1
    Ok, penny just dropped. I understand what you mean now. Thanks for sticking with me. – DanielAttard May 2 '14 at 14:09
  • Why don't you share a doc with us and we'll have a look.. – Jacob Jan Tuinstra May 2 '14 at 18:25
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.

  • 1
    This is a great solution. Thanks so much for showing me how to do it. – DanielAttard May 26 '14 at 18:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.