How to grant permission to self-defined functions?

Question

I have in Google Sheets a table with the name of a contact in column 1 and want to retrieve their email address in column two.

This is the code I have used:

function email(name) {
  var contacts = ContactsApp.getContactsByName(name);
  var emailAdd = contacts[0].getPrimaryEmail();
  return emailAdd;
}

I now try to test this function in a Sheet, by writing =email(A1) (for example) in column 2, and get the following error message:

You do not have permission to call getContactsByName.

How can I overcome this problem? I have tried to go into Resources>Advanced Google services and enabling the contacts API, without more luck.

Answer 1

Custom functions can't call services that require authorization. Source: https://developers.google.com/apps-script/guides/sheets/functions.

The way to overcome the problem is to use another way for calling the function, like assign the script to a custom menu, an image, an installable trigger or by running it from the Script Editor.