In prepping the edcampIS.org website for registrants, I took a few minutes to figure out how to automate parts of the process in an effort to minimize ongoing support. Fun experiments include:

1. Embedding a Google form for future attendees to register (http://www.edcampis.org/registration),
2. Embedding a Map that automatically updates when someone registers (http://www.edcampis.org/attendees), and
3. Adding new registrants’ Twitter IDs to a public Twitter list (EdCampIS2012 Twitter list).

Embedding a Google form into a website

This process is fairly straight-forward. You’ll need a Google account, a Google form to use, and a website where you’ll be embedding the form. When looking at the “Edit form” window in Google Docs, click on “More actions>Embed” and copy the code in the pop-up window.

Paste the code into your website’s html editor and save. If you are using a Google Site, the process is even simpler – just edit the Site page and select “Insert>Spreadsheet Form” and then locate your form in the pop-up.

Embedding a Map that automatically updates

This is fairly quick to get going but there is an endless amount of customization that can be applied through HTML.

The Google Map gadget is an option from within Google Spreadsheets, just select “Insert>Gadget>Maps>Map.” This gadget isn’t very sophisticated – it just wants to know where it should throw a map marker and optionally if you’d like something to be displayed when the marker is clicked on.

When you are dealing with a spreadsheet that is connected to a live form, it is preferable to stay away from the main data table, as any mistakes within it can cause the form to break. So to prep for a map, it is worth creating a second tab (or worksheet) within the spreadsheet.

This new worksheet will just include the data that the map gadget wants. Column A can be the registrant location. You can get specific right down to a street address if you want, but for edcampIS we are just using City, State, Zip. I combined this location-based data into a single cell using the CONCATENATE function that will pull pertinent info from the first registrant’s form entry (row 2 in the worksheet called ‘data from main spreadsheet’).

=CONCATENATE('data from main spreadsheet!'!F2, ", ",'data from main spreadsheet!'!G2, " ",'data from main spreadsheet!'!I2)

I copied and pasted that cell all the way down the spreadsheet. Easy.

Then I went and inserted the map gadget and assigned the location data. The Map was not happy. It seemed to be upset that there were a bunch of cells in the column that were blank except for the comma that I used in the CONCATENATE function. To fix that, I wrapped the entire formula in an if/then function.

=if(len('data from main spreadsheet!'!A2)>0, CONCATENATE('data from main spreadsheet!'!F2, ", ",'data from main spreadsheet!'!G2, " ",'data from main spreadsheet!'!I2), "")

This says “If the length of the data in the main spreadsheet’s A2 cell is more than 0 characters long, then do the CONCATENATE thing, otherwise, just leave the cell blank.”

Copy and paste down the column. Done. Pretty Easy. The map is happy and displays the markers.

I was happy with that for a few hours but then decided it would be cooler for the map to tell us more about each attendee than just their approximate location. The map gadget will use Column A to figure out where the marker should go, but can also use column B as a “tooltip” (the information that displays when a marker is clicked upon).

This means that anything we want to pop-up needs to be in that single cell next to the location, which is a great opportunity for the CONCATENATE function again. I ended up pulling the following data from the main registration spreadsheet:

• first name
• last name
• Twitter ID
• profile photo
• city/state (again)
• school or organization name

I used html and css to format the display of the pop-up, and included some live links to Twitter and a default profile image if none was provided by the registrant. I had enough data that I found it easier to build parts of the formula in individual cells (on another worksheet) and then CONCATENATE everything together at the end. As with the basic location stuff, I had to wrap everything in an if/then so that there wouldn’t be a bunch of empty debris that would upset the map.

 

Adding new registrants’ Twitter IDs to a public Twitter list

This is truly an example of standing on the shoulders of giants. I did nothing beyond perform a search engine query and then copy and paste the work of Vic Fryzel and Martin Hawksey. My solution is not elegant in any way but it currently works.

I followed Martin Hawksey’s tutorial step-by-step, which takes advantage of a bunch of complicated authentication work that Vic Fryzel completed.

Once done, I went back into the Script Editor and found line 507 of the Twitter Approval Manager script. Martin designed the script to prompt the user for the name of the list to be populated. I deleted this input request and hard-coded the list I am using:

var list_id="edcampIS2012";

After saving that, I wanted to automate the script so that I don’t need to click on a button every time I want the list updated. To accomplish this, I set up a time-based trigger:

• In the script editor, click on “Triggers>Current Script’s Triggers”
• click on “add a new trigger”
• In the first drop down, select “addToList,” which is the script you want to run
• In the second drop down, choose “time-driven” as you want this to happen even when the spreadsheet isn’t open
• In the third drop down, select the time interval between script runs – I chose “hourly>4 hours” because I’m impatient
• Finally, click on “notifications” so that the script will alert you via email if it fails

I did get some notifications of failure every time the script ran but there were no new Twitter IDs to add to the list. To avoid this in the future, I went to line 520 in the script editor and commented out the “new member” counter.

 

With this work done, I’m hoping that the spreadsheet will manage the website content in the coming months. You may have improvement & enhancement suggestions, please comment or send an email with all ideas or questions.