Editor's Note: Guest author Phil Ridout is an independent consultant specialising in Knowledge Management. He is a facilitator for the Knowledge and Innovation Network (KIN) a business to business knowledge sharing network.

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.

Posted by Phil Ridout, independent consultant

Want to weigh in on this topic? Discuss on Buzz

Editor’s Note: Guest author Dave Abouav is a Google employee who is a part-time instructor for a night class in physics. He created Flubaroo as a 20% time project at Google based on his own teaching experiences.

As an instructor for a night class in physics at De Anza Community College, I didn't have enough time to grade assignments. Because it was a survey course, multiple choice assignments seemed like a viable option. So I used Google Forms to collect homework assignments. But I needed a way to grade the collected submissions and perform some useful analysis. The idea for Flubaroo was born and is now ready to share with other teachers thanks to Google Apps Script!

Flubaroo is designed as a simple grading solution for teachers using existing tools they're already comfortable with. It allows automated grading of quizzes and assignments administered via Google Forms. Beyond just grading, Flubaroo also provides analytics such as a histogram of grade distribution and identification of questions that many students missed. Flubaroo’s most popular feature is its ability to email each student their grade, along with the answer key if desired.

Here are the key reasons I chose Google Apps Script to implement Flubaroo:

1. I wanted to build a simple application which can be easily integrated with Spreadsheets without the need to compile and deploy code.
2. I liked its ability to integrate right into the spreadsheet as a menu, easily giving teachers access to all of Flubaroo's features.
3. Apps Script provided UI Services which allowed me to build UI applications very easily from within a browser. I did not have to install complex tools to create a UI.
4. Apps Script Services makes it very easy to integrate other Google APIs such as Google Charts API for the grade histogram.
5. A JavaScript syntax is easy to program and without the need to learn more complex languages like Java and Python.
6. Apps Script gave me the ability to easily share my grading technique with other teachers through the Apps Script Gallery. It is very easy for anyone to install the Flubaroo script in their Apps Script editor.

It was easy to get started with Apps Script, and the examples already provided in the Apps Script Documentation further helped me get it working quickly. Flubaroo is about 2300 lines of code. I used Spreadsheet Services to process and grade assignment submissions. I extensively used Ui Services to create the grading flow. In addition, Flubaroo makes use of the Google Chart API to create the grade distribution chart. Integration with Apps Script with Google Chart API was very easy. Below is an example of the types of charts I was able to generate using Apps Script’s integration with Google Chart API.



One of the great features of Apps Script is the availability of Triggers and Events. Events helped me provide a menu to the users. I created an onOpen function which installed a menu on the spreadsheet every time the spreadsheet is opened by a user. This allows users a menu based access to the various functions in the script.



If you’d like to give it a try then please install the script from the Apps Script gallery. You can also visit Flubaroo website to go through a full tutorial of the script. Thanks to Apps Script, grading assignments is no longer a chore for me.

Posted by Dave Abouav, Googler

Want to weigh in on this topic? Discuss on Buzz