Google Spreadsheet Script Permission Error

Question

Why can't I create this script?

function setValueTest(value) {  
  SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName('sheet1')
    .getRange('D20')
    .setValue('Hello');

  return(value);
}

Error message:

Error: Exception, You do not have permission to call setValue (line 11).

But if I "don't have permission", why can I run other scripts? which also define values

But if I do that I can (it doesn't):

function setValueTest(value) {
  return(value);
}

are you trying to use the function in a cell (like a formula) on the worksheet? is so then this explains is why you are getting the error. — Mr Shane, Dec 31 '21 at 22:35
Yes, it's a custom function and I'm trying to use it in a cell. — Dhenyson Jhean, Dec 31 '21 at 22:43

Cooper · Accepted Answer · 2021-12-31T22:34:43.807

I can create this function with no problem:

function setValueTest(value='five') {  
  SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName('sheet1')
    .getRange('D20')
    .setValue('Hello');
  Logger.log(value);
  return(value);
}

And run it:

Execution log
2:58:25 PM  Notice  Execution started
2:58:25 PM  Info    five
2:58:26 PM  Notice  Execution completed

I even ran it with this simple trigger. It will run as long as the spreadsheet it accesses is that one that it is bound to.

function onEdit(e) {
  e.source.toast('entry');
  const sh = e.range.getSheet();
  if (sh.getName() == "Sheet0" && e.range.columnStart == 1 && e.range.rowStart == 1 && e.value == "TRUE") {
    e.range.setValue(false);
    e.source.toast('Flag1');
    setValueTest();
  }
}

So I guess I can't reproduce your problem with your code.

Perhaps you should try switch to another container and recreate the script. — Cooper, Dec 31 '21 at 22:14

Google Spreadsheet Script Permission Error

1 Answers1