Do not have permission to use setFormula Google appScript

Question

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?

Although I'm not sure whether my understanding of your question is correct, if you want to use ``pos()`` as the custom function, unfortunately, the other function cannot be put using the custom function. So as a workaround, how about using the OnEdit event trigger? For example, when ``=SAMPLE()`` is put to a cell, ``=SAMPLE()`` is replaced to the function you want by the OnEdit event trigger. [This thread](https://stackoverflow.com/q/56799257/7108653) might also be useful. If this was not the direction you want, I apologize. — Tanaike, Jul 02 '19 at 22:22
I need the address of the cell printed in the cell itself, like when I type "=pos(A1)" I should have "$A$1" in the cell. If you know what I mean. About OnEdit, I am trying to read about, I am not sure if I understood well. — Alexandre, Jul 02 '19 at 23:51

Rubén · Accepted Answer · 2021-02-07T22:08:49.577

4

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

answered Jul 03 '19 at 01:20

Rubén

34,714
9
70
166

@CristianErikAmesMasekThe reference explains how custom functions works in Google Sheets :) – Rubén May 14 '20 at 19:28
Hmm agree with @CristianErikAmesMasek, however having now read the reference page this seems to be the key bit that Ruben could add: "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." – johnmartirano Feb 07 '21 at 17:35

Do not have permission to use setFormula Google appScript

1 Answers1

Linked