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.