Google Script setValue permission

问题:

I'm trying to set some value to a cell in a google docs 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'm 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 that the affected cell takes the value of the cell A1 and increase the value of A1 +1.

Thank you

回答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

回答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