Sync Gmail and Google Calendar recipients to Google Contacts - Part 2

In the previous post we looked at how to populate a Google Sheet with names and email addresses from our Gmail and Google Calendar accounts. In this post we'll see how to push this information into Google Contacts using the Google People API.

To get started, you can create a new file in the Apps Script IDE (I called mine "Sync.gs". The file will contain the code we need to update Google Contacts:

function syncContacts() {
  init_();
  const rows = g.nuSh.getRange(`A2:C${g.nuSh.getLastRow()}`).getValues();
  const saveUsers = rows
    .filter((row) => row[2] === 'Save user')
    .map((row) => [row[0], row[1]]);
  if (saveUsers.length > 0) {
    const contacts = saveUsers.map((user) => {
      let [givenName, ...familyName] = user[0].split(' ');
      familyName = familyName.join(' ');
      return {
        contactPerson: {
          names: [{ givenName, familyName }],
          emailAddresses: [{ value: user[1] }],
        },
      };
    });
    const resp = People.People.batchCreateContacts({
      contacts,
      readMask: 'names,emailAddresses',
    });
  }

Above, we initialize the script using the init_ function we defined in the previous post. We then use the global variable "g" to access the New users sheet. We extract the data from all the cells. We then filter the rows to only those that have the "Save user" action selected. We use "map" to extract the name and email from each row.

We check whether there are any such rows. If so, then we convert the whole name to a given name and a last name, by splitting the name on spaces. We then create an object with a "contactPerson" key that points to an object with the name and email address. We then use the People.People.batchCreateContacts method to create the new entry in Google Contacts.

We now need to copy any row with "Ignored user" or "Ignored domain" action to its respective tab:

const ignoredUsers = rows
    .filter((row) => row[2] === 'Ignore user')
    .map((row) => [row[0], row[1]]);
  let nextRow = 2;
  if (ignoredUsers.length > 0) {
    nextRow = g.iuSh.getLastRow() + 1;
    g.iuSh.getRange(nextRow, 1, ignoredUsers.length, 2).setValues(ignoredUsers);
  }

  const ignoredDomains = rows
    .filter((row) => row[2] === 'Ignore domain')
    .map((row) => [row[1].slice(row[1].indexOf('@') + 1)]);
  if (ignoredDomains.length > 0) {
    nextRow = g.idSh.getLastRow() + 1;
    g.idSh
      .getRange(nextRow, 1, ignoredDomains.length, 1)
      .setValues(ignoredDomains);
  }

The logic is similar to Google Contacts: Filter "New users" on the two actions. If there are such rows then get the next row from the destination tab, and paste the rows there.

Now we need to clean up the "New users" tab. After we pushed contacts into Google Contacts and into the two other tabs we need to remove them from New users to avoid duplications. So the only rows that should remain on the tab are those that don't have any action selected. We'll filter these rows, empty the sheet, and add back the rows and their dropdown menus:

const unselectedRows = rows.filter((row) => row[2] === '');
  g.nuSh.getRange('A2:C').clearContent().clearDataValidations();
  if (unselectedRows.length > 0) {
    g.nuSh.getRange(2, 1, unselectedRows.length, 3).setValues(unselectedRows);
    const rule = getValidationRule_();
    const ruleArr = new Array(unselectedRows.length).fill([rule]);
    g.nuSh
      .getRange(`C${nextRow}:C${nextRow + unselectedRows.length - 1}`)
      .setDataValidations(ruleArr);
  }
  SpreadsheetApp.flush();
}

This is pretty much it. Now we have two custom menu options in our Google Sheet: one populates with de-duped entries from Gmail and Calendar. The other handles each row based on its selected action.