Experiences on the Front Lines of User Interfaces and Web Development

Christmas Card Address Lists using Google Apps Script and Google Docs

My wife and I send out Christmas cards every year. And every year it is a pain to consolidate and update our mailing address list, not to mention then have it in the correct CSV format for whatever photo card service we are choosing that year (we now make sure the service we use will print all the addresses on the envelopes for us).

By last year I had standardized on using Google Contacts to house all of our addresses. It works well since I can edit them from my Gmail account on any device, or via the Mac Address Book, or on my iPhone (since all three of those are synced, which is awesome in itself). Plus we often find we want/need our friends and family's addresses in our phones in case we are visiting them!

Solution Last Year

  • Google Contacts
    • Using the home address
    • Using a specific contact group
    • Using optional, custom label for the name / greeting on the card (i.e. "The Smith Family" even when the name on that specific contact is "John Smith")
  • Workflow prior to shipping out cards:
    • Export the entire group as a CSV (note: year later and Google Contacts "Preview" is still just a preview/beta and doesn't even have export! So you have to go back into old interface for that)
    • Share file with my wife, we both go over the list and find errors, outdated, missing entries.. so repeat above steps
    • Open in excel and massage the data as needed, and get into the right columns with correct header names for the given service (aka Minted.com or TinyPrints)

Current Solution

  • Kept Google Contacts with a group and custom label
  • Using a Google spreadsheet with a custom script to populate the sheet using the ContactsApp API.
    • This allows the data massaging and column header work to be encapsulated and easily repeatable
    • Able to parse address and create secondary sheets for invalid or missing addresses
    • Can quickly generate and share a file with wife, then once making corrects in the "master database" (e.g. the contact room), regenerate
    • Should work for years to come

Cliff notes of my Apps Script

MINTED_HEADERS = [  
  "Name",
  "Street Address 1",
  ...
]

// This is all it takes to get a group and all contacts by that group! 
var group  = ContactsApp.getContactGroup('Christmas cards');  
var contacts = ContactsApp.getContactsByGroup(group);

var ss = SpreadsheetApp.getActiveSpreadsheet();  
var sheet1 = ss.getSheets()[0];

sheet1.appendRow(MINTED_HEADERS);

// Walk each contact and if there is an address
// add it. Can of course throw in another sheet
// or log etc if there is no address found

for (var i = 0; i < contacts.length; i++) {  
  var contact = contacts[i];
  var greeting = "";
  var customGreetings = contact.getCustomFields('Envelope Greeting');

  // grab greeting from a custom field if it's there
  //  otherwise use the contact name
  if (customGreetings.length > 0) {
    greeting = customGreetings[0].getValue();
  } else {
    greeting = contact.getFullName();
  }

  if (contact.getAddresses().length > 0) {
    address = contact.getAddresses()[0].getAddress();
    // parse address here

    sheet1.appendRow([greeting, street1, ...]);
  }
}

Google has provided great documentation and a very intuitive API (once I got used to everything being an optional array of items even custom fields of the same name... turns out you can have multiple values for the same field name).
While probably over-engineering for the situation, I finally have a complete solution with much less manual intervention, and I no longer anticipate the address list being the stressful part of the Christmas season anymore =).

comments powered by Disqus