9
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?

ReyAnthonyRenacia
  • 17,219
  • 5
  • 37
  • 56
Chaim Friedman
  • 6,124
  • 4
  • 33
  • 61
  • 2
    Here's an [SO post](http://stackoverflow.com/questions/36109437/you-do-not-have-permission-to-call-appendrow) describing their difference. You may also read about [Permissions and types of scripts](https://developers.google.com/apps-script/guides/services/authorization#permissions_and_types_of_scripts) for additional reference. Also, make sure you're the owner of the script you're modifying. – ReyAnthonyRenacia Apr 25 '17 at 12:13
  • 1
    Custom functions (apps script methods called from the worksheet) can only edit the cell they are called from (and those immediately below and right), and only by returning a value / array of arrays of values. – tehhowch Jul 11 '18 at 12:07

1 Answers1

13

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.
Keren Duchan
  • 131
  • 1
  • 3