Skip to main content
added 155 characters in body
Source Link
Shahar
  • 261
  • 1
  • 4

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 youit's unreachable (you are always returning before you get to that statement), but, even if you weren'tit wasn't, it would only make it worse because it doesn't sleep between invocations, but rather at everyafter 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 everythingall the upper bounds and their respective outputs in two arrays and wrote the code that way. 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 (keep it to only 1 column and multiple rowseach element is outputted on a different row). Check it out:

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 NOTNOT 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.

I'm assuming this is because calling a custom function takes a lot of memory (going through the existing functions 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 you are always returning before that statement, but even if you weren't it would only make it worse because it doesn't sleep between invocations, but rather at every 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 everything in two arrays and wrote the code that way. In addition, for the code to only be invoked once I made it so that you have to choose what range you want (keep it to only 1 column and multiple rows). Check it out:

Note that the parameter is a range. Meaning that it will be in the format of 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. 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 your messy method.

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:

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.

added 155 characters in body
Source Link
Shahar
  • 261
  • 1
  • 4
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;
};

I tested it on 1,021 rows and it worked perfectly. 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 your messy method.

function assignBoxNumber(range){
  var maxes = [1, 72, 73, 126, 127, 203, 207, 270, 334, 434, 498, 561, 628, 692, 792, 873, 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;
};

I tested it on 1,021 rows and it worked perfectly. There shouldn't be a problem now that there's only 1 call to the custom function.

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

I tested it on 1,021 rows and it worked perfectly. 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 your messy method.

Source Link
Shahar
  • 261
  • 1
  • 4

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 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 you are always returning before that statement, but even if you weren't it would only make it worse because it doesn't sleep between invocations, but rather at every 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 everything in two arrays and wrote the code that way. In addition, for the code to only be invoked once I made it so that you have to choose what range you want (keep it to only 1 column and multiple rows). Check it out:

function assignBoxNumber(range){
  var maxes = [1, 72, 73, 126, 127, 203, 207, 270, 334, 434, 498, 561, 628, 692, 792, 873, 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 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. There shouldn't be a problem now that there's only 1 call to the custom function.