Auto Generating Google Forms
Introduction
At Maveris Labs, you’ve typically seen us write about the technical aspects of our cyber security and IT related research. Today, we are going to share something a little more casual. I’d like to discuss how a few coworkers and I used Google Forms, Sheets, and Apps Script to spread a little cheer. The principles laid out in this post may inspire someone reading to do creative things with all the free services Google offers.
Background
Given the holiday season and our inability to congregate due to Covid-19, the folks at Maveris decided to throw a week of virtual holiday festivities, dubbed “Maveristmas”. These festivities included daily competitions where participants could submit entries to be later voted on by peers to earn points (or what we like to call “Magical Maveris Medallions’’) for a chance to win a plethora of prizes. To carry out these requirements, my coworkers and I decided to take advantage of Google Forms, Sheets, and Apps Script.
Each competition had a different set of parameters. The parameters were fairly consistent and while it was easy enough to manually create the competition submission forms, it was not as convenient to compile all of the submissions and manually create the forms for voting. Automating the creation of the voting form, based on the entries of the submission form, was an obvious choice. Automation allowed us to carry out these competitions quickly and efficiently throughout the week without having to sacrifice any valuable work or family time.
Requirements of our Generated Voting Forms
To ensure the voting forms were generated in a consistent manner, the following requirements needed to be met:
- The resulting form must display Picture entries
- Entries must display an associated caption if available
- The resulting form must display Text entries
- The resulting form must display Movie entries
- The resulting form must be configurable based on a given set of parameters such as Title and Description
- The resulting form must require the voter to vote based on three categories: Most Creative, Original, and Hilarious.
- The resulting form must alert administrators if a voter has selected the same entry for multiple categories.
- A voter must only be able to submit one entry
- A voter must be logged in with a valid account
- A voter must earn a configurable amount of points after submitting their voting form submission; these points must be tracked in a master Google Sheet.
Limitations
During development of a proof-of-concept, I discovered there were some limitations of Apps Script and the Forms Service API, including:
- The inability to add images to MultipleChoice Items
- The inability to embed Video Items from sources other than YouTube
- The inability to add text boxes that are not tied to a submittable Form Item or a Form Section Header
These limitations meant that I would have to find alternative ways to display Picture and Video Entries.
Generating Forms based on submissions from other Forms
To facilitate the dynamic creation of the voting forms, we wanted to have a simple way to pass parameters to our script without having to hard code things. To do this, we decided to create a Google Sheet to store information about each competition, including: the name and description of the competition, the entry submission form ID, and the number of points each voter would get for completing the submission and voting process.
Additionally, to simplify the extraction of the parameters from a spreadsheet, we used Named Ranges to specify the range of cells with the pertinent data. The sheet and the range were given the same name.
I then wrote the following function to lookup the values based on a given set of parameters:
const spreadsheet_id = "INSERT SHEET ID HERE";function lookupValueFromSpreadsheet(field, lookup_field, keyfield="CompetitionId", sheetname="CompetitionParameters") {
const charList = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
var ss = SpreadsheetApp.openById(spreadsheet_id);
var sheet = ss.getSheetByName(sheetname);
var named_range = ss.getNamedRanges().filter(r => r.getName() === sheetname)[0];
var rows = named_range.getRange().getValues();
var fields = rows[0];
var items = rows.splice(1);var keyfield_index = fields.map((f, index) => {
return f === keyfield ? index : null
}).filter(index => index !== null)[0]var row_index = items.map((row, index) => {
return row[keyfield_index] === lookup_field ? index : null
}).filter(index => index !== null)[0] + 2;var column_index = fields.map((f, index) => {
return f === field ? index : null
}).filter(index => index !== null)[0];var range = `${charList[column_index]}${row_index}`;var cell = sheet.getRange(range);return cell}
This function effectively uses the SpreadsheetApp API to access the Google Sheet. It then gets the range of values stored in the sheet and breaks them into rows. The first row is our header row, or fields as noted in the code, and the rest of the rows are the items. Using some fancy JavaScript, we extract the range for the desired cell by filtering on the indexes of the specified key field, row, and column indexes. This range inevitably ends up being in a familiar format, such as “A3” for example that can then be passed to the Spreadsheet App to extract the given cell as a Range object. This object is then returned and allows us to get or set the value:
// Getting The Value of a Cell:
const competition_name = lookupValueFromSpreadsheet(competition_id, "CompetitionName").getValue();// Setting The Value of a Cell
const competition_description = lookupValueFromSpreadsheet(competition_id, "CompetitionDescription")
competition_description.setValue("This is just an example, we did not actually do this");
As shown below in the Apps Script Debugging Console, we are now extracting the data we need from cells. We can override the given keyfield or sheetname easily by passing them in as additional parameters.
Creating the Form and setting its Properties
This is perhaps the easiest thing to do, and given our method of passing parameters via a Google Sheet, we could have customized this process even further. Using the FormApp API to meet our requirements, all we needed was the following:
// Create Form
var form = FormApp.create(competition_id);// Set Form Properties
form.setTitle(competition_name);
form.setDescription(competition_description);
form.setConfirmationMessage("Thank you for voting! Have a Merry Maveristmas and a Happy Holiday!")
form.setAcceptingResponses(true);
form.setCollectEmail(true);
form.setLimitOneResponsePerUser(true);
form.setRequireLogin(true);
This is what allowed us to ensure that the user was logged in with their Google Account and also made it possible for us to limit each user to only respond once.
Enumerating the Submission Form Entries
As a bit of background, let’s presume that the Submission Entry Form looked something like this:
This means that each entry will have a caption and image associated with it. To enumerate the submissions from this form, we must first obtain the Form ID. This can typically be found in the URL when in edit mode on the form:
In our case, we take this ID and add it to the CompetitionParameters Spreadsheet mentioned earlier. Now, when we look up the parameters for this particular competition (CompetitionTwo), we will be able to easily extract the submission form ID.
To enumerate the submissions, we can use the FormApp API to access the form residing at the specified ID and loop through each submission to extract the contents to prepare our form:
function enumerateSubmissions(form_id) {
var form = FormApp.openById(form_id);
var submissions = [];
form.getResponses().forEach(resp => {
items = resp.getItemResponses();
var meta = {}items.forEach((item, i) => {
let title = item.getItem().getTitle()
let value = item.getResponse();
if (Array.isArray(value)) {
file = DriveApp.getFileById(value[0]);
meta.filename = file.getName();
meta.url = file.getUrl();
meta.id = file.getId();
meta.mimetype = file.getMimeType();
meta.content = file.getMimeType().includes('image') ? file.getAs("image/png") : '';
} else {
meta.title = value
}
});submissions.push(meta);
})
return submissions;
}
After opening the form using FormApp.openById(form_id), we loop through each response using the getResponses() method and the forEach JavaScript Array method. Each response contains what Google refers to as “Items”. These Items contain the entry data for each field in the form and may hold the data in various formats. As such, if the data is stored as an Array, we presume that the data is an uploaded file and we set a metadata variable accordingly. Otherwise, we presume the entry is text and we extract the value and assign it to the meta.title key. Once each Item has been iterated over for a particular submission, we push it to the submissions array and repeat.
Creating the Voting Form based on the submissions
Once we have the data enumerated from the submission form in the format we want, we can then add those submissions to the resulting Voting form. First, let me show you the result, then I’ll break it down into smaller pieces to explain what’s going on.
try { var submissions = enumerateSubmissions(competition_submission_form_id);
if (submissions.length == 0) {
throw new Error(`[-] No Submissions for ${competition_id}`);
} const categories = [
{ name: "Most Creative" },
{ name: "Most Hilarious" },
{ name: "Most Original" }
]; if (submissions.every(s => s.filename !== undefined)) {
// There are file entries which means we will create votes for each category
submissions.filter(s => s.mimetype.includes('image')).forEach(s => {
var image_item = form.addImageItem().setTitle(s.title);
try {
image_item.setImage(s.content);
} catch (e) {
form.deleteItem(image_item.getIndex());
var item = form.addMultipleChoiceItem().setTitle(s.title);
item.setChoices([item.createChoice(s.url)])
}
}); const videos = submissions.filter(s => s.mimetype.includes('video'));
if (videos.length) {
var video_item = form.addMultipleChoiceItem().setTitle("View Video Submissions")
video_item.setChoices(
videos.map(v => video_item.createChoice(`${v.title}: ${v.url}`))
)
} categories.forEach(category => {
var item = form.addListItem();
item.setTitle(category.name);
item.setChoices(
submissions.map(o => item.createChoice(o.title))
);
}); } else {
// No file entries, therefore we create a multi choice vote system for the best entry overall
var item = form.addMultipleChoiceItem();
item.setTitle("Best Caption");
item.setChoices(submissions.map(o => item.createChoice(o.title)))
} Logger.log("[+] Success! Form Created for " + competition_id)
} catch (e) {
console.error(e.message);
}
First we wrap everything in a try/catch statement. This is used for debugging purposes and allows us to write in cleanup actions if something were to fail. Those cleanup actions have been removed from this post as I felt they were unnecessary to explain the concepts and may confuse the reader, but as an example, something we did was delete the resulting form on error so that we didn’t have to do it manually.
Next, we enumerate the submissions using the function we discussed earlier:
var submissions = enumerateSubmissions(competition_submission_form_id);If no submissions were found, then we throw an error and exit
if (submissions.length == 0) {
throw new Error(`[-] No Submissions for ${competition_id}`);
}If that condition passes, we run another condition to check to see if the submission contains the filename attribute.
if (submissions.every(s => s.filename !== undefined)) {If the attribute is defined, the submissions are filtered based on their mimetype of either “image” or “video” entries. Each entry is then appended to the form based on the context of their mimetype. Images used the addImageItem method, while videos used the addMultipleChoiceItem. Additionally, each entry has their caption set using the title attribute of the associated submission.
submissions.filter(s => s.mimetype.includes('image')).forEach(s => {
var image_item = form.addImageItem().setTitle(s.title);
try {
image_item.setImage(s.content);
} catch (e) {
form.deleteItem(image_item.getIndex());
var item = form.addMultipleChoiceItem().setTitle(s.title);
item.setChoices([item.createChoice(s.url)])
}
});const videos = submissions.filter(s => s.mimetype.includes('video'));
if (videos.length) {
var video_item = form.addMultipleChoiceItem().setTitle("View Video Submissions")
video_item.setChoices(
videos.map(v => video_item.createChoice(`${v.title}: ${v.url}`))
)
}
If you will recall the limitations mentioned above, we ran into an issue where videos not hosted on YouTube could not be added to a Google Form, so we used the addMultipleChoiceItem as a way to list out the URLs to each video and the voter could simply click the link to view the video in a new tab. Another note, we ran into an issue where some pictures did not embed properly in the form, so as a quick workaround we threw in a try/catch statement and, on failure to embed the picture, deleted the image item and instead create a MultipleChoiceItem with a link to the picture.
For each category of Most Creative, Most Original, and Most Hilarious, a ListItem was then populated. This is where the actual votes would be cast:
categories.forEach(category => {
var item = form.addListItem();
item.setTitle(category.name);
item.setChoices(
submissions.map(o => item.createChoice(o.title))
);
});Finally, we conclude the form creation with the latter else block. This condition executes if the submission does not contain a filename. In this case, we assume that the entry is text based and we allow a single vote to be cast; “Best Caption”
} else {
// No file entries, therefore we create a multi choice vote system for the best entry overall
var item = form.addMultipleChoiceItem();
item.setTitle("Best Caption");
item.setChoices(submissions.map(o => item.createChoice(o.title)))
}Create The Form
You can find the final product of our form generation code here.
Now once we execute createForm(“CompetitionTwo”), we end up with a voting form that looks like this!
Form Validation and Awarding Points
Two of the requirements of this form were:
- The resulting form must alert administrators if a voter has selected the same entry for multiple categories.
- A voter must earn a configurable amount of points after submitting their voting form submission, these points must be tracked in a master Google Sheet.
To carry out these requirements, an additional app script had to be made and tied directly to the form which would run after submission. To track each voter’s participation and award them Magical Maveris Medallions, we created a TotalMedallions Spreadsheet, which contained multiple fields for each competition, as an example, the following is an excerpt of the fields in the TotalMedallions spreadsheet:
- EmailAddress
- Total
- CompetitionOne_Results
- CompetitionOne_Vote
- CompetitionOne_Submission
These fields were used to track each competition’s participation, award extra points for other activities, and track the total number of medallions each participant earned throughout the week. Be sure to check back with MaverisLabs later to read more about how the winner was selected using the TotalMedallions spreadsheet.
Using the following code, adapted and modified from the same structure as our form generation code, we could ensure that the form would update the TotalMedallions spreadsheet and that each user’s submission was unique.
function onSubmit(event) {
var form=FormApp.getActiveForm();
// returns the total number of form submissions
var last_response = event.response
var email = last_response.getRespondentEmail();
var items = last_response.getItemResponses();
var answers = items.map(i => i.getResponse());if (answers.some( (answer, index, all_answers) => all_answers.filter(a => a == answer).length > 1 )) {
var message = `[Log] :anger: ${email} has submitted multiple same submissions to ${form.getTitle()}`;
console.error(message);
SlackLog.log_to_slack(message)} else {
const votingpoints = lookupValueFromSpreadsheet(competition_id, "VotingPoints").getValue();
lookupValueFromSpreadsheet(email, `${competition_id}_Vote`, keyfield="EmailAddress", sheetname="TotalMedallions").setValue(votingpoints);SlackLog.log_to_slack(`[Log] ${email} just submitted to the ${form.getTitle()}`);
}}
Of note in the above, an Event Object is passed on submission of a Google Form tied via an Installable Trigger. This object contains the submission data of the user, from which we can extract the email address.
To ensure that votes cast for each category are unique, we run a conditional check against the answers using the JavaScript Array some method. This method tests that at least one element of the array evaluates to true in the given anonymous function. In the anonymous function, each answer is checked against the all_answers array (this is a reference to the original array to which the some method is used). By filtering the all_answers array down to a new array of only the items which are equal to the given answer, we can effectively determine that any array greater in length than 1 would include duplicates. As such, we use a Slack App tied via a Google App Script Library to alert the administrators of the conflict that has occurred. Again, check back with MaverisLabs later to read more on how the Slack App library is configured!
Finally, if no duplicates are found, we can award the user participation points using the lookupValueFromSpreadsheet function we created earlier and call the setValue method to set the value of the cell in the TotalMedallions spreadsheet.
Tying the onSubmit Code to the Google Form
To tie the onSubmit code above to the Google Form, open the Google Form and click on the 3 vertical dots in the upper right corner and select Script Editor.
This will open up a script editor to which you can add your code:
From there you can select Triggers -> Add Trigger in the left control bar
Using the form provided, you can set the properties as follows:
The important ones to note are the function and event type. They should be onSubmit and On form submit respectively. When you select Save, you will be asked to grant permissions to the app so that it may gain access to the used APIs specified in your code (i.e. FormApp and SpreadsheetApp). Once you’ve done that, the form should be ready to use! Be advised, if you modify your code and need to grant new permissions, you may need to save the trigger again so those permissions can be granted. Saving it in the App Script area does not always inform you of this.
Final Notes
This solution worked really well for us in a pinch. We had roughly 2.5 days to get everything set up and the free tools Google offers as part of their web software suite allowed us to get this job done quickly and more importantly, free. Given more time, we might have created a better solution to track the total points or created a fully automated Slack App for competition voting, but in reality, as @khr0x40sh likes to remind us, “sometimes the juice just ain’t worth the squeeze”. Regardless, I learned a lot about the capabilities of AppScript and hope that readers will gain some inspiration from this post to do cool things with AppScript or even come up with something better to host their own virtual holiday festivities.
Website: www.maveris.com
Email: info@maveris.com
Maveris exists to help your organization reach its fullest potential by providing thought leadership in IT and cyber so you can connect fearlessly. To learn more visit us at: www.maveris.com
