Spreadsheets: "you do not have permission to call setNumberFormat"

Question

I've got the following script running on a Google Spreadsheet:

function FORMATCURRENCY(value, currency) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  
  var range = sheet.getRange("D19");
  
  range.setNumberFormat("$#,##0.00;$(#,##0.00)");
  
  return range 
}

But when I run it I get an error saying "You do not have permission to run setNumberFormat".

I've looked into the documentation for this function here and I've followed all the instructions, including adding the authorization dependencies on the scope properties in the script area and I still get the same error.

My manifest file looks like so in the scripts:

{
  "timeZone": "Europe/London",
  "dependencies": {
  },
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/spreadsheets.currentonly",
    "https://www.googleapis.com/auth/spreadsheets.readonly",
    "https://www.googleapis.com/auth/userinfo.email"
  ]
}

What is the problem here?

Answer 1

Custom functions can not be used with services that require authorization.

All Set methods belong to this category, in your case setNumberFormat:

See the official documentation regarding custom functions:

If your custom function throws the error message You do not have permission to call X service., the service requires user authorization and thus cannot be used in a custom function.

To use a service other than those listed above, create a custom menu that runs an Apps Script function instead of writing a custom function. A function that is triggered from a menu will ask the user for authorization if necessary and can consequently use all Apps Script services.

Alternatively, you can run the function from the script editor but keep in mind that you need to pass the arguments to be able to execute it or define them within your function.

---

In this case, since you are not using value and currency within the FORMATCURRENCY function, you can execute this directly from your script editor or from a custom menu but not as a formula in your sheet:

function FORMATCURRENCY() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  
  var range = sheet.getRange("D19");

  range.setNumberFormat("$#,##0.00;$(#,##0.00)");
  
}