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 return
ing 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.