by Gina Trapani
Your spouse has contacts stored in Outlook at the office, and you've got some in Gmail and in your computer's address book at home. How do you coordinate your holiday card list? With Google Spreadsheets, that's how.
Those of us who are still old-fashioned enough to *gasp* send snail mail holiday cards that we might even *double gasp* write in ourselves with an actual pen can still use tech to get organized. Today I've got a quick rundown on how to use Google Spreadsheets to create and collaborate on a holiday card list, with a little extra mail-merging and label-printing thrown in for good measure.
Export your contacts
Wherever your contacts live - whether it be Gmail, Outlook, or Thunderbird - your address book surely supports an export function. Export your address list as a CSV (comma-delimited text file) file from each of your address sources. The raw CSV will require a little massaging to get it ready to go into Google Spreadsheets. Open it up in Excel using the Text Import. Be sure to set the delimiter as a comma, or else it'll be a mess.
Once your address book is open in Excel, get to cleaning things up. Trim down your list to just the people you want to drop a holiday card and remove any columns you don't need for snail mail, like email, notes, cell phone number, etc. Shoot for six fields across:
First name, Last name, Street Address, City, State, ZIP
Leave your CSV file open.
Drop your list into Google Spreadsheets
Point your clicker at this extremely simple and public Holiday Card List spreadsheet. Anyone signed in with a Google Account can view it, but you can only edit your own copy, so choose File -> Copy Spreadsheet from the menu to make your own writable copy.
I added in a few sample entries in this Google Spreadsheet for illustration purposes, so feel free to delete those before you get started.
Now comes the fun part, the part which makes me want to press each individual Google Spreadsheets developer lovingly to my bosom (in a very platonic, virtual, metaphorical way): select the cells in your local Excel spreadsheet, hit Ctrl-C to copy 'em to clipboard, and then paste them into your Google Spreadsheet, and boom, everything goes into individual cells the way you'd expect. It's magical.
Wash, rinse and repeat for all your exported address book CSV files until you've got all the names and addresses you need in your sheet. When you're done, hit the Google Spreadsheets Collaborate tab and invite your spouse to view and edit this sheet. This way she can do the same and add any other folks to the list.
You'll notice I put in a few extra columns after the address information: "Received 2005," "Sent 2006," "Received 2006." See, here you're going to track your holiday card comings and goings, so that next year? You don't have to go through this entire rigamarole again.
Once your holiday card list is finalized by the ball and chain and yourself and stored safely up in the Google cloud until next year, from the File menu choose Export > .xls and save that final spreadsheet to disk.
But Gina, you say. Couldn't I just work from a master Excel spreadsheet on my desktop the whole time? Why get Google involved at all?
Well, Grasshopper, with Google Spreadsheets you get collaboration between you and your other holiday card-sending compatriots, plus the ability to check off who sent you what when. So when a few unexpected's send your wife cards at the office, she can log on, add their name and addresses. Then, back home you can run off a fresh, last-minute "holy crap I forgot about these people" mailing.
Option 1: Print your own labels
Now that you've got your list set up, it's time to address some cards. To print out your own mailing labels, download a Word template, like this here 20 per sheet jobber (which, sadly, requires Internet Explorer to download. I know.)
Open up that bad boy in Word, and from the Tools menu, choose Mail Merge. From here it's just a matter of following along with the Office wizard. When it comes time to import your addresses, choose the .xls file you saved above. Be sure to hit "Map Fields" to tell Word what fields are what when you merge.
Obviously, you'll need to pick up package of mailing labels to print these out on (this template works with Avery 8161 and 5161). Or you can be cheap like me and just print 'em out on paper and tape them to your cards. The bigger your mailing list, the more worth it the labels become.
Option 2: Make the USPS send those cards for you
Alternately, the US Postal Service offers a pretty neat option for the lazy and/or busy: upload your spreadsheet to their site, along with a photo (or choose one of theirs), and they print out custom holiday cards AND mail them for you. This one's perfect for busy parents who want to send out a recent snapshot of the kids with the least amount of fuss.
I haven't tried it myself, but my brother did it last year and he had nothing but good things to say. The cards come out well, the pricing is reasonable, and you suffer no paper cuts or printing crises. The service is called NetPost, and while the web site isn't the most intuitive thing in the world to navigate, it's worth sparing yourself from messing around with all those cards, envelopes, labels and stamps.
This article was inspired by Lifehacker reader Donn, who asked the readers how to deal with his card list. As usual there are a ton of great responses in that discussion, including:
- scornish's G Sheets usage much like we described here.
- rogerkaplan's advanced Excel and Word mail merge trickery that puts my simple spreadsheet to shame.
- andyjoe16 uses Gmail's notes field to sort out who gets a card and who doesn't.
- Ryan updates an Access database each year that he started for his wedding invitations.
- jackson swears by Plaxo and its Thunderbird plugin.
Thanks all for your fabulous ideas. How are you coordinating and collaborating online this holiday season? Let us know in the comments.
Gina Trapani, the editor of Lifehacker, is slowly getting won over by the browser-based office suite. Her semi-weekly feature, Geek to Live, appears every Wednesday and Friday on Lifehacker. Subscribe to the Geek to Live feed to get new installments in your newsreader.