How to create a mailing list from Google Contacts

Create a file ready to be merged or used by an email service from one or more sets of Google contacts

Illustration: Andy Wolber / TechRepublic

The following steps help you transform the names and addresses that you saved in Google Contacts into a standard file (comma separated values ​​or .csv) that you can use as a data source for mailings. The steps also cover how to combine two or more sets of exported Google Contacts addresses into a single usable list without duplicates. I recommend using a desktop browser, such as Chrome, as most of the data exporting, importing and cleaning steps take advantage of a screen the size of a laptop or computer. Office.

SEE: Electronic communication policy (TechRepublic Premium)

1. Export contacts

What’s new at TechRepublic

First, go to Google Contacts on the web and then select Export from the menu on the left. If the menu does not appear, click or tap the three horizontal lines in the upper left corner to access the menu (Figure A).

By default, the system will export all contacts. To select a subset, you can select the drop-down menu, then Favorite contacts, Frequent contacts, or any set of contacts by tag. (You can also select contacts individually before starting the export process. To do this, click or tap on each contact’s profile picture and then select Export.) If you plan to export contacts often, I suggests that you create and apply labels to organize contacts into groups, as this simplifies the selection.

Choose the Google CSV Format button, and then select Export. The system should download and save a file to your computer. Typically, the name of the exported file is contacts.csv. You can rename the file to reflect the set of exported contacts with the date (for example, WolberContacts-20210818.csv).

Repeat the export process for each person whose Google contacts you want to include in your mailing list.

Figure A

Screenshot of Google Contacts, with the three horizontal line menu circled and an arrow pointing to Export (after Import).

Export all (or some) of your Google contacts in .csv format on the web.

2. Import contacts into a Google sheet

Create a new Google sheet (for example, type in Chrome), then select File | Import. Navigate to one of the contact files you saved above, choose it, then click (or tap) Select. This should bring up an import file screen.

For the first contact data file, select Replace Spreadsheet, then Import Data. For additional contact data files, select Add to current sheet to place the imported contacts in rows after the end of the current data (Number B).

SEE: How to sync your Google contacts with your iPhone (TechRepublic)

Leave the separator type on Detect automatically and also leave the box next to Convert text to numbers, dates and formulas checked. Select Import Data.

Repeat the import process for each file. You should end up with all of your imported contact files on one Google Sheet.

Number B

Screenshot of the import file with

In Google Sheets, import contacts. For the first contact list, select Replace worksheet as the import location. For additional lists, choose Add to Current Sheet.

3. Remove duplicate header lines

If you have imported multiple contact files, you will have a header row for each import. Keep only the first header row (that is, row 1). Scroll to each additional imported header row, click the row number to select it, and choose Edit | Delete line (Figure C). After this step, you should have headers in row 1 with all of your contacts in one sheet.

Figure C

Screenshot that shows an additional header row (row 45), with a red line crossing it to indicate the need to remove it.

If you are importing multiple contact lists into your spreadsheet, be sure to remove the extra header rows.

4. Remove columns

Then remove all unaddressed columns (Number D). The obvious columns to remove will be all columns to the right of the BY column (that is, 1-Type Relationship). But you’ll also want to remove all other unaddressed fields including email, birthday, directory server, mileage, etc. The goal is to end up with a person or organization and address data on each line.

SEE: How to create an envelope in Google Docs (TechRepublic)

As you remove, remove the combined columns and keep the individual data columns, as these separate columns streamline sorting later in the process. For example, delete column A (for example, “Andrew J. Wolber), which is a combined column that includes the contents of column B (” Andrew “), C (” J. “), and D (” Wolber) .

Since it is common to have more than one address for a person (for example, home, work, or additional workplaces), carefully review your data before deleting a column with Street, City, PO Box, Region, Code Postal, Country or Extended Address Information. In some cases, you may need to cut and paste the address information from one set of columns into another (for example, for a home-work address or vice versa) to ensure that all of the address information are contained in a coherent set of columns.

Number D

Screenshot of the email and phone columns displayed, with a red X on each column to indicate a need to remove it.

Select and remove all unaddressed columns like email addresses, phone numbers, etc.

5. Remove duplicates

If you combined the contact lists of several people, sort your list to identify duplicates. I suggest you sort in three different ways: By Business Name, Last Name, and Mailing Address. Review the list after each sort. Consolidate or delete data as desired. (For more details, read How to alphabetize in Google Sheets.)

SEE: How to migrate contacts to another Google account on your Android device (TechRepublic)

Additionally, Google Sheets can help you find and remove duplicate data. Select a range, then choose Data | Remove duplicates. The sheets will show the number of rows deleted and the number of rows remaining. (For details, read How to Find Duplicates in Google Sheets.)

6. Standardize abbreviations

The way many people enter addresses in Google Contacts may not match standard U.S. Postal Service abbreviations. For example, you can replace Road, Route, Street, Suite, and Avenue, respectively, with RD, RTE, ST, STE, and AVE for sending. (Review common abbreviations on, as in Encrypted.) In Sheets, use Edit | Find and Replace to streamline this process.


Screenshot of the USPS Street Suffix Abbreviations page, showing the alley abbreviated as ALY, the annex as ANX, ARCADE as ARC and AVENUE as AVE.

Use standard postal abbreviations whenever possible.

7. Export your list

Select File | Download | Comma separated values ​​(.csv, current sheet) to export your list, as shown in Number F. This creates and downloads a file with the filename of your document combined with the name of your sheet (eg Mailing List – contacts.csv). The comma separated file includes the header row with the names of the data fields. This is the file that you can use as a data source for your shipment.

If you use a postal service, they may take additional steps on your list to standardize postal abbreviations you may have missed, add postal code data (for example, ZIP + 4), or update addresses to due to relocations (for example, with the change of national database address). Postal services may charge a fee for these changes. You may want to update the names and addresses in Google Contacts with the identified changes.

Figure F

Download screenshot |  The comma separated values ​​menu is displayed.

Choose Download | Comma separated values ​​to export your mailing list from your spreadsheet in a standard format.

What is your experience ?

Are you using Google Contacts as the data source for mailings? Are you standardizing address information to reflect shipping standards when entering data? How do you methodically update address information in Google Contacts after a send? Are there any additional services you offer to make shipping easier? Let me know, either in the comments below or on Twitter (@awolber).

Also look

Leave a Reply

Your email address will not be published.