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.

  • @Rubén Is there a reason you removed the worksheet-function tag? I think that's apropos here. You can always ping an editor and ask them about their change rather than just reversing it.
    – jonsca
    Sep 9, 2016 at 23:01
  • @jonsca: The reason to remove the worksheet-function tag is that it's not related to the question. The excerpt for this tag says: "This tag is for spreadsheet questions where the question or answers involve an in-the-cell worksheet function, as opposed to some form of code." A custom function, like the one posted by the OP is not "opposed to some form of code", it actually uses Google Apps Script code. Sep 9, 2016 at 23:32

1 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.