</p>
Earlier this month my NGO (GAIN) had its annual meeting. Around 200 participants, which includes several presidents, CEOs, ambassadors and representatives of the World Bank, OAS, and more than 100 companies. The invitation list to the reception had more than 1.000 names.</p>
I built the Invitation system almost from scratch using Google Apps Script (GAS), and I want to share the experience. The decision to engage on this was partly becasue I had the chance to participate on a free and open event about GAS for developers at the Google Washington DC offices (thank you!).
My goal was to follow these premises:
- Make it as simple as possible, for both sides.
- Do not ask any information you know. I hate filling up my name, over and over again, when they should already know it! And learn from the corrections people do on their info.
- Send short, simple and personalized, messages.
- Automate, as much as possible.
I do have coding experience, but not in GAS or javascript. I estimate it was, overall, around 3 full days of coding. I did not start from a blank sheet, I used this script as my starting point. This is hardly a re-usable script as-is, but I hope it can help others build upon it. The whole GAS code is here, and a template of the Spreadsheet is here.
The Master List
A Google Docs Spreadsheet with the list of people:
- Personal Info: Name, Email, Company, Group (invited to all sessions or only reception). [Tip: GAS MailApp allows comma separated emails, so you can send it to the person AND their assistant.]
- Token: This is an identification number used to match each person with their reply. I used this Spreadsheet formula to generate it:
- A List of the Sessions to place the Confirmed/Declined, and their comments (e.g. vegetarian)
- I had to add later on few other columns to put things like Badge Name (via a formula), Comments...
Managing the invitations
The main loop is:
- For every person, send them an email if the have "Go" Status, then set to "Emailed". Choose the right template for each group.
- If "Emailed", check for replies and set to "Received".
- If "Received" check for new replies. (I ended up removing this to a separate function)
To send the invitations, I chose the template depending on the Group, which is then basically a call to MailApp with a HTML email body hard-coded.
The interesting bit is the construct_URL function. We made a reply questionnaire using Google Forms, where we write the rough agenda and we ask if you can join us.
We actually made two Google Forms, one for each group with its questions to the appropriate sessions. Instead of just adding the link to the email, I used the URL to pass along personalized pre-populated information to the form. That saves time to the person (we already know his name, and also allows us to place the Token on the last field to identify the answer). [btw Non ASCII characters like accents, can break on IE]
This is the simple code to make a personalized URL like this one.
Now the answers are registered in another Google Spreadsheet. To check for those answers, you just need to process that Sheet looking for Tokens to match the reply with the person (they might have changed their name or other info):
The actual Processing of the information is then just placing the answers from the response form into the appropriate row in the Master List. You just need to code a single reply and iterate to each. Also the special cases Yes/No to all. Making the loop for all matching Token makes sure that the last Reply is the one remaining, in case they submitted more than once.
The nice thing is that now we can carry back all modifications to their info back to the Master List. If we misspelled their name, or they changed Position, they will likely correct that in the form, and this little code will flag that in green for you. So next time the code uses that information for other emails, badges, or other events, their information will be updated, by them (I found this to be the case in 5% of the cells).
A few days before the event you can send a reminder to those attending to at least one session, with a confirmation of their reply, so they can update it if there has been changes. You can also add some attachments with more info:
[We used a very similar IF condition to print the badges for everyone coming to at least one session]
Media Consent Forms
We also prepared a document to allow us to take pictures of the participants during the event. Again, I would hate to make them write their name, If I already know it. All we did was upload to Consent document to a Google Docs, and personalize it:
Unfortunately, there is not Print option inside GAS, so you need to search on your Google Docs for the common part of the Title of all those consent forms. Then batch download them as a zip file ("Select all" then "Download"). Once in your computer, just drop them to the printer icon.
Managing the Information
Once you have all the Information in the Master List, is quite easy to manage many parts of the event
Badges: Very similar code to the Consent Forms to print the Names and Companies.
Securing the Info: The Google Docs should not be shared with anyone, only the people inside your team, and only editable to a few. I was really afraid of changes in the Master List, so used to option to set different access right to individual sheets. You can also use the GAS onOpen function and SpreadsheetApp.getActiveSpreadsheet().addMenu("Annual Meeting", menuEntries) to add an extra menu, only to particular users.
Dealing with "Manuals": There will always be cases when they reply via a phone, or you need to update the reply. Since this system relies upon keywords in the "Status" column you only need to set it to something else than the chosen ones to track manual instances. For example set it to "Manual" and add the "Confirmed" replies on the appropriate Field. You can also put any other Status, and you will be able to track it on the Status Sheet ( i.e. How many "Missing Email" we have?)
Tracking Participation
Having all the information in one placed and updated in almost real time, allows to easily track the progress, with only a few Google Spreadsheet Formulas:
Count number of Confirmation for a particular Session: `=COUNTIF('Master list'!K:K,"Confirmed")` Count the total number of Replies: `=COUNTIF('Master list'!J:J,"Replied")`
You can even prepare a Participation list for each Session, with Names, Titles, ... Very useful for example to order lunch and spot vegetarians. This is the formula to pull out a sorted list:
Tracking Feedback
After the event, we can update the actual participation, and send a personalized Thank You note, and include a quick Feedback Questionnaire.
Summary
It was actually fun to code this system. It is the first time I use GAS, or even code in Javascript, and it turned out to be very fun. The debugging options are very useful, specially since I was developing on-demand, so I made sure to place plenty of stop flags before sending to all our invitees a broken email.
The actual managers in charge of organizing this event where very happy and loved the system. I tried to anticipate all their needs and plan a structured clean script. In the end there was some organic growth to code things or cases I had not planned, but it turned out to be incremental changes, never too deep. Unfortunately that turned the system highly customized and not re-usable without many modifications.
I got some actual comments from the end-users:
from a participant:
I just wanted to take a moment after the successful annual conference you just organized to briefly mention that the registration system you used excelled any other mechanism I've seen in hundreds of other conferences throughout my professional experience. In your case, we got emails with the right information, including attachments, in a timely manner. I specially appreciated you pre-populated every communication and form with the information I initially gave you. This makes a difference, unlike other systems, where you annoyingly have to enter the same information once and again.
from our staff:
Best registration system I've seen. And I've seen many.
With just a few weeks to go before our nonprofit's annual event, we were so lucky to have this system to organize participant information, mail invitations, receive responses and calculate the data in a very organized fashion. The most efficient piece was the opportunity to personalize each email and feedback form on GoogleDocs.Our process was more efficient because of the system Bruno, implemented to help streamline and reduce our workload.
This system is far from needing an actual developer to nanny its execution, and also far from being re-usable (separating the system and the particularities of our event). Yet, I am proud of it, and how it turned out.
What I would do differently in the code if I were to do it again:
- Email body should not be hard-coded, it should be placed on a separate sheet to be taken from there. Also add a simple text version (reduces spam score).
- I would implement a "putRowsData" similar to "getRowsData".
- Customize the Forms so they live inside our NGO site, not using the Google Templates.
After this expericence I missed:
- The option in GAS MailApp to authenticate as another user to send on his/her behalf.
- The option in GAS MailApp to save the output to the Drafts folder, instead of sending the email directly.
- GAS Mailapp will not accept a Google Docs as the body of the email. That would be great!
- A way to sync the code to a revision system. e.g. link it to a git repository or, even better, allow "includes" to pull the code at execution time.
- A "sandbox mode" where all output goes to you. E.g. email to be send to user@example.com would actually go to brunosan+user-example-com@gmail.com
- URL fetch would sometimes silently fail. Better fetch once and re-use if possible.