How I automated the school minibus booking system...

For a long time there had been gripes in our school about the haphazard way buses were booked, so I decided Google could come to the rescue! Here's how.

I set up a Google form like the one below, with a link to it via our school intranet:

I then opened up the Google Sheet behind this form like so:

Once in the relevant Google Sheet I navigated to Tools>Script editor...
Once inside the script editor I typed out the following (you could easily adapt this to suit your own purposes, once you've got the gist of it).
The first part of the script involves setting up the variables:
If this looks like gobbledygook don't worry - it did to me at first. What you are doing here is assigning the values in the rows of your spreadsheet to variable names that you will use later in your program. Remember that the first row in your sheet zero, the next one and so on... Line 2 of the code in human language reads something like: 'Set up a keyword called Username and assign it to the newest value in the second column'. Make sense now?
Next you need to compose the body of your e-mail. The code I used to do this looked as below:
Anything in blue here is a reference to a keyword (or variable in the lingo) assigned in the first step. The '\n\n' elements indicate new lines as do the HTML tags - I have no idea why you need them both, but it seems you do.
Lastly these few lines of code sent the email:
I wanted this code to run every time a new record was inserted into the Google Sheet via the form, so I needed to set up the project's trigger. I did this from within the script editor window by choosing Resources >Current project's triggers. This is the window that popped up. Note the settings that I chose:
This tells the computer to run the script every time a new form is submitted. In other words, every time someone requests a bus, an email will be sent to the bus manager containing all the information that he needs for the booking.
Lastly a dialogue box appears asking you to authorize the script, which you must do of course and hey presto, you're done.
The e-mail that results (with nonsense information in in this case) looks like this:
Happy booking!

comments powered by Disqus