James is finishing up a tweak to the Sunlight Labs API that allows for fairly sophisticated search for members of Congress, it isn’t “published” yet but it is active so if you want to experiment you’re welcome to try it out, but for now it is “unofficial”.

Here’s the deal: We wanted a better way for people to search for members, as members of congress are often times referred to by different names– think “Ted Kennedy,” “Edward Kennedy,” “Teddy Kennedy” etc. Whether it is nicknames or typos, it makes analyzing data difficult if names are not standardized.

We’re not saying we’ve come up with a complete solution to name standardization or even congressional name standardization, but we’ve got a simple solution that might make some lives easier. To demonstrate, we’ll use Google Spreadsheets. Follow along at home!

Step 1: Get an API key from Sunlight Labs here.

Step 2: Create a Google Spreadsheet

Step 3: Name the columns of your spreadsheet “Member”, “Firstname”, “Lastname” so it looks like this:

Step 4: Let’s add some wacky mispellings and some semi-dirty data like below:

Step 5: Here’s where it gets fun. We’ll use the importXML function in Google Spreadsheets to take the values of our dirty data and send them to the Sunlight Labs API, and get a firstname. Enter this code into your spreadsheet:

=importXML("http://services.sunlightlabs.com/api/legislators.search.xml?apikey=YOURAPIKEYHERE&name="&A2,"//firstname")

See below for an example:

Step 6: Do the same for the last name column, but change your call to parse the lastname, like so:

=importXML("http://services.sunlightlabs.com/api/legislators.search.xml?apikey=YOURAPIKEYHERE&name="&A2,"//lastname")

Step 7: Finish it up! Select the first values of those newly processed columns and fill in the rest of the values like so:

Neat! Clean easy name cleanup in your spreadsheet!