Automating Google Forms & Sheets using Apps Script

Sending emails or update spreadsheets based on triggers.

Google offers a wide range of office tools with exceptional capabilities to have your documents available on-the-go. I recently came across Apps Script a couple of months back when I had to make some calls to an API and update a Google Spreadsheet. Usually, I would call the APIs in a Python Script & update the Google Sheet. Now, however, I feel that I could have been a lot more productive had I used Apps Script.

In this article, we’re going to learn how to automate your Google Sheets & Forms to work with certain triggers to send emails or update a row in your spreadsheet. Let’s begin.

What is App Script?

Apps Script is a scripting platform developed by Google for light-weight application development in the G Suite platform. Ever since it’s launch back in August 2009, Apps Script has improved drastically and expanded along with different Google Apps like Docs, Sheets, Forms, etc.

It lets you rapidly develop & publish add-ons for all the Google Apps, including Gmail! Gmail provides a card & widget library so you don’t have to worry about the UI development across Desktop & Mobile!

Creating a Google Form

The first thing we need to do is create a simple Google Form & create a form. If you’ve never created one before, get started by clicking the + button on the bottom right.

We’re going to create an Employee Registration Form for Acme Corporation which will ask for their name, employee ID, the city they’re based out of & their work preference (home/office).

Great, now we need to link a Google Spreadsheet where the form responses will be saved. Head ever to the Responses tab & click on the Sheets button & when prompted to use an existing spreadsheet or create a new one, select the new spreadsheet option.

Link a spreadsheet to your Google Form

Now we have our spreadsheet ready and linked with the form. Add a few responses to the form so that we have some data to play with.