How to set oauthscope to use Google Contact API in Google Spreadsheet custom function?

Question

I'm trying to create a very simple custom function for google spreadsheet to get the full name of a person from the email using the Google Contact API.

/**
 * Get the Full Name from the email.
 * @param {email} input The email to get the full name.
 * @return The full name of the contact.
 * @customfunction
*/
function GETNAME(input) {
  var user = ContactsApp.getContactsByEmailAddress(input);
  return user[0].getFullName()
}

The function runs perfectly in the AppScript but when I try to use it as a custom function in Google spreadsheet i got the following error message :

Exception: You do not have permission to call ContactsApp.getContactsByEmailAddress. Required permissions: https://www.google.com/m8/feeds

I have the edited my appsscript.json file to edit the oauthscope as followed :

{
"oauthScopes": [
  "https://www.google.com/m8/feeds/"],

  "timeZone": "Europe/Paris",
  "dependencies": {
  },
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8"
}

But I'm still getting the same error message. How can I set the permission?

Best regards

Richard

Answer 1

Tanaike pretty much already mentioned this in his comment, but I'll elaborate a little more.

Most Apps Script services cannot be used in custom functions, especially if they require user authorization. Check the documentation for the supported services.

If your custom function throws the error message You do not have permission to call X service., the service requires user authorization and thus cannot be used in a custom function.

The Contacts/People service requires authorization so there's no way to use it in a custom function. Instead you could consider running a function that reads all the emails in a column and writes their names to another column. For example:

function setNames() {
  var ss = SpreadsheetApp.getActiveSheet()
  var inputrange = ss.getRange(1, 1, ss.getLastRow()) //Column A
  var outputrange = inputrange.offset(0, 1) // Column B

  outputrange.setValues(inputrange.getValues().map(email => [ContactsApp.getContactsByEmailAddress(email[0])[0].getFullName()]))
}

Note that as also mentioned by Tanaike, the ContactsApp service is deprecated so you'll want to use the Advanced People service instead. It's trickier to use, but here's a sample based on the previous one:

outputrange.setValues(inputrange.getValues().map(email => [People.People.searchContacts({"query":email[0], "readMask": "names"}).results[0].person.names[0].displayName]))

This is just a sample and there's work to be done for a usable function, but I hope that this gives you a starting point. The bottom line is that you will need a complete script if you want to access Contacts data since custom functions won't work.