Shopee Logo
BI BOSS
MediumMedium
Automating Google Forms & Sheets using Apps ScriptSending emails or update spreadsheets based on triggers.
google-sheetsgoogle-sheetsapps-scriptapps-script
Automating Google Forms & Sheets using Apps Script
Back to HomeRead More...

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.

Acme Corp is universal, so you may find employees from different planets and starships, hence the Work From Home / Work From Office debate. Anyway, five of the employees have put in their preferences.

But HR has a problem. They would like an email to be fired to their inbox every time someone fills the form.

You have two options.

  1. Use an automation tool like Zapier — Zapier is an amazing tool to connect different apps & workflows to create a truly automated system. But, it comes at a price. In the free tier, you’re allowed to have just 100 actions a month, which can easily run out once you start understanding Zapier.
  2. Use Apps Script to trigger emails— Think about it. All they need is a couple of emails. This is a great opportunity for you to learn Google Apps Script & show-off in front of your colleagues.

Let’s understand what bounded-scripts are before we move ahead. App Scripts can be standalone or they can be bounded with a Google Doc, Form, Sheet, etc to enable our code to respond to certain events & update data in our documents.

So, how do you create a bounded script? Go to your Google Sheet & in the menu bar, open the Tools menu. Click on Script Editor to open up your blank canvas to the world of Apps Script!

Capturing Form Entries

The first step we need to take is to create a function that will be called whenever the form is submitted. Let’s create a function onFormSubmit(event) which takes in event data received from the trigger.

function onFormSubmit(event) {  
  // This function will be called everytime the form is submitted.  
}

Creating a Trigger

A trigger is an effect of an event.

Here, the event is the submission of the Google Form, the effect is the function call (onFormSubmit).

After creating the function, your script editor should look like this. Click on the stopwatch button to open the Apps Script Dashboard & create a trigger.

Click on the stopwatch icon to create a trigger.

Click on the Add a new Trigger button to open up the trigger options.

The Apps Script Dashboard

To create a trigger, you need to select which function should be called (since our Apps Script has only one function, it has autoselected the function)

A head deployment makes the current project code available. The advantage of a head deployment is that it is always in sync with the most recently saved code. When you create an Apps Script project, you automatically create a head deployment for that project.

The next two options are very important. The event source can be from a spreadsheet (because our script is bound to a Google Sheet), it can be time-driven, or from a Calendar event.

For now, select the ‘From a Spreadsheet’ option.

Next up, we need to select an event type, and we see our preferred event ‘On Form Submit’ on the list!

The failure notification settings can notify you of any errors during the trigger. You can choose to get an immediate notification or as per a desired frequency.

Click Save to create your trigger!

Extracting the form-data & sending an email

This brings us to the last step of our task. Google explains its event object, the one which is sent to onFormSubmit in the documentation here.

We can get the form-data in two ways. By using the namesValues object or the values array. The benefit of using namedValues is that you receive a completely readable JSON Object along with the form question and the value provided by the user!

Let’s print one of the form-data (a new submission) by using Logger. You can log anything by using the Logger.log() utility. This makes it easier to debug your script if anything goes wrong.

function onFormSubmit(event) {  
  Logger.log(event);  
  Logger.log(event.namedValues);  
}

You can view your logs in the My Executions tab in the Apps Script Dashboard.

See the namesValues log? It has all you need to send an email.

Like many other IDE & Code Editors, Apps Script Editor can help you with code suggestions so its easy to find the right function.

We will now extract data in a readable form in HTML & send it out to the Acme Corp HR Department.

Let’s loop over the formValues JSON object by using a for loop and create an unordered list.

function onFormSubmit(e) {  
  var formValues = e.namedValues;  
  var html = '<ul>';  
  for (Key in formValues) {  
    var key = Key;  
    var data = formValues\[Key\];  
    html += '<li>' + key + ": " + data + '</li>';  
  };  
  html += '</ul>';  
}

Great! It’s time to send that email.

Would you believe me if I told you we can send an email with just 1 line of code? 😮

We can. Just 1 line of code will send that email to any email address you want.

NOTE: Since you’re the owner of the sheet & the apps script, your email id will be used as the sender, so before you decide to prank anyone, think of the consequences ahead.

We will use the GmailApp library to send out the email. The GmailApp.sendEmail method takes the recipient's email, subject, body, options (optional, but required for our use case). We will be leaving the body blank since we are going to use the htmlBody option to send out a well-formatted email.

GmailApp.sendEmail('[<](mailto:joshvarun@gmail.com)Recipient email>', 'New Form Submitted!', '', {htmlBody: html});

Great! You’re all set!

If you submit an entry in the form now, you get an ERROR in your Execution dashboard! WHAT?

Oh, GmailApp.sendEmail requires certain permissions.

No biggie, let’s provide those permissions. Click on the play button next to the stopwatch button.

The play button will attempt to run your code, realize that you do not have permissions, and will prompt you to provide the required permissions for sending an email. The code will fail, since the onFormSubmit will not receive an event, don’t worry this was just to provide permissions.

If you submit an entry now, you should be able to send an email to your recipient! Great job! 😃

Now, let’s update the color of the city cell to blue every time someone enters the city name as Gotham 🦇.

var cityIndex = 4;  
  if (formValues\['City'\] == 'Gotham') {  
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form Responses 1').getRange(SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form Responses 1').getLastRow(), cityIndex).setBackground('#4287f5');  
  }

The above code sets the city column index. Indices start from 1. Then, if the city is Gotham, we invoke the SpreadsheetApp which gets the active sheet & the sub sheet by name.

The getRange function takes in [row, column] to determine the location of the cell and set background to it.

You can get the last row of your spreadsheet (since form entries will always be added at the bottom) by using the getLastRow() method.

That’s it! You have learned how to write a Google Apps Script that can perform an action based on a trigger!

Your spreadsheet should now look like this:

Batman…hmm!?

CONCLUSION

This article explored how you can use Google’s App Suite to automate Google Forms and Sheets. I hope you enjoyed it and that you find it helpful. Happy Coding! ❤️

Back to HomeRead More...