Search code examples
google-apps-scriptgoogle-sheetsgoogle-sheets-formulacustom-function

Do not have permission to use setFormula Google appScript

I am trying to make a script where I have the address of the cell.

In excel, I have this code:

Function pos(cell As Range)
    pos = cell.Address
End Function

And it gives me the address of the cell.

On Google app script I tried this code:

function addrss(cel){
    var spreadsheet = SpreadsheetApp.getActive();
    var cc = spreadsheet.getCurrentCell().activate();
    c = cc.setFormula('=ADDRESS(COLUMN('+cel+');ROW('+cel+'))');
    return c;
}

function pos(cell){
    var ad = addrss(cell);
    return ad;
}

But get an error saying "You do not have permission to call setFormula".

Is there a way to have this permission or a way around?

Solution

  • Custom functions can't be used to set cell formulas, they can only be used to return a value/array of values. Ref. Custom Functions in Google Sheets

    To automatically set the formula of a cell instead of using a custom function you might use a function called from:

    • custom menu
    • button
    • dialog or sidebar
    • simple or installable trigger

    Resources

    Related