2

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)
}
  • If i t's 1-100, put a 1 in column A. What about 72-100? And yeah the code is extremely messy. – Shahar Aug 4 '14 at 21:51
  • The 1-100 is meant as an example. My ranges are from 1-71, 72, 73-125, etc. as shown in my if statements. – Eric Aug 4 '14 at 21:55
  • Have a look at a similar question: webapps.stackexchange.com/a/60298/29140 – Jacob Jan Tuinstra Aug 5 '14 at 8:33
3

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.

  • That requires an additional sheet plus many VLOOKUP formulae will make it slow. – Jacob Jan Tuinstra Aug 6 '14 at 6:03
  • 2
    Jacob, I would always go for native functions, rather than custom, in the interest of performance. Even better would be applying as an array formula. Regarding the additional sheet - the lookup data has to go somewhere! (whether in separate cells, or hardcoded into the formula, or in Google Apps Script). Array formula would be something like: =ArrayFormula(IF(LEN(U:U),IFERROR(VLOOKUP(U:U,D1:E20,2),"Error: No box location!"),)) – AdamL Aug 13 '14 at 23:20
  • 1
    @Adaml I agree. In this case, the OP made an effort to make something work in GAS. I and another poster made suggestions about how to improve the code. – Jacob Jan Tuinstra Sep 8 '14 at 21:19
  • 3
    @JacobJanTuinstra yes I think the GAS answers are totally appropriate, given the question. I guess my comment was directed more towards your comment above it, rather than your answer. In that I would generally recommend native functions (used optimally) rather than custom functions in the interest of performance. But that is indeed a generalisation - in some cases, just deleting blank rows in a sheet will give the best performance increase of all! – AdamL Sep 9 '14 at 1:44
1

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

0

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:

enter image description here

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.

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.