The custom formula I created isn't working due to a permission issue, but after following the guidelines I still can't understand what the problem is.
I wrote a custom formula "SUMIFALL" that I published to other group members on the same domain as an addon. For some reason, when we try to use it (me included) in a spreadsheet that doesn't contain the actual script (but does included through the addon), it generates the following error:
Error You do not have permission to call getActiveUser (line 37).
Following these guidelines about access permission of custom formulas in google sheets: https://developers.google.com/apps-script/guides/sheets/functions#advanced
and specifically regarding sheets:
Read only (can use most get*() methods, but not set*()). Cannot open other spreadsheets (SpreadsheetApp.openById() or SpreadsheetApp.openByUrl()).
I tried to make sure to use only Get functions, but it still generates the error. The code:
/**
* For each cell in {searchCol} that contains the cell content, it adds
* the correspond value from the same row in {sumCol}
* {criterion} is optional if want to add a condition the the {sumCol} value also need to fullfill.
* @param {searchCol} input The column to check and iterate over.
* @param {sumCol} input The correspond column from which we add to sum.
* @param {criterion} input An optional field - adding a constraint on {sumCol} values.
* @return The sum of all cells that met the requirements.
* @customfunction
*/
function SUMIFALL(searchCol, sumCol, criterion){
var checkCriteria = criterion != null ? encapsulateCriterion(criterion) : function(val) { return true; };
var arrayOfSum = searchCol.map(function(cell){
var sum = 0;
for(var i=0; i<searchCol.length; i++){
var traffic = Number(sumCol[i]);
var phrase = searchCol[i].toString();
var found = phrase.search(new RegExp("\\b("+cell+")\\b","gi")) != -1;
sum += checkCriteria(traffic) && found ? traffic : 0;
}
return sum;
});
return arrayOfSum;
}
/**
* For each cell in {searchCol} that contains the word {cell} content
* adds the correspond value from the same row in {sumCol}
* {criterion} is optional if want to add a condition the the {sumCol} value also need to fullfill.
* @param {cell} A cell that contains the word to be searched.
* @param {searchCol} input The column to check and iterate over.
* @param {sumCol} input The correspond column from which we add to sum.
* @param {criterion} input An optional field - adding a constraint on {sumCol} values.
* @return The sum of all cells that met the requirements.
* @customfunction
*/
function SUMIFREGEX(cell, searchCol, sumCol, criterion){
var checkCriteria = criterion != null ? encapsulateCriterion(criterion) : function(val) { return true; };
var sum = 0;
for(var i=0; i<searchCol.length; i++){
var traffic = Number(sumCol[i]);
var phrase = searchCol[i].toString();
var found = phrase.search(new RegExp("\\b("+cell+")\\b","gi")) != -1;
sum += checkCriteria(traffic) && found ? traffic : 0;
}
return sum;
}
function encapsulateCriterion(criterion){
var criteriaSplit = criterion.search(/[0-9]/gi);
var criteria = criterion.substring(0,criteriaSplit);
var number = Number(criterion.substring(criteriaSplit));
return function(val){
return criteria == '=' ? val == number :
criteria == '<' ? val < number :
criteria == '>' ? val > number :
criteria == '<=' ? val <= number :
criteria == '>=' ? val >= number :
criteria == '<>' ? val != number : true;
}
}
They also mention on their page:
If your custom function throws the error message You do not have permission to call X service., the service requires user authorization and thus cannot be used in a custom function.
Does anybody know what might be the problem? The only manipulation on Sheet content is on the supplied range, which is very similar to the example they provided on their page.
Thanks in advance
getActiveUser()? – TheMaster