Custom function throws a "You do not have the permission required to setValue" error

Question

I am trying to set some value to a cell in a Google Spreadsheet:

    function exampleFunction() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheets()[0];
      var range1 = sheet.getRange("A1");
      var value1 = range1.getValue();
      value1+=1;
      range1.setValue(2);
      return value1;
    }

If I am trying to affect a cell with this function, this error appears:

You do not have the permission required to setValue. (line 10, file "ddd")

Do you know how I could make that possible? I actually want the affected cell to take the value of the cell A1 and increase the value of A1 by 1.

Meander

What is 'range1.setValue(2);' supposed to do? Are you trying to add 1 to A1 and put the we result somewhere else or are you trying to change cell A1 by adding 1 to it? Are you using a custom functions to call it, i.e. =exampleFunction() in a cell? Don't understand. – Lonni

I agree with Michael, it is not clear... I assumed in my answer that this function is used as a custom function since the error message one gets when testing it as a custom function is exactly "you don't have permission to call setValue()..." Also: the question says :"the affected cell" I guess this means "the cell in which I put the function" ? – Balough

Does this answer your question? No permission to call msgBox in Google Apps Scripting – Chloromycetin

Answer 1

from the documentation :

Custom functions return values, but they cannot set values outside the cells they are in. In most circumstances, a custom function in cell A1 cannot modify cell A5. However, if a custom function returns a double array, the results overflow the cell containing the function and fill the cells below and to the right of the cell containing the custom function. You can test this with a custom function containing return [[1,2],[3,4]];.

reference : Custom Functions in Spreadsheets

Balough

Well, then what is a purpose of .setValue() method? This doesn't make sense. – Zomba

Custom function is only one small use case of gas – Balough

So where else do u use functions from Google Scripts other than in scripts? – Zomba

I didn't say "script", I said custom functions used in spreadsheet cells. Please read the first page of documentation: type of scripts. – Balough

This is completely useless. You cannot also modify something like backgroundcolor of the same cell that invoke teh custom function – Catastrophism

I get this same error message even though I'm updating exactly the same cell. I tried setValues to overlap and still get the same error. – Splenomegaly

Just return a value, don't try to setvalue. Please read the doc, they show examples. – Balough

@Sergeinsas - could you please consider updating your answer to reference the latest version of the documentation in the body? This Q&A is currently discussed on meta to become referenced as canonical for the issue (possibly move your last comment to the answer) - thank you! – Fassett

Answer 2

It looks that you are using the above function as a custom function, in other words, it is called by cell formula on the Google Sheets UI, in the following way:

=exampleFunction()

Custom functions in Google Sheets have limitations like they can't be used to call Google Apps Script services that require permissions. The workaround is to use another mean to call the function:

• Run it from the Google Apps Script Editor
• Use a custom menu
• Use a trigger

Also they could be called from dialogs and sidebars, Google Apps Script Web apps and by using the Google Apps Script execution API

Chloromycetin

Answer 3

It's just a little different than what we programmers think.
You can use setFormula in a Macro but not in a custom function.
Just create a simple macro from Tools > Macros > Record Macro, and then open the Script editor and change the Macro's code to your code...

Here is my Macro's code:

function SetFormula() {
  var spreadsheet = SpreadsheetApp.getActive();
  var formulaValue = spreadsheet.getRange('formulaText').getValue().toString();
  spreadsheet.getRange('total').setFormula(formulaValue);
  return formulaValue;
};

Then, to run your macro automatically (you can run that manually from Tools > Macros > YOUR-MACRO-NAME), just create a trigger as follows:

1. Open the Script Editor:

2. Then go to Triggers from the left side panel and tap on Add Trigger button:

3. Finally, create the trigger, select your Macro from the list (mine is SetFormula), select the Event Source as From SpreadSheet, the Event Type to On Edit, and save it.

That's it!

---

I named my ranges as FormulaText and total to be more flexible.
you can do that from here:

Lavin

Answer 4

Custom functions do have permission limitations as noted above. They can run with a custom menu or you can insert an image and assign a custom script to it to use it like a button.

Using a Trigger is another way to accomplish something like this example, which makes it automatic.

A simple trigger in an App Script such as onSelectionChange(e) works without running into the permissions issue of putting a custom function into a cell. This trigger is newer than what was available in the original post. In the simple example below, cell A1 will turn white with an even integer and red with anything else. Granted, the speed at which the triggers fire may vary. It's not always as instantaneous as one might expect.

function onSelectionChange(e) {

  const sheet = SpreadsheetApp.getActive()
  var value1 = sheet.getRange("A1").getValue()
    if(value1 % 2 == 0) {
        sheet.getRange("A1").setBackground("#FFFFFF") //white
    } else {
        sheet.getRange("A1").setBackground("#FF0000") //red
    }
}

Gupta