One of the main responsibilities my team has is offering and running training on a number of resources teachers in the district have access to. The most popular, because of their new-ness, are Google Apps (GSuite…whatever) and Canvas. Because we offer so many workshops, our Drive is full of copies of copies of copies of registration forms. So, I built a custom registration form using Google Apps Script and a Sheet. This detail-heavy post walks through the entire process with code.
There were a lot of iterations of the project and in the end, we wound up with a website which:
– Can be managed by anyone on the team
– Collects the username (their email address, actually)
– Displays available seats for the workshop
– Allows users to both register and cancel registrations
– Displays the user’s current registrations
This uses a Google Apps Script web app and a Spreadsheet to run the back end service. The sheet serves as two databases: course offerings (with details) and user registrations. The front end site uses AJAX calls to populate a page specific to the user.
In order to return the correct data,
Session.getEffectiveUser().getEmail() is used to check the logged-in user against the database so only their course information is returned. For example, this script gets the current user registrations from the registration sheet:
Much of the backend work is building arrays and objects of the correct data and sending it to the client side scripts to display. The full commented code is in this GitHub Gist.
Google Apps Script allows for templated HTML. I’m not using a template, per se, because it’s loading a static page and populating
div elements with AJAX calls. But, the same could be done to create multiple pages using the same template.
There are two main actions: registering for a course and cancelling a course. Both call the server to make changes to the spreadsheet and pass updated information to the front. Registering for a course (or courses) write across a range rather than submitting n number of results for that user. Cancelling a course, instead of building and writing a new array, searches through the Sheet for the user and the matching date and simply deletes the cell. The app is then reloaded to refresh the user registrations at the top.
It’s not pretty and there are some changes I still want to make to clean up some of the array building/passing, but it’s such a small amount of data, the site loads very quickly even with a large number of registrations.
If you have suggestions, or if you make improvements, please leave a note in the comments. As is, you could probably copy and paste most of this into your own sheet and get it going. Be sure to fill in the correct sheet names and ID’s in the
code.gs file to avoid breakage.
—Written on January 23rd , 2017 by Brian Bennett Categorized in: All How To Technology