How to make a mailing list from Google Contacts

The following steps help you transform names and addresses you’ve saved in Google Contacts into a standard (comma-separated value or .csv) file you may use as a data source for postal service 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 you use a desktop-class browser, such as Chrome, since many of the export, import and data cleanup steps benefit from a laptop or desktop sized screen.

SEE: Electronic communication policy (TechRepublic Premium)

1. Export contacts

First, go to Google Contacts on the web, then select Export from the left-side menu. If the menu isn’t displayed, click or tap on 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 may select the drop-down menu, then Starred contacts, Frequently Contacted or any set of contacts by Label. (Alternatively, you may individually select contacts before you start the export process. To do this, click or tap on each contact’s profile image, then select Export.) If you plan to export contacts often, I suggest you create and apply labels to organize contacts in groups, since that makes selection simpler.

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

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

Figure A

2. Import contacts into a Google Sheet

Create a new Google Sheet (e.g., type sheet.new in Chrome), then select File | Import. Navigate to one of the contact files you saved above, choose it, then click (or tap) on Select. This should display an Import file screen. 

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

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

Leave the Separator type as 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 contacts files on one Google Sheet.

Figure B

3. Delete duplicate header rows

If you imported more than one contact file, you will have a header row for each import. Keep only the first header row (i.e., row 1). Scroll down to each additional imported header row, click on the number of the row to select it, then choose Edit | Delete row (Figure C). After this step, you should have headers in row 1 along with all your contacts in a single sheet.

Figure C

4. Delete columns 

Next, delete all the non-address columns (Figure D). Obvious columns to delete will be all columns to the right of column BY (i.e., Relation 1- Type). But you also will want to delete all other non-address fields, including email, birthday, directory server, mileage, etc. The goal is to end up with a person or organization and address data on each row.

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

As you delete, remove combined columns and keep individual columns of data, since these separate columns streamline sorting later in the process. For example, remove Column A (e.g., “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 (e.g., home, work or additional work locations), review your data carefully before you delete any column with Street, City, P.O. Box, Region, Postal Code, Country or Extended Address information. In some cases, you may need to cut-and-paste address information from one set of columns into another (e.g., for a home to work address or vice versa) to ensure that all address information is contained in a consistent set of columns.

Figure D

5. Delete duplicates

If you’ve combined contact lists from multiple people, sort your list to identify duplicates. I suggest you sort three different ways: By business name, last name and street 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 a different 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. Sheets will indicate the number of rows removed and the number of rows that remain. (For more 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 might replace Road, Route, Street, Suite and Avenue, respectively, with RD, RTE, ST, STE and AVE for mailing purposes. (Review common abbreviations at USPS.com, as in Figure E.) In Sheets, use Edit | Find and Replace to streamline this process.

Figure E

7. Export your list

Select File | Download | Comma-Separated Values (.csv, current sheet) to export your list, as shown in Figure F. This creates and downloads a file with the file name of your document combined with the name of your sheet (e.g., 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 may use as the source data for your mailing.

If you use a mailing service, it may take additional actions on your list to standardize mailing abbreviations you may have missed, add postal code data (e.g., ZIP+4) or update addresses due to moves (e.g., with the National Change of Address database). Mailing services may charge fees for these changes. You might want to update the names and addresses in Google Contacts with any changes identified.

Figure F

What’s your experience?

Do you use Google Contacts as a data source for mailings? Do you standardize address information to reflect mailing standards when you enter data? How methodically do you update address information in Google Contacts after a mailing? Are there additional services you suggest that make mailings easier? Let me know, either in the comments below or on Twitter (@awolber).

Google Weekly Newsletter

Learn how to get the most out of Google Docs, Google Cloud Platform, Google Apps, Chrome OS, and all the other Google products used in business environments.
Delivered Fridays

Sign up today

Also see