Using ContactsApp from a Google spreadsheet

Question

Overview: When I call ContactsApp.getContact() in a GoogleAppsScript function, the function works fine when run from the Script Editor. However, when I try to use my function from a Google Spreadsheet, I get a permission error. How can I resolve this permission error and use ContactsApp from a custom function in a spreadsheet?

Details: I create a new spreadsheet and open the Script Editor and define the following simple function:

function example() {
  ContactsApp.getContacts();
  return 5;
}

The first time I run it from the script editor I am prompted to approve access to my contacts. I grant approval, and the function runs just fine. I can add logging and verify that it is working as intended.

Now back in the spreadsheet I use the formula '=example()' in a cell, expecting it to set the value of the cell to 5. Instead, the cell says "#ERROR!" and mousing over the cell shows the detailed error:

You do not have permission to call ContactsApp.getContacts. Required permissions: https://www.google.com/m8/feeds (line 2)

Looking at the project properties of the script, I can see that https://www.google.com/m8/feeds is indeed listed as a required oauth scope.

How can I resolve this permission error and use ContactsApp from a custom function in a spreadsheet?

Answer 1

I have found the answer in the documentation here: https://developers.google.com/apps-script/guides/sheets/functions#advanced

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: Cache HTML JDBC Language Lock Maps Properties Spreadsheet URL Fetch Utilities XML

So using the ContactsApp in a custom function as the question desires is not currently permitted because authorization would be required and custom functions don't permit this.