CODEWITHSUNDEEP
google-sheetsgoogle-apps-scriptgoogle-people-api
Jayashree Nandi
Jayashree Nandi

Reputation: 11

Connecting Google Sheets and Contacts

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();
}

Upvotes: 0

Views: 138

Answers (1)

Patsytalk
Patsytalk

Reputation: 884

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

Validation for existing Data

Spreadsheet

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

Upvotes: 1

Related Questions