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, then 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 are going to use Google Forms to store our data. All Google Forms are connected to a Google Sheet where the values are stored, so we are going to 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 of type ‘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.

Selecting the text field element
<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!

Submitted info added to the Google Sheet

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 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 how 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.

Event Trigger for automatic emails

Click Save, sign in to your Google account and Voila!

~fin~

--

--

--

Software Engineer @ FordPro Charging. Passionate about Programming, Guitar, and Product development — Work with me: www.rohitagrawal.me

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Helpful sites to strengthen my coding skills!

KingdomX Weekly Report(3.12–3.18)

Aws using Command-line Interface

6 Main Outsourcing Risks and How to Mitigate Them

Stages of compilation process

Major Benefits of Magento Commerce Cloud 2021 | Milople

How to run multiple Flask apps on the same domain with https

How to Solve Software Problems

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Rohit Agrawal

Rohit Agrawal

Software Engineer @ FordPro Charging. Passionate about Programming, Guitar, and Product development — Work with me: www.rohitagrawal.me

More from Medium

What are the top Node.js development trends to watch in 2022?

Google Domains Synthetic Record Setup for Forwarding

Getting started with Meilisearch

Hands on APIs using Postman