Google Contacts (People API) query not working in script called from trigger script

UPDATE

I've determined that this is an authentication problem. For some reason I can perform People API queries when I'm running the function from the editor, but from the trigger I get this message from the executions log:

> GoogleJsonResponseException: API call to people.people.searchContacts
> failed with error: Request is missing required authentication
> credential. Expected OAuth 2 access token, login cookie or other valid
> authentication credential. See
> https://developers.google.com/identity/sign-in/web/devconsole-project.
> at searchContactsMain(searchContactsMain:6:29)
> at onEdit(onEdit:21:7)

Anyone can point me to the right direction?

ORIGINAL POST

First, I have a script that takes the text in cell A1 of a sheet and queries my Google Contacts using it. It lists all the results to column B. This script works.

function searchContactsTest() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet()
                            .getSheetByName("MAIN");
  var query = sheet.getRange('A1').getValue();

  let found = People.People.searchContacts({
    "query": query,
    "readMask": "names,addresses"
  });

  if (found.results) {
    var contactNames = [];

    for (i = 0; i < found.results.length; i++) {
      let thisResult = found.results[i];
      contactNames[i] = [];
      contactNames[i].push(thisResult.person.names[0].displayName);
      Logger.log(`Result ${i}: ${thisResult.person.names[0].displayName}`);
      if(thisResult.person.addresses){
        Logger.log(`Result ${i}: ${thisResult.person.addresses[0].streetAddress}`);
      }
    }
  } else {
    Logger.log(`No results for ${query}`);
  }

  if (found.results.length > 0) {
    // Write results to column B starting from row 2
    sheet.getRange("B2:B").clearContent(); // Clear previous results
    var outputRange = sheet.getRange(2, 2, contactNames.length, contactNames[0].length);
    outputRange.setValues(contactNames);
  }
}

Now then, I want to use the People.searchContacts query in another context: when the user clicks on a cell containing a name. I'm using the onSelectionChange trigger for this. I'm turning the clicked cell background red as a test of the trigger, then I call my function displayContact(range).

function onSelectionChange(e) {
  const range = e.range;
  sheetID = range.getSheet().getSheetId().toString();

  if (sheetID == "1060200918") //Main front end sheet
  {
    if (range.getNumRows() === 1 &&
      range.getNumColumns() === 1 &&
      range.getCell(1, 1).getValue() != '') 
      {
        range.setBackground('red');
        displayContact(e);
      }
  }
}

This works too.
Next, in displayContact, I do a People.searchContacts, using the value of the cell that triggered the event. The script should write the address of the contact returned to A2.

function displayContact(e) {
  range = e.range;             //**** Getting range from event object ****

  sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("MAIN");
  testOut = sheet.getRange("A2");

  //range = sheet.getRange("B2"); //**** Hardcoding the range ****
  var query = range.getValue();

  //Test to see if we're accessing the cell that was clicked on.
  testOut.setValue(query);   

  let found = People.People.searchContacts({
    "query": query,
    "readMask": "names,addresses"
    //"readMask": "names,addresses,emailAddresses,phoneNumbers"
  });
  
  range.setBackground('blue');

  if (found.results) 
  {
    let thisResult = found.results[0];
    let contactAddress = thisResult.person.addresses[0].streetAddress;
  
    //sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("MAIN");
    outRange = sheet.getRange("A3");
    outRange.clearContent(); // Clear previous results
    outRange.setValue(contactAddress);
  }
  
  else 
  {
    Logger.log(`No results for ${query}`);
  }
}

Line 11 writes the value of the cell I'm clicking on (I'm using B2) to cell A2, to test whether the event object is being passed correctly. That works. However, line 19 which turns the cell background blue as a test (Logger produces no output for triggered events) never executes. The query never happens, and the person's address never gets written to A3.

HOWEVER, if I comment out line 2, uncomment line 7, and run it from the editor instead of letting the trigger handler run it, everything works perfectly. Line 7 hardcodes the range to the exact same cell I'm clicking on. The same value gets written to A2. Everything is exactly the same except that I'm setting the range manually rather than getting it from the event object, and running the script from the editor.

What am I doing wrong?

The error message is related to the simple trigger. In the Google Documentation here, it states the following under the restriction of Simple triggers:

> They cannot access services that require authorization. For example, a
> simple trigger cannot send an email because the Gmail service requires
> authorization, but a simple trigger can translate a phrase with the
> Language service, which is anonymous.

That is why, is working under the editor, but is unable to authenticate using the trigger.

I will recommend to maybe use an installable trigger for this situation, instead of a simple trigger. Either by adding the trigger manually or adding it programmatically. However, take in consideration that the onSelectionChange(e) is not available.

Maybe you can use an onEdit trigger, and limit the change to a specific range. For example, adding a drop-down in cell A1 with all the names of the contacts. Once the user change the value, it will paste the results in column B, and return the Address in A2 or whatever the codes need to do.

Add an If statement that it will only do those changes under the condition that the change is only done in A1. You can use the method getA1Notation() for the if statement.

With this code, I share the Google Spreadsheet Phonebook with certain people, add a group called schools to the directory and add them to that group and share them.
Google allows to make a maximum of 200 queries, so I get the whole phonebook and table in Array list and encounter if(!veri3.includes(veri2[i]))

check if there is a group named schools in the phone book, if not, add a group named schools

 var groupName = "Okullar";
  var groups = People.ContactGroups.list()["contactGroups"];
  var group = groups.find(group => group["name"] === groupName);
  // 3. CREATE GROUP IF DOESN'T EXIST:
  if (!group) {
    var groupResource = {
      contactGroup: {
        name: groupName
      }
    }
    group = People.ContactGroups.create(groupResource);
  }
  var groupResourceName = group["resourceName"];

get data of sheet named list from table

 var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Liste");
var dataRange = sheet.getRange(2, 1, sheet.getLastRow()-1, 7)
var data = dataRange.getValues();

Getting the sheet named List in the table into Array list

  //Tabloyu Listeye alma
      var veri1=[];
    for (var i=0;i<data.length;i++){
      veri1.push([data[i][3]]); 
     }
    veri3=veri1.flat()


  var veri1=[];
for (var i=0;i<data.length;i++){
  veri1.push([data[i][3]]); 
 }
veri3=veri1.flat()

import data from phonebook to Array List

//Rehberi Listeye ALma
  var options = {
    pageSize:1000,
    personFields: 'names,emailAddresses,phoneNumbers,metadata'
  };
  var callback = People.People.Connections.list
  do{

    response = callback('people/me',options)

//response.connections.forEach(connection => connection.names ? isim.push(Utilities.formatDate(new Date(connection.metadata.sources[0].updateTime),"GMT", "dd.MM.yyyy")) : '');
response.connections.forEach(connection => connection.emailAddresses ? veri.push(connection.emailAddresses?.map(email => email.value)):'');

  options.pageToken = response.nextPageToken
  } while(options.pageToken)


veri2=veri.flat()

People Api asks for tokens for subsequent records after a certain registration, that is, there are 2500 contacts in your phone book, it does not show you 2500 records at once, it shows 1000 records so that you can get the next 1000

records options.pageToken = response.nextPageToken
   } while(options.pageToken)

this part must be in your code

this section compares two array lists

 for (var i=0;i<veri3.length;i++){

if(!veri2.includes(veri3[i]))  

Adding a contact that is in the table but not in the phonebook to the phonebook

 for (var i=0;i<veri3.length;i++){

if(!veri2.includes(veri3[i]))  


 {
   var contactResource = {
     "names": [{
      "givenName": data[i][0],
      "familyName": data[i][1],
    }],
     "phoneNumbers": [{
      'value': "0"+data[i][4]
    }],
    "emailAddresses": [{
      'value': data[i][3]
    }],
    "organizations": [{
       "name": data[i][6],
      "title": data[i][5],
      "type": "work"

    }],
  }

add the contact you added to the phone book to the group called schools

 var contactResourceName = People.People.createContact(contactResource)["resourceName"];
 var groupResourceName = group["resourceName"];
  // 4. ADD CONTACT TO GROUP:
  var membersResource = {
    "resourceNamesToAdd": [
      contactResourceName
    ]
  }
  People.ContactGroups.Members.modify(membersResource, groupResourceName); 

this code is to delete the person deleted from the table from the phone book

function tablodansilinenlerirehberdensilme() 

the whole code

function sharephonebook() {
      var veri=[];
  var veri2=[];
    var veri3=[];
  var response;

// grup yapma........................................................

  var groupName = "Okullar";
  var groups = People.ContactGroups.list()["contactGroups"];
  var group = groups.find(group => group["name"] === groupName);
  // 3. CREATE GROUP IF DOESN'T EXIST:
  if (!group) {
    var groupResource = {
      contactGroup: {
        name: groupName
      }
    }
    group = People.ContactGroups.create(groupResource);
  }
  var groupResourceName = group["resourceName"];



  // tabloyu alma
 var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Liste");
var dataRange = sheet.getRange(2, 1, sheet.getLastRow()-1, 7)
var data = dataRange.getValues();

//Tabloyu Listeye alma
  var veri1=[];
for (var i=0;i<data.length;i++){
  veri1.push([data[i][3]]); 
 }
veri3=veri1.flat()


//Rehberi Listeye ALma
  var options = {
    pageSize:1000,
    personFields: 'names,emailAddresses,phoneNumbers,metadata'
  };
  var callback = People.People.Connections.list
  do{

    response = callback('people/me',options)
    
//response.connections.forEach(connection => connection.names ? isim.push(Utilities.formatDate(new Date(connection.metadata.sources[0].updateTime),"GMT", "dd.MM.yyyy")) : '');
response.connections.forEach(connection => connection.emailAddresses ? veri.push(connection.emailAddresses?.map(email => email.value)):'');
  
  options.pageToken = response.nextPageToken
  } while(options.pageToken)


veri2=veri.flat()


  for (var i=0;i<veri3.length;i++){

if(!veri2.includes(veri3[i]))  


 {
   var contactResource = {
     "names": [{
      "givenName": data[i][0],
      "familyName": data[i][1],
    }],
     "phoneNumbers": [{
      'value': "0"+data[i][4]
    }],
    "emailAddresses": [{
      'value': data[i][3]
    }],
    "organizations": [{
       "name": data[i][6],
      "title": data[i][5],
      "type": "work"

    }],
  }
  var contactResourceName = People.People.createContact(contactResource)["resourceName"];
 var groupResourceName = group["resourceName"];
  // 4. ADD CONTACT TO GROUP:
  var membersResource = {
    "resourceNamesToAdd": [
      contactResourceName
    ]
  }
  People.ContactGroups.Members.modify(membersResource, groupResourceName);  

Logger.log("bulunmadu"+ veri3[i])

  } 



 }

tablodansilinenlerirehberdensilme()

  };




function tablodansilinenlerirehberdensilme() {
//Sabitler  
  var veri=[];
  var veri2=[];
  var veri4=[];
  let x=1;
//OKullar Grubunun Kodunu Alma
  const people = People.ContactGroups.list();
  const group1 = people['contactGroups'].find((group) => group['name'] === 'Okullar');
  //console.log(group1.resourceName);
  
    // tabloyu alma
 var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Liste");
var dataRange = sheet.getRange(2, 1, sheet.getLastRow()-1, 7)
var data = dataRange.getValues();

//Tabloyu Listeye alma
  var veri1=[];
for (var i=0;i<data.length;i++){
  veri1.push([data[i][3]]); 
 }
veri3=veri1.flat()
//Logger.log(veri3)

//Okullar Rehberini Listeye Alma
  var options = {
    pageSize:1000,
    personFields: 'names,emailAddresses,phoneNumbers,metadata,memberships'
  };
  var callback = People.People.Connections.list
    do{
     response = callback('people/me',options)
     response.connections.forEach((person) => {
      // if contacts/connections is available, print the name of person.
      if (person.memberships[0].contactGroupMembership.contactGroupResourceName === group1.resourceName) {
        veri.push(person.emailAddresses[0].value);
        veri4.push(person.resourceName);
        return;
      }
      });
      options.pageToken = response.nextPageToken
      } while(options.pageToken)
 
   veri2=veri.flat()
  // Logger.log(veri2)
//Tabloyu Listeye Alma
 //veri2 flatlanmış Rehberlistesi
  for (var i=0;i<veri2.length;i++){

if(!veri3.includes(veri2[i]))  {
  
  People.People.deleteContact(veri4[i])
 
}
  }
  
}


`