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.
Google offers a limited amount of computing power to Sheets users. This restriction is particularly noticeable for scripts: they take a long time to run, and trying to invoke a script too often results in errors of the type you encounter.
The task you are trying to do here could be done with a single script invocation. It may still take a while to process the sheet, but at least you won't get the error about trying to invoke a script 1000 times at once.
function names() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var numberRange = sheet.getRange("B2:B1001");
var nameRange = sheet.getRange("C2:C1001");
var values = numberRange.getValues();
var names = [];
for (var i=0; i<values.length; i++) {
switch (values[i][0]) {
case 55837649021 :
names.push(["Anton Hylderburg"]); break;
case 55837109810 :
names.push(["John Strong"]); break;
case 55837109410 :
names.push(["Elisabeth Joseph"]); break;
default:
names.push([""]);
}
}
nameRange.setValues(names);
}
This script can be invoked manually, from the editor window. Or you could add an item to the main menu if you use this often. Like this:
function onOpen() {
var menu = [{name: "Insert Names", functionName: "names"}];
SpreadsheetApp.getActiveSpreadsheet().addMenu("Custom", menu);
jumpToDate();
}
This function will execute any time you open the spreadsheet, and will add an item Custom > Insert Names to the menu. Using this function, you can invoke the script any time you want.
It's also possible to run the script automatically, e.g., every hour. In the script editor, go to Resources > Current project's trigger, and add a trigger for function names
.
Best Answer
UPDATE: Google Apps Script now offers two JavaScript engines
References
Your code is using an arrow function. This feature is part of ECMAScript 6 but it's not supported by default by Google Apps Script (it supports some ECMAScript 5 features but not all). Replace the arrow function by an anonymous function, add the arrow function polyfill use CLASP a desktop IDE and Babel or something similar.
References