How to set permissions to allow Google Sheets to use an Apps Script to query my Contacts?

I have a Google Sheet that I want to check if an email address already exists in my list of Contacts.

I wrote an Apps Script with this function

function isAContact(email) {
    if (null != ContactsApp.getContact(email)) {
        return 1;
    }

    return 0;
}

If I run this function directly in Google Apps Scripts it works fine, after it asked for permission on the first run.

When I call this function in Google Sheets

=isAContact(H4) // H4 is a cell with an email address

it complains that the permission to call getContact() is denied.

How can I allow this spreadsheet to use the Contacts Api?

Solution

Work arounds include: Timed trigger pulling in your contacts list every 5 min to a hidden sheet and setting up your comparison against that.

changing your function to work on the active range or a preset range when triggered from a custom menu: https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#getUi()