Google-sheets – Code invoked too many times

google sheetsgoogle-apps-scriptgoogle-sheets-custom-function

My goal is pretty simple: take the value in column U and check if it's within particular ranges. If it's 1–100, put a 1 in column A. If it's 101–200, put a 2 in column A. The problem is, I have almost 2000 rows of numbers in column U. Putting =function(U2) in each cell in column A results in a

Script invoked too many times per second

error, and adding Utilities.sleep(500) at the end doesn't help either. Here's my code so far. Please excuse the ugliness, as I'm a very beginner programmer.

function assignBoxNumber(sgNumber) {
  if(sgNumber>=1 && sgNumber<=71) {
    return "1"
  }
  if(sgNumber==72) {
    return "1 and 2"
  }
  if(sgNumber>=73 && sgNumber<=125) {
    return "2"
  }
  if(sgNumber==126) {
    return "2 and 3"
  }
  if(sgNumber>=127 && sgNumber<=202) {
    return "3"
  }
  if(sgNumber>=207 && sgNumber<=269) {
    return "4"
  }
  if(sgNumber>=270 && sgNumber<=333) {
    return "5"
  }
  if(sgNumber>=334 && sgNumber<=433) {
    return "6"
  }
  if(sgNumber>=434 && sgNumber<=497) {
    return "7"
  }
  if(sgNumber>=498 && sgNumber<=560) {
    return "8"
  }
  if(sgNumber>=561 && sgNumber<=627) {
    return "9"
  }
  if(sgNumber>=628 && sgNumber<=691) {
    return "10"
  }
  if(sgNumber>=692 && sgNumber<=791) {
    return "11"
  }
  if(sgNumber>=792 && sgNumber<=872) {
    return "12"
  }
  if(sgNumber>=874 && sgNumber<=955) {
    return "13"
  }
  if(sgNumber>=956 && sgNumber<=1019) {
    return "14"
  }
  else {
    return "Error: No box location!"
  }
  Utilities.sleep(500)
}

Best Answer

You may not need a script for this as a formula of the kind:

=iferror(vlookup(U1,D$1:E$20,2),"Error: No box location!")

copied down to suit may serve where D1:E20 is a table of the kind:

WA66026 example

This could be in the same sheet but seems sensible to hold such 'reference' data in a separate sheet (in which case the formula above would require the addition of a sheet reference).

If double-clicking works for coping down the speed is about the same as for a user defined function and may even be faster, though neither should take more than about 10 seconds for 2000 rows.

Related Solutions

Google Sheets – How to Avoid ‘Invoked Too Many Times Per Second’ Error

Don't use a custom formula--instead process all the values into an array then have the array written to the sheet. This will not adjust as your spreadsheet changes, but you should be able to modify this to look for the last value using a combination of the match and len functions in the sheet to id the starting and ending rows.

function straightToText() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var sheet = ss[0].getRange(1, 1, 9999, 1).getValues(); // get first sheet, a1:a9999
  var results = [];
  for (var i = 0; i < sheet.length; i++) {
    var hash = hash256(sheet[i][0]); // hash256 is the custom formula in this case
    results.push(hash);
  }
  for (var j = 0; j < results.length; j++) {
    var row = j+1;
    ss[0].getRange(row, 3).setValue(results[j]);  // write output to c1:c9999 as text
  }  
}

This works for a sheet processing 10k rows with no errors, where use of the custom formula in the sheet itself (i.e., =hash256(a1)) produces an error after ~500 rows.

Google Apps Script – Prevent ‘Invoked Too Many Times Per Second’ Error

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.