Fun with google form scripting

Sometimes doing the not most important thing on your todo list is so fun. That was the case today.

I have started to work with a lot of really amazing people to support SpeakEasy as a place for new voices to be heard at testing conferences. Turns out it is a lot of work! It is so impressive to me that Anne Marie and Fiona dreamed this up, actually got it running, and have made such a sustained impact on the community over the last few years. I personally am a graduate of the program, have continued on as a mentor, and now am a volunteer. As a group of new volunteers, we were given the open invitation from Anne Marie and Fiona to make SpeakEasy our own. They know that the need to keep it running took its toll on some of the processess and that with some extra volunteer power we may be able to address some of the underlying processes.

One process I was particularly interested by was how we were alerted about new mentees and then how we would then take them through the process from new sign up to being mentored.

Oh the glory of google sheets! We added three columns to the google sheet that gets created from the google form on the website. One was used to sign up for the job of getting that person matched, one was for tracking your progress on that journey, and one was for confirming final status of that new sign up. As you can imagine this gets messy and also limits the value we can get from data like how long it takes for someone to get matched.

Excel table row with data about a mentee and matching them with a mentor.
An example row from the “to be mentored” sheet.


In comes the power of a small script. I have known about the ability to script Google apps for a while now but never played with it myself. This felt like the perfect opportunity. What I really wanted was to be able to have a new mentee show up in a management tool for tracking and visibility, in our case Trello. But let’s start with how to get a Google form to take action on submit.

I found this website that gave a great framework for my efforts. A couple of interesting notes to get it working though. First of all, do make sure you update all of the fields they suggest to make sure they match your form. But even if you do that, if you choose to “Run > onFormSubmit” from the tool bar you will get an error like this:

TypeError: Cannot read property "response" from undefined. (line 24, file "Code")
This is the error when trying to run onFormSubmit from inside the Google scripting tool.


Basically it is telling you that because no form has been submitted you can not run this command. Shucks. Really wanted to test this before going live. Good thing I attached to it to a play form, so I just went and did that! Submitted the form and waited for my glorious Trello card to show up.

Wahwahhhhh turns out that there are some typos in the script. It took me a while to sort them, but using the “notification” option on the triggers to say email me immediately helped a lot to debug. In particular you will need to look at SendEmail() and update the “title” and “message” variables to “trelloTitle” and “trelloDescription” for the form to work.

Viola! Now on submit I have a new line in my excel response sheet as well as a new card in trello.

A trello card with the details from the entry form on the SpeakEasy website
A new trello card for a mentee to match

But honestly, the more exciting thing is the power of collaboration that is unlocked now. Now we have timestamps for activities that we perform, we can at a glance see how much any one person has in progress, and we can track where bottlenecks may occur during the process.

A filled in Trello card with dates and times of activties from new application through to introduced to mentor.
Possibly example process of getting Jane Doe matched in Trello.


Obviously we as a team need to sort out our own process with this tool, but the oportunities for collaboration are so much greater. Looking forward to seeing how it progresses!


2 thoughts on “Fun with google form scripting

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s