Event Management Automatisation using Apps Script

Last year the network I help to run, KIN, decided to start using Google Sites for their shared online space called MemberSpace. This ‘MemberSpace’ is used as a repository for shared documents and information about various KIN events. I soon realised that using Google Apps Script would significantly enhance the event management process.

The network facilitators organise about 30-40 events a year. For each event we need to :

  • Create an Events page in the MemberSpace Google Site (in a standardised format)
  • Create a Calender Event in the Network Events Calendar maintained by facilitators
  • Announce the event on the MemberSpace news (announcements) page
  • Allow delegate self-registration
  • Send attendees a customized email confirmation when they register
  • Send joining instructions emails to all attendees immediately prior to the event

By using Google Apps Script, we have been able to automate all of the above tasks and make event management a lot simpler.

The Solution

To accomplish this automation, we have created an event management spreadsheet template with an accompanying form and script. Whenever an event is organised, the event facilitator copies the event management template and fills in standard data about the event in one of the sheets.

Then all the facilitator has to do is select the appropriate menu items to accomplish the tasks listed above. (This spreadsheet is only seen by the Facilitators. The event attendees only see the event registration form.)

So what’s in the script behind this?

Before I started this project, I had never used Javascript. I used various tutorials on Apps Script documentation site to learn about Apps Script and discovered that a good starting point was the Simple Mail Merge Google Apps Script tutorial. The workflow of managing an event was relatively simple but required integration with various services such as Mail, Calendar, Contacts, Forms and Sites. Below is a description of how we used Apps Script to automate these tasks.

1. Event Registration Email

Whenever an attendee registers for an event, we need to send a customised confirmation email. This script is based on the Simple Mail Merge Google Apps Script tutorial code and we use an onFormSubmit trigger to automatically run the script to email registration confirmation to delegates when they submit a registration form. Mail Services in Apps Script are used to send these emails.

2. Event Page in Sites

The ‘Create Event Page’ script is invoked by the facilitator to create an event page (using Apps Script Sites Services) in the MemberSpace using one of three page templates depending on the event type. Should any event details change, this script can be run again to delete the original page and create a new one with the amended details.

3. Calendar entry in Events Calendar

Once the event page has been created, the organiser creates an entry in the calendar by running the ‘Create Calendar Entry’ script which generates a calendar entry containing a link to the previously created event page in the MemberSpace. This script uses Apps Script Calendar Services.

4. Email confirmation / joining instructions

A week or so before the event takes place, joining instructions are sent to delegates using a menu option from the original spreadsheet. This runs a slightly modified version of the email script used in step 1.

5. Event Announcement in Sites

Finally, the organiser can ‘announce’ the event by running the ‘Announce Event’ script which places an announcement in the MemberSpace News page. (Actually, there are three ‘Special Interest Group’ News pages and one ‘General’ News page which are merged – by a time trigger driven script – into a single announcements stream that can subscribed to using a Site Services script the basis of which can be found here.)

Performing the above tasks manually may seem simple or trivial but it can be time consuming and error prone. By using Apps Script, we have implemented event management functionality that allows us to manage events in a standardised way.

Where good ideas come from

I would like to share with you some insights about innovation and motivation. However, rather than writing up an essay on this topic I opted for a more leisurely approach: using video presentations from YouTube. A short introduction first.

You might have come across stories where people “just had an enlightening moment” and invented something extraordinary and became “overnight success”. But the reality is that great inventions are never borne overnight. Steve Johnson, the author of “Where good ideas come from”, explains it in more detail, and in a very entertaining way, in the first video presentation. If you still struggle to come up with that “one big idea” that will move things forward for you, don’t despair! There is hope for all of us “slow thinkers” after all!

The second video, equally entertaining, is about motivation – or rather the secrets of creating an environment that allow the innovation to flourish, increasing the chances of coming up with that elusive “big idea”. Fifteen minutes required to view both video presentations can be a great investment in your future. Enjoy!

If you like it, please share using this short URL version: http://ow.ly/3ZKVz

What Steve Coast’s Move to Bing Really Means

OK, sure — there is a rush to blog the news. Steve Coast joined Bing. But leaving it at just that news misses the whole point of what Bing/Microsoft wants and why Steve would go join the Bing Maps team.

One benevolent dictator for life welcomes another to his domain.

Microsoft and OpenStreetMap

Remember this news a couple months ago?  It was cute right?  Nothing earth shattering, just another layer to do your Bing Maps mashups over (assuming you are one of the 5 people who have ever done that).  But deeper something else was going on.  See Google broke the detente that the vendors had with the data providers. (can you believe that was barely a year ago?)  In doing so it put a ton of pressure on the other mapping API vendors to change how they did business.  Despite the problems with Google Maps, the one thing it had going for it was that it could be updated.  Microsoft was stuck with the old model.  Navteq is authoritative1 and that means it takes months to update the data.  Google’s navigation might have been totally borked, but at least they controlled it.  Microsoft and MapQuest didn’t have the luxury.

Time to Bail!

So MapQuest went first and in a big way.  Not only did they announce they were breaking with their data vendor, but they were also going to donate money and time back to OSM to help further the project along.  Already MapQuest’s Open website is running and those at least in the UK are happy as clams.  But despite all those German OSM mappers doing great work in the middle of Europe, for OSM to be viable it needs to have a workable/routable map in the good old USA.  This was the whole concept behind those Cloudmade ambassadors.  The idea was they could have succeeded if they had the map right.  Well the reality of the scope of the project killed Cloudmade’s efforts and the USA has lagged behind.  What the OSM project needed was some company that could do the heavy lifting and move the project forward in the USA and other parts of the world where it needed help.

Enter the 500 lb Gorilla

So here we are.  Microsoft needs to get involved with OpenStreetMap to continue to be relevant in the web mapping space and OSM needs Microsoft, their aerial images, their big pocketbook and their need to dominate all spaces they exist to join up.  Simple as pie, right?  Oh, but wait — the minute Microsoft enters the community, they’ll be a huge backlash.  I mean the goal of Microsoft is to put a proprietary OS on everyone’s desktop (at least that is the feeling I got from an OSMer at WhereCamp5280).  Ah, but remember this wonderful news?  Who better to represent Microsoft with OSM than one of the founder’s of OSM!

The Way Forward

So what does this mean, really?  Short term, not too much.  The OSM data in the USA is still not good enough to route with2.  But the better aerials will improve the USA greatly and quickly.  If there is one thing that Microsoft has done well, it is the aerial imagery in the greatest democracy south of Canada, north of Mexico; OSM-US .  Plus rather than investing in their technology to route with Navteq, they’ll push those efforts toward OSM routing and BOOM; we’ll have a working, routable OSM map that everyone can use.

Makes you wonder about Google’s choice to roll their own now doesn’t it?  How quickly Google’s map looks like it will be a drag on their innovation.  Between the OSM mappers, MapQuest, Microsoft and all the others who are part of the open project; I see no way OSM doesn’t dominate the market as the choice for mapping data moving forward.  And you know who wins, everyone who wants free and open data.  That’s the take away — congratulations to Steve Coast because he sure deserves success, but the real winners here are you and I.

1. By authoritative I mean busted and old business model.

2. Flame away guys, but you’d be wrong to think otherwise!