Calling custom function directly from Google Sheets

Question

How can I solve permission problems with custom functions (calling from Google sheet cells)?

Like

"You do not have permission to call setDataValidation"
"You do not have permission to call setValue"

I researched a lot on these types of the issue especially checked similar issues on Stack Overflow. Some say to add scopes in projects properties(my project already has required scope), some say to call a custom function from menu or buttons.

But my requirement is to call the custom function from Google Sheets from a cell.

I will then share that sheet with my organization (GSuite), so they can have access to the data and custom functions.

I am in trouble, I did not find any solution.

Please suggest possible ways to correct this problem.

*I can easily call these function from Google Apps Script directly (from run) but not from sheets.

Sample code that throws error "You do not have permission to call setDataValidation"

function getNamedRanges(type,cell){  // parms ex. ("Cars" ,"D1")
  Logger.log(type);
    var namedRanges = SpreadsheetApp.getActive().getNamedRanges();
    for (var i = 0; i < namedRanges.length; i++) {
      if(namedRanges[i].getName()==type){
        var cell = SpreadsheetApp.getActive().getRange(cell)
         createDropdownForRange(namedRanges[i].getRange(),cell);
      }
    }

}


function createDropdownForRange(namedRange,cell){
   var rule = SpreadsheetApp
   .newDataValidation()
   .requireValueInRange(namedRange,true);   
  cell.setDataValidation(rule);
}

can you post your script snippet? (the one that doesn't work) some functions require more permissions than others — CrissCrossCrass
I have just added a code snippet that creates dropdowns from Named ranges. It is working fine from Google apps script editor but when I am trying to call it from the sheet it is not working. — sandeep
Custom functions must return a value. You should read the restrictions on custom functions here Additionally, custom functions cannot change other cells. — Cooper
I want to set validation(dropdown from a named range) in the same cell from where the formula is called. How could I do this? — sandeep

Cooper Cooper · Accepted Answer · 2019-05-23T20:53:15

Custom Function Restrictions

Every custom function must return a value to display, such that:

If a custom function returns a value, the value displays in the cell the function was called from. If a custom function returns a two-dimensional array of values, the values overflow into adjacent cells as long as those cells are empty. If this would cause the array to overwrite existing cell contents, the custom function will throw an error instead. For an example, see the section on optimizing custom functions. A custom function cannot affect cells other than those it returns a value to. In other words, a custom function cannot edit arbitrary cells, only the cells it is called from and their adjacent cells. To edit arbitrary cells, use a custom menu to run a function instead. A custom function call must return within 30 seconds. If it does not, the cell will display an error: Internal error executing the custom function.

Calling custom function directly from Google Sheets

1 Answers