Objective: Write a script to add contacts from a Google Sheet into Google Contacts, checking for duplicates to avoid redundant entries.
Steps:
Ensure your Google Sheet includes columns for First Name, Last Name, and Email.
Open Extensions > Apps Script.
Replace the existing code with:
function syncContacts() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const data = sheet.getDataRange().getValues();
const contacts = ContactsApp.getContacts();
data.forEach((row, index) => {
if (index === 0) return; // Skip headers
const email = row[2];
const existingContact = contacts.find(contact => contact.getEmails().some(e => e.getAddress() === email));
if (!existingContact) {
const contact = ContactsApp.createContact(row[0], row[1], email);
}
});
}
Save and name your project.
Run syncContacts.Explanation: This script checks each row in the Google Sheet against existing contacts to see if the email address already exists. If not, it creates a new contact with the provided details.
Sample Table Data for SyncContacts Script
The syncContacts() function in Google Apps Script is designed to synchronize contact information from a Google Sheet to the user’s Google Contacts. This script reads data from the active spreadsheet, checks if a contact already exists based on the email address, and if not, creates a new contact with the provided name, surname, and email.
Here’s an example of how your data in Google Sheets might be organized to work with this script:
| First Name | Last Name | |
|---|---|---|
| John | Doe | john.doe@example.com |
| Jane | Smith | jane.smith@example.com |
| Alice | Lee | alice.lee@example.com |
Column Descriptions:
- First Name: The first name of the contact.
- Last Name: The surname or last name of the contact.
- Email: The email address of the contact. This is used to check if the contact already exists in Google Contacts.
How the Script Works with the Data:
- Data Retrieval: The script retrieves all the data from the active sheet starting from the first row which contains headers (“First Name”, “Last Name”, “Email”).
- Process Each Row: For each row after the header, the script:
- Extracts the email address.
- Searches the existing contacts to see if there is a match for this email.
- If no existing contact is found with that email, a new contact is created using the first name, last name, and email from the row.
Tips for Data Management:
- Data Accuracy: Ensure that email addresses are accurately entered as they are key to identifying unique contacts.
- Formatting Consistency: Maintain consistent formatting for names and emails to prevent issues during synchronization.
- Header Row: The first row is assumed to be headers and is skipped by the script, so always include headers for clarity.
This setup makes it easy to manage and sync contact information, reducing manual entry errors and enhancing workflow efficiency.