Motivation
We wanted to create a game quiz, with questions and answers, and know the time taken to answer all questions. Making it with Google Forms was the simplest option. The problem is that it has the form submission time but not the time when the form was open. So we couldn’t use it to know how much time it took to answer all questions.
We searched and found some alternatives. Timify is one of them. It tracks the time since the form is open until it is submitted. It also prevents multiple forms from the same participant.It did all what we needed but it’s free version only allows less than 100 form submissions, which isn’t an option for us.We have around 500 participants and it is a free event, we don’t charge money, therefore we didn’t want to have expenses.
Another option was to use Microsoft Forms. As opposed to Google Forms, Microsoft Forms registers the time the form was open. That would be an option, but when we tried them they looked slow and not reliable.
Without alternatives we decided to implement our own solution. We didn’t want to create another form platform, so we decided to use the Google Forms and create an application only to track the start time. Somehow, we tried to build ourselves what Timify was selling.
Architecture
We started by creating a Proof of Concept, to validate if it was possible to use Google Forms in the way we needed. After having it working, we evolved it to have some more features, like allowing us to configure multiple events with start and end times.
Since the responses/answers for the Google Forms are saved in a Google Sheets, we decided to also use Google Sheets to store the start time and also the configuration of the event. To access the Google Sheets we used an OAuth token which is cached in the Firestore.
The events are configured in a Google Sheets and have the following properties:
- Enabled A toggle to disable (close) the event.
- ID Unique identification of the event (may be a name).
- Title Title of the event.
- Subtitle Subtitle of the event.
- id entry ID of the Google Forms’ entry to inject the participant start ID.
- form id ID of the Google Forms with the questions.
- open date Date-time after which the event is open for participation.
- close date Date-time at which the event closes and doesn’t accept more participants.
- close message Message to show while the event is not open.
- time zone Time zone of the event’s organizer. It is helpful to convert the start times to the organizer time zone, so that he knows the local time when each participant started.
We used Google Cloud Functions to do the Server Side Rendering of the web page.
The first thing a participant does is to open the event URL in the browser. It’s browser sends a GET request to /events/{id}, which invokes a Function (see figure below). The Function retrieves the OAuth token from Firestore and reads the event with the given ID from the Google Sheets. Before returning an HTML web page, the function checks the state of the event to see if it is enabled and open for participation. The rendered web page has the information of the event. If the event is open, the web page has a form for the participant to fill his information (e.g. name, e-mail, team, and country) and a button for the participant to start the event (see screenshot below). If the event is closed, only the title and a message are shown (see screenshot below).
After the participant clicks on the start button, the time starts counting and he is redirected to the Google Forms with the questions. The start button sends a POST request to /events/{id}/start which executes a Google Cloud Function (see figure below).
This Function reads the event again to validate if it is still open. If it is open, the current time is appended to another Google Sheets, with all the information of the participant, and an unique ID which is used to identify the participant (a UUID). The Google Forms is presented to the participant inside an iframe. To avoid asking again for the participant to identify himself in the Google Forms, we instead inject the UUID in the Google Forms.
After starting the event and being presented with the Google Forms, all the participant interaction is handled by the Google Forms. When the participant answers all questions and wants to finish, he clicks the submit button of the Google Forms, which saves the responses to a Google Sheets (see figure below).
Results Calculation
At the end of the event, after all participants have finished, we can calculate the results to know who won the event. The start time (the time when the participant clicked the start button) and the participant information is stored in one Google Sheets. The end time (the time the Google Forms was submitted) and the responses are stored in another Google Sheets. These two Sheets are merged together by using the UUID generated for each participant. The total time spent is calculated by subtracting the start time from the end time. The number of correct answers is automatically calculated by Google Forms. With the number of correct answers and the time spent we can calculate the score for each participant and publish a results list.
Usage and Quotas
As per the above architecture, we were expecting two function invocations per participant: one to open the event and another to start it. That would mean two function invocations and two socket connections per participant.
On average, with had 500 participations per day, around 2500 function invocations and 3000 socket connections. This is alarming because it is 5 times the number of participants, above our expected usage. And the quota limit is 5000 function invocations and 5000 socket connections per day. This high number per participant may be explained because some of them reloaded the page or tried to access it before the event was open.
The only quota exceeded was the number of socket connections open per 100 seconds.
That quota is limited to a maximum of 50 socket connections per 100 seconds.
Only in one day it was exceeded.
We also used too many calls to the Google Sheets API, but we didn’t exceed the quota of 100 requests per 100 seconds per user.
Future Work
This started as a simple proof of concept and it worked in the two events we organized,
however there is still room for improvement.
Starting with the landing page of the event. It takes some time to load because of the server side rendering, which accesses the Google Sheets API to read the event information. Most of the time this page is static. The only dynamic part is to check if the event is open or closed. Therefore we could have created two static pages (one for the event closed state and the other for the open state), and we only need to host one of them. And when the event changed state, we replaced it with the other.
The static web page can use a framework like GatsbyJS to generate the static web site from the event information in the Google Sheets.
Since the time period the event is open is known in advance, and it is published in the bulletin for all participants to know, we can use a cache to keep the static page until the event state changes. These two improvements, using static pages and cache, would allow the event page to load faster.
We heavily used the Google Sheets API because it allowed us to easily edit the event information and to see the participants starting at real time. However the calls to the API cause some delay in the execution and use quota. In our cases the quota was not a problem, but if we want it to scale we need to avoid unnecessary calls to any external API. A possible solution is to load all events from the Google Sheets into Firestore.
And to record the participants start times to the Firestore, and only export them to a Google Sheets at the end of the event. This way we would only use the Firestore during the event, which is faster and has higher quota limits.
In the event page, before the participant starts, he is asked for some personal information. We didn’t validate this information nor wanted any of it to be mandatory, in order to allow anyone to participate even if anonymous. But since some participants tried to cheat the game by playing multiple times, we should have done some kind of validation. We should have verified the e-mail was valid. Nevertheless, this would not prevent some of them from creating dummy e-mails or using fake identifications to go around the system. But at least it would make it less likely for this kind of thing to happen.
During the event we also saw some errors appearing in the invocations chart of the Google Cloud Functions, but in the logs there were no errors. We could not identify the cause of these errors, neither any participant reported problems. This is something we don’t understand yet, how the chart shows errors but there are none in the logs.
Artifex Anima 