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.
Indeed, the first invocation of importrange
cannot be nested within another function: the request for authorization does not "bubble up" (at first I thought this was an error on Google's part, but then considered that a single command can combine several importrange
within it, so providing an authorization dialog in such a case would be extra complicated).
But you don't need a separate sheet to handle authorization. Just make the first call to the spreadsheet a single cell request: importRange("key","A1")
. Then this can be replaced with the countunique
that you want. Or, you can have a column with keys, and another column with a single cell request for authorization purpose.
Best Answer