Google Forms + Google Sheets + Google Apps Script = ❤. Easy Database Solution for Static Websites
What up Hackerman! This kind of a tutorial-style article is not something I usually do, but I just had to share this amazing service provided for Google that you can integrate into your projects RIGHT NOW! I’ll walk you through the process of getting data from your HTML form to a Google Sheet, and even set trigger events (such as sending a notification mail) on each entry.
“Why should I care?” — says the ignorant reader
Let’s take a look at the following scenario:
Our protagonist is a guy named Rohit(not gonna use another name coz I’m a narcissict). Rohit can make beautiful static websites, but is a novice in back-end development. He finds the complexity of it all to be very daunting and intimidating and thus would love to not go that route. He spends hours researching how to integrate the Google Sheets API into the static website of a project he was working on to store some data, but that turns out to be a huge waste of time. There was no way he could do that without running into the Google OAuth 2.0 Authentication issue and thus having to create a server, learn NodeJS, learn to deal with authentication keys and other bullcrap, not understand a thing, weep and then punch through his monitor (in that order). It wasn’t a viable option because he was working on a deadline of 2 days, and having to explain non-technical people the technicalities of the above problems using “heavy” words like servers, authentication, cookies, HTTP requests, etc is not fun because one of them is bound to snap at some point and shout “NERDDD!” while the jock starts making fart noises using his armpits.
So, if you feel like you’re in the same boat, this post might just be for you. Let’s assume you provide a service and want to collect data from your customers for the same. You also want to send them a confirmation email automatically once they click that submit button. Here’s how you would go about it:
Getting the Form ready
The first step is to create the HTML form that we are going to use to send the data. Here is a simple form made using Bootstrap. Feel free to copy for testing purposes.
Collecting data from the form into a Google Sheet
Now we get into the crux of this post. Since using the Google Sheets API directly with our above form would require OAuth Authentication, we will use Google Forms to store our data. All Google Forms are connected to a Google Sheet where the values are stored, so we will have our HTML form as an external interface to the Google Form to submit the values.
Step 1: Make a Google Form with each field corresponding to the names of the fields in the HTML form.
Make sure that all the fields in the Google Form are ‘Short answer.’
Step 2: Get submit information of the form.
Click on Preview -> Inspect -> Select an element to inspect it.
Look for the following line and copy the attributes to the <form> tag in our HTML form.
<form action=”https://docs.google.com/forms/d/e/1FAIpQLSfwKsvVcehBx5KDNnvrMpg8B1Mwz-BenyI9zVrLBae1gDEm8A/formResponse" target=”_self” method=”POST” id=”mG61Hd”>Step 3: Get information about each field.
Now inspect each text field element, and you should get information about the corresponding <input> tag.
<input type="text" class="quantumWizTextinputPaperinputInput exportInput" jsname="YPqjbf" autocomplete="off" tabindex="0" aria-label="Full Name" aria-describedby="i.desc.927799477 i.err.927799477" name="entry.1464718183" value="" dir="auto" data-initial-dir="auto" data-initial-value="">Copy the ‘name’ attribute and paste it into the <input> tag of the corresponding <input>(<select> tag, if a multiple-choice field) tag of our HTML form. Repeat for all fields. Our updated code should look like this:
Now let’s test it out. Go to your HTML form, fill in the information and submit it. The information should have been added to the Google Sheet linked to the Google Form! Hurray!
Sending the “Hey, you just filled that form, didn’t ya?” Email
If all you wanted was to store some data, then you can close this tab and open Pornhub or something. If you want to make this method even cooler by adding custom add-ons, then keep reading. My project needed me to send an automated email to a new user as soon as he filled out the form. I couldn’t find one online, so I made my own using Google Apps Script. Google Apps Script lets you use Javascript to create custom add-ons for all your Google Apps(Forms, Sheets, etc.).
We’ll add a script to our Spreadsheet itself which does the above. Here’s how to go about it:
Step 1: Define Email Content
Add a new Sheet to the same Spreadsheet. Name it whatever you want; I called mine “Sheet 2”. Cute, right? It’s Greek. Now, merge a few cells from the top left corner and write your content in it. Insert any variables you want with a ‘{variable_name}’ into the text body. Here’s what it should look like:
Step 2: Write the Script
Go to your main Sheet(should have a name like “Form Responses 1”) -> Tools -> Script Editor. Now delete everything and insert the following script:
Save the Project and click on ‘Run’. An Authorization Popup is going to appear, go ahead and Review Permissions for it by signing into your Google Account. Now run the script, the email should have been sent.
Step 3: Automate the script by adding a trigger event
Currently, the script runs when we manually run it, but not all of us are as purposeless as Literature majors, so we need the script to run automatically when a new entry is added to the Spreadsheet. We do this using Event Triggers.
In your Script Editor, go to Edit -> Current project triggers -> Create a new trigger and make the following changes.
Click Save, sign in to your Google account and Voila!
~fin~
