Google Sheets "You do not have permission to call appendRow"

Question

function myFunction() {
    var url = 'https://api.github.com/users/chaimf90/repos'
    var response = UrlFetchApp.fetch(url);
    var json = response.getContentText();
    var data = JSON.parse(json)
    var sheet = SpreadsheetApp.getActiveSheet();
    sheet.appendRow(['Repo Name', data[0].name]);
}

When I execute this function from the script editor it runs as expected, but when I try to call this function in the sheet itself by calling =myFunction(), I get an error saying that I do not have permission to call appendRow.

Why can I call this function from the script editor, but not from the sheet itself?

Answer 1

I had the same problem. The solution seems to be to create a custom menu that runs an Apps Script function instead of writing a custom function and invoking it from a cell in the spreadsheet.

A function that is triggered from a menu will ask the user for authorization if necessary and can consequently use all Apps Script services. I learned this here.

Example:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('My Custom Menu')
      .addItem('Run My Function', 'myFunction')
  .addToUi();
}

function myFunction() {
  SpreadsheetApp.getUi()
     .alert('Running My Function');
}

After writing and saving this code:

1. Close and re-open your google sheets document.
2. After a few seconds, a new menu "My Custom Menu" will appear at the top, next to File, Edit, View, ..., Help.
3. Click on "My Custom Menu" and then click on "Run My Function" in order to invoke the function myFunction.