Code invoked too many times

Question

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)
}

Answer 1

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:

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.

Answer 2

I would go about doing it like so.

Code

function myValues(range) {
  var output = [];
  for(var i = 0, iLen = range.length; i < iLen; i++) {
    output.push(checkValue(range[i][0]));
  }  
  return output;
}

function checkValue(value) {
  var check;
  if(value >= 1 && value <= 71) {
    check = 1;
  } else if (value == 72) {
    check = '1 and 2';
  } else if (value >= 73 && value <= 125) {
    check = 2;
  } else if (value == 126) {
    check = '2 and 3';
  } else {
    check = 'Error: No box location!';
  }
  return check.toString();
}

Note

Make sure to add the rest of the criteria yourself !!

Example

I've created an example file for you: code invoked too many times

Answer 3

Google's help center says:

Script invoked too many times per second for this Google user account. This indicates that the script began executing too many times in a short period. It most commonly occurs for custom functions that are called repeatedly in a single spreadsheet. To avoid this error, code your custom functions so that they only need to be called once per range of data, as explained in the guide to custom functions.

I'm assuming this is because calling a custom function takes a lot of memory (going through the existing functions [possibly in Google's servers] to see if it's there, then turning back to execute it). You need to make it so that it will be only called once instead of 2000 times. The fact that you added sleep() did nothing because it's unreachable (you are always returning before you get to that statement), but, even if it wasn't, it would only make it worse because it doesn't sleep between invocations, but rather after each invocation.

Your code is a little funny, and I was especially amused at the else clause at the end. I couldn't find a pattern for your ranges so I just stored all the upper bounds and their respective outputs in two arrays. In addition, for the code to only be invoked once, I made it so that you have to choose what range you want and it returns an array (each element is outputted on a different row). Check it out:

function assignBoxNumber(range){
  var maxes = [1, 72, 73, 126, 127, 203, 207, 270, 334, 434, 498, 561, 628, 692, 792, 873, 874, 956, 1020];
  var vals = ["", "1", "1 and 2", "2", "2 and 3", "3", "", "4", "5", "6", "7", "8", "9", "10", "11", "12", "", "13", "14"];
  //if (maxes.length != vals.length) {return "you fucked up"; }
  var length = range.length, output = new Array(length);
  var i, obj, key;
  for (var row = 0; row < length; row++)
  {
    obj = range[row][0];
    key = "";
    for (i = 0; i < maxes.length; i++)
    {
      if (obj < maxes[i]){ key = vals[i]; break;}
    }
    output[row] = (key == "") ? "Error: No box location!" : key;
  }
  return output;
};

Note that the parameter is a range. Meaning that it will be in the format of <cell1>:<cell2>, U1:U20 for instance, but it's NOT a string. The first number is the first cell (in the example U1), the second is the last cell (U20). You can also select the region you want to help you get the range:

But obviously for 2k+ rows you want to write it in. So call the function like =assignBoxNumber(<first cell>:<last cell>).

I tested it on 1,021 rows and it worked perfectly. I understand why there was a problem before - it takes some time to actually find the custom function. There shouldn't be a problem now that there's only 1 call to the custom function.

UPDATE: Fixed the arrays and now the output is completely identical to that of your messy method.