I want to write code for an Apps Script connecting Google Contacts and Sheet.
Background: Let’s say there are two people, A and B. When a new contact is created/added either in A’s or B’s Google contacts, does it automatically get added as a new row in ONE COMMON GOOGLE SHEET?
This is the piece of code I used. However it is pulling new contacts only from the user A and not User B. Although it is running for both users and getting other contacts too, but new ones for user B are not updating.
// Function to retrieve contacts from People API and append to the common Google Sheet
function exportContactsToSharedSheet() {
// Replace with the ID of the shared Google Sheet where contacts will be stored
var sharedSheetId = 'PUT_SHARED_SHEET_ID_HERE';
var sheet = SpreadsheetApp.openById(sharedSheetId).getActiveSheet();
// Identify the user adding the contacts
var currentUser = Session.getActiveUser().getEmail();
// Call People API to get connections (i.e., contacts)
var people = People.People.Connections.list('people/me', {
personFields: 'names,emailAddresses,phoneNumbers',
pageSize: 1000
}).connections;
if (people && people.length > 0) {
for (var i = 0; i < people.length; i++) {
var person = people[i];
var name = person.names && person.names.length > 0 ? person.names[0].displayName : 'No Name';
var email = person.emailAddresses && person.emailAddresses.length > 0 ? person.emailAddresses[0].value : 'No Email';
var phone = person.phoneNumbers && person.phoneNumbers.length > 0 ? person.phoneNumbers[0].value : 'No Phone';
// Append contact details to the shared sheet
sheet.appendRow([name, email, phone, currentUser]);
}
} else {
Logger.log('No contacts found.');
}
}
// Set up time-based triggers for automation (e.g., every hour)
function setupTriggers() {
ScriptApp.newTrigger('exportContactsToSharedSheet')
.timeBased()
.everyHours(1) // Set interval as per your needs
.create();
}
New contributor
Jayashree Nandi is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1
Getting the new Contact from User A and User B
The reason why the script isn’t working is because User B will also need to run the setupTriggers() function. As per Restrictions:
Installable triggers always run under the account of the person who created them.
I also modified the script to add a condition inside the for loop to allow both users to add new contacts from their respective ends.
There’s also a discrepancy where duplicate values were being added to the spreadsheet. To improve this, I implemented a function that prevents users from adding existing contacts. Only the latest entry will be added to the spreadsheet.
Script used for exportLatestContactsToSharedSheet() & getExistingContacts()
function exportLatestContactsToSharedSheet() {
var sharedSheetId = 'PUT_SHARED_SHEET_ID_HERE';
var sheet = SpreadsheetApp.openById(sharedSheetId).getActiveSheet();
var currentUser = Session.getActiveUser().getEmail();
Logger.log('Current User: ' + currentUser);
var existingContacts = getExistingContacts(sheet);
Logger.log('Existing Contacts: ' + JSON.stringify(existingContacts));
var people = People.People.Connections.list('people/me', {
personFields: 'names,emailAddresses,phoneNumbers',
pageSize: 1000
}).connections;
if (people && people.length > 0) {
Logger.log('Number of Contacts Found: ' + people.length);
var newContacts = 0;
for (var i = 0; i < people.length; i++) {
var person = people[i];
var name = person.names && person.names.length > 0 ? person.names[0].displayName : 'No Name';
var email = person.emailAddresses && person.emailAddresses.length > 0 ? person.emailAddresses[0].value : 'No Email';
var phone = person.phoneNumbers && person.phoneNumbers.length > 0 ? person.phoneNumbers[0].value : 'No Phone';
if (!existingContacts[email]) {
sheet.appendRow([name, email, phone, currentUser]);
newContacts++;
Logger.log('Added Contact: ' + name + ', ' + email);
} else {
Logger.log('Existing Contact: ' + email);
}
}
if (newContacts === 0) {
Logger.log('No new contacts to add.');
} else {
Logger.log(newContacts + ' new contact(s) added.');
}
} else {
Logger.log('No contacts found.');
}
}
function getExistingContacts(sheet) {
var data = sheet.getDataRange().getValues();
var existingContacts = {};
for (var i = 1; i < data.length; i++) {
var email = data[i][1];
if (email) {
existingContacts[email] = true;
}
}
return existingContacts;
}
function setupTriggers() {
ScriptApp.newTrigger('exportLatestContactsToSharedSheet')
.timeBased()
.everyMinutes(1)
.create();
}
Sample Output
Validation for existing Data
Spreadsheet
Note:
- Make sure that the People API is added in the Services.
- Make sure the user has Editor access.
- You can still add the
setupTriggers()or set manually in Installable Triggers.
Reference
-
getDataRange()
-
SetupTrigger()