Fixing permission problems in cross-spreadsheet import when using Apps Script

Question

I created a function in the Apps Script associated with a spreadsheet:

function importNotes() {
      s = SpreadsheetApp.openById('spreadsheet-key').getRange('F2').getNotes();
      return s;
}

Note that the spreadsheet the function is attached to is not the one that the note is being read from. Both are owned by me.

When I step through the function in the debugger, it works, e.g., sets the local variable to [['This is a note']]. (Of course, I use an actual spreadsheet key.)

When I put this in a cell in the associated spreadsheet:

=importNotes()

I get an error:

You do not have permission to perform that action. (line 3).

I've tried renaming the function to defeat caching and reloading the spreadsheet, but I continue to get the error.

How can I fix the permission problem?

Answer 1

This is one of restrictions imposed on custom functions: they cannot perform tasks that require authorization, such as accessing user's files. From the documentation:

Unlike most other types of Apps Scripts, custom functions never ask users to authorize access to personal data. Consequently, they can only call services that do not have access to personal data, specifically the following:

[a list that does not include SpreadsheetApp]

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.

Another solution, perhaps more suitable for your case, is to use ImportRange in your spreadsheet with the function, and access those cells. A custom function can access read-only methods of Spreadsheet service, such as getSheetByName, getRange, etc.