3

I am trying to use a custom function. Whenever I try to use it as a formula from within sheets and run it on a cell it throws the error:

You do not have permission to call setDataValidation

But when I run it from the script editor it runs perfectly fine. How can I make it work without having to click Run from the script editor?

Note: I prefer not to use triggers.

Rubén
  • 34,714
  • 9
  • 70
  • 166
Flame of udun
  • 2,136
  • 7
  • 35
  • 79
  • Are you trying to affect cells other than those it returns a value to? [Doc: Return values](https://developers.google.com/apps-script/guides/sheets/functions#return_values) – ocordova Jul 28 '16 at 17:36
  • @ocordova no. it's only for the cell on which the formula is called. – Flame of udun Jul 28 '16 at 17:38

1 Answers1

3

SpreadsheetApp service is read only when called with custom functions

Ocordova has the right idea. The article he cited discusses how the SpreadsheetApp service is...

Read only (can use most get*() methods, but not set*()). Cannot open other spreadsheets (SpreadsheetApp.openById() or SpreadsheetApp.openByUrl()).

If you want to be able to edit cells, you will need to call the functions from a custom menu.

Joshua Dawson
  • 629
  • 10
  • 17
  • I am not using `openById()` nor `openByUrl()` but using `getActiveSpreadsheet()` but I still get the error. – c-an Jan 14 '20 at 09:11