Google Sheets

Are you still using Excel or Sheets to store and share contacts? It is undeniably inconvenient when collaborators need to periodically open the file, copy the details, and paste them into their Google Contacts before they can call, text, or email the new contacts. Hence, this article provides solutions that eliminate the need for team members to perform the time-consuming and repetitive task of extracting contacts from a spreadsheet. Solution 1: Using an automation platform like Zapier Cost: 💰💰 Setup: ⚙️⚙️⚙️ Security: 🔒 Pros: 🟢 No coding needed 🟢 One-time setup for the whole workflow Cons: 🔴 All users sharing their Google credentials in a specific Zapier account can be risky 🔴 Expensive for small teams There are other connector apps, such as n8n, IFTTT, and Make (formerly Integromat), but Zapier takes the place as the top choice for no-code app-to-app connections. In essence, these apps automatically create an entry in Google Contacts each time a row is added from the selected online spreadsheet (whether Excel, Google Sheets, Smartsheet, etc). Zapier, like other platforms, offers free plans. However, the number of actions (called zaps) is minimal. To increase the capacity, a paid plan must be availed at $19.99 per month. This middleware does not require coding, but the one-time setup takes a lot of steps. It also includes giving Zapier the credentials of everyone who would be receiving the contacts from the spreadsheet. Steps: Sync Sheets with Contacts Using Zapier Part 1: Preparing the spreadsheet Google Sheets works best, though Zapier connects with most online spreadsheets. Ensure that the first row of the spreadsheet includes proper headers, such as the fields you have for Google Contacts (first name, middle name, last name, email-1 value, email-1 label, etc.). It would help if you follow the format of a filled .csv file according to your preference. Verify that there is only one contact entry per row and that the data matches the header (i.e, first name and last name are separated and placed under the corresponding column). Confirm that all contacts are in a single sheet. Part 2: Setting the Zapier account Go to Zapier.com Sign in to the account you want to use. Allow Zapier access to that account. Answer prompts until you reach the Zapier home page. 1. Go to Zapier. 2. Sign in. 3. Permit Zapier. 4. Complete the prompts. Part 3: Creating the automation. In Zapier, the automation process that connects the apps, in this case Google Sheet and Google Docs, is called a zap. A zap has two parts: the trigger (e.g. a new row created for a new contact) and the action (e.g. create a new entry on the address book). Click +Create button, then Zaps. Assign values for the trigger. In the Setup tab, enter the following data: App: Google Sheet Trigger event: New or Updated Spreadsheet row Account: Enter the account of the owner of the Sheet to be called. Click Continue. In the Configure tab, enter the following data: Spreadsheet: Ensure that the correct file name is entered. Worksheet: Ensure that the correct sheet is entered. Trigger column: The column that would enable the creation of a new entry. (New rows always trigger the Zap, even if they’re blank—so add a filter to ignore empty rows.) Test the trigger. Assign values for the action. In Setup tab, enter the following data: App: Google Contacts Action event: Create Contacts. Account: The account where the contacts should go. (You cannot enter multiple accounts. You must create one Zap for each account that needs the contacts from the Sheet. In the Configure tab, match the field in Google Contacts with the corresponding column in the Sheet. Test the action. Publish the Zap. 1. Click +Create, then Zaps. 2. Click Trigger. 2.a. Enter values in the Trigger Setup tab. 2.b. Enter values in the Trigger Configure tab 2.c. Test the Trigger. 3. Click Action. 3.a. Enter values in the Action Setup tab. 3.b. Match the correct values. 3.c. Test the Action. 4. Click Publish. Solution 2: Using Google Apps Script Cost: 💰 Setup: ⚙️⚙️⚙️⚙️ Security: 🔒🔒🔒🔒 Pros: 🟢 All data and credentials stay within the Google Workspace environment. 🟢 No additional subscription fees. Cons: 🔴 Knowledge of JavaScript is needed 🔴 Does not work with a high amount of data Google users can enjoy automating repetitive tasks, such as updating Contacts from a spreadsheet, using Google Apps Script. So, if your contact list is in Excel or another online spreadsheet, you would need to transfer it to a Google Sheet first to continue with this method. The script will push all the Sheet contact updates into the Contacts of the user who runs it. To distribute the contacts among other users, they need to run the script in their account as well. Personal Gmail users can use this tool as well, but they have a lower quota and higher restrictions as compared to Google Workspace accounts. Steps: Sync Sheets with Contacts Using Google Apps Script Part 1: Preparing the spreadsheet Pointers to have the Google Sheet ready for the Google Apps Script: The first row must be headers (e.g. first name, last name, email, etc) that you want to be filled in the Google Contacts. There should be one contact entry per row. The columns should have the same detail (e.g. all first names in the first column). Avoid blank rows in the middle of the data sheet. Part 2: Running the script Open the Sheet prepared for syncing. Go to Extensions, then App Script. Write the script or copy/paste one that maps the rows of the sheets with the contacts. Name the script, e.g. “Updates G Contacts” and save. Click Run. Grant needed permissions. On the left pane, select Triggers. Create a trigger that will launch the script every time the Sheet is updated. Note: This procedure must be done for each Google account that needs to access the contacts from the Sheets. 1. Open the Sheet. 2. Go to Extensions → Apps Script. 3. Write the script. 4. Name the script. 5. Click ‘Run’. 6. Grant needed permissions. 7. Select ‘Triggers’. 8. Create triggers. Solution 3: Exporting/Importing Contacts, Then Using Shared Contacts Dashboard Cost: 💰 Setup: ⚙️ Security: 🔒🔒🔒 Pros: 🟢 Easy setup and effortless updates with other users 🟢 Free for small teams and minimal costs for larger ones Cons: 🔴 Manual import/export of contact data needed (one-time or recurring). Shared Contacts Dashboard synchronizes contact lists with your team. Instead of using a spreadsheet to manage contacts, you can update them directly in Google, and the app will automatically share the changes with your team. If you have the contacts initially in the spreadsheet, you just have to upload the contact list to Google Contacts, and the app will automatically distribute it to your collaborators. Steps: Sync Sheets with Contacts Using Export/Import plus Shared Contacts Dashboard Make sure your spreadsheet is saved in CSV format on your computer, whether created offline or downloaded from the cloud, so it’s ready to import into Google Contacts. If the file is created or maintained offline, skip exporting the spreadsheet. Part 1: Exporting the spreadsheet For Google Sheet: Open the Google Sheet that contains the contacts to be shared. Go to File → Download → CSV (comma separated values). Note the file location where it was saved. For Microsoft Excel: Open the Microsoft Excel that contains the contacts to be shared. Go to File → Export → Download as CSV Note the file location where it was saved. Part 2: Importing to Google Contacts Open Google Contacts from Google apps or go to contacts.google.com On the left panel, click Import. Click on ‘Select file’. Select the CSV file that contains the contacts for sharing. Click ‘Import’. The newly imported contacts will appear under the label ‘Imported on (current date)’. You can rename the label (recommended) or keep it as is, just be sure to remember it’s the one you need to share. Note: Clicking the +Label on the dialog box allows you to add the contacts to an existing label or create a new label. However, a label named ‘Imported on (current date) would still appear. 1. Open Google Contacts. 2. Select ‘Import’. 3. Click ‘Select file’. 4. Select the correct CSV file. 5. Click ‘Import’. 6. (Optional) Rename the label. Part 3: Sharing through Shared Contacts Dashboard Install Shared Contacts Dashboard from Google Workspace Marketplace. It is recommended to do an admin install so everyone in your team can already have it on their account. Open the app by clicking on the ‘Sign in’ at the upper right corner of this website. Hover on the label you created for sharing and click the share icon for it. Add the users who would be accessing the label. Set access permissions level accordingly. Click ‘Share’. Note: If you are sharing outside of your domain or with personal Gmail users, they should give permission to the app first before they can access the shared contacts. 1. Install from Marketplace. 2. Open Shared Contacts Dashboard. 3. Click the share icon. 4. Add collaborators. 5. Set permissions. 6. Click ‘Share’. Solution 4: Hybrid Solution (combined 1 & 3) - The best of all worlds Cost: 💰💰 Setup: ⚙️⚙️ Security: 🔒🔒🔒 Pros: 🟢 No need to manually export/import the spreadsheet 🟢 No need to access other users’ credentials Cons: 🔴 Any change to the spreadsheet structure (columns renamed, reordered) breaks the Zap. In this setup, Zapier pushes any spreadsheet contact updates to the main Google account. You only need to configure Zapier for that account, as Shared Contacts Dashboard will automatically sync updates with the rest of the team. The setup is as follows: Follow the steps in Solution #1, but apply them only to the main account. Share the contacts with the rest of the team following Solution #3, Part 3. This keeps the password of each collaborator private as it is no longer needed to be entered into Zapier. Takeaway: Sychronizing contacts from spreadsheet to a team no longer needs to be tedious and time consuming. With a number of solutions available there ought to be one that suits your business needs.

4 Ways to Synchronize Contacts from Google Spreadsheet to Your Team’s Google Contacts