How to Enable Automatic Email Sending After a Google Form Submission? - ⋮IWConnect

How to Enable Automatic Email Sending After a Google Form Submission?

Nowadays, most people use Google Forms to create surveys and share documents with their colleagues and friends. As we all know, Google Forms is a free Google application which we can use to quickly create and distribute a form to gather information. Usually, when we have created a Google Form and submitted it for its completion, we need to check the form itself to see whether our recipients have answered it, which can be a time-consuming process.

Google Forms can be also used for Release Management, or the complete process of software release, starting from planning to deploying, i.e. your employees can send requests for deployment approval via it. But, if the approval after submission is not automated, the employees will have to manually send an email to the Release Managers for deployment approval. This manual task takes some valuable time which can be used for more important and urgent tasks.

There is already an out-of-the-box functionality for automatic sending e-mails on form submission. However, that new functionality isn’t fully customizable. So, how can you change this? How can you implement a mechanism that will send back a custom email the moment an employee submits the form, i.e. how can you automate it? The following blog post focuses on implementing functionality that can make a custom look of the body of the e-mail based on our needs once a Google form is submitted. Additionally, this functionality can be combined with additional functionality like creating an event into the Google calendar which is linked with our form, and a lot more things that are not available in the already existing functionality.

Google Form and Google Spreadsheet linkage

Picture no.1 below represents a Google form which is part of the release management process. The Google form is connected to a Google spreadsheet in Google Drive, and whenever a response is added, it is automatically saved in the spreadsheet (in Google Drive). The Google Form can be changed according to our needs.

Picture no.1 Google Form
Picture no.2 The Google Spreadsheet that is linked to the Google Form

To implement the functionality of automated email sending after a Google form submission we need to create a Google Script and a trigger for the script.

Creating the trigger

Choose Script Editor from the Tool dropdown.  You will be navigated to a window that will look like the one on the picture below:

Picture no.3 Script Editor

If you click on the Current Project Triggers button, it will lead you to an edit page where you can configure your new trigger. The triggers for the script can be:

  • On open
  • On edit
  • On change
  • On form submit.

Because the goal is to implement a solution that will automate the email sending after a Google form submission, the trigger that should be chosen is on form submit. Thus, whenever an employee submits a form, the script will be triggered. In addition, the function that will be run should be chosen – in our case it is the custom function onFormSubmit.

Picture no.4 Edit Trigger

Another option that can be set here is the failure notification, which can be described in some other article.

Create the script

Once you have set the trigger, you should create the script. The code for the script is the following:  

function onFormSubmit(e) {
 var email = ""; 
  var subject = "Test Subject";  
  var message = "";
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); //Taking the current spreadsheet
  var lr = ss.getLastRow(); //Taking the last row number
  var headers = ss.getRange(1,1,1,ss.getLastColumn()).getValues()[0]; //Rest information from the lase row
  for(var i in headers){
    if (e.namedValues[headers[i]].toString() == "" || e.namedValues[headers[i]].toString() == ","){
    } //Excluding empty columns from the e-mail
      message += '<li>' + headers[i].bold() + ': '+ e.namedValues[headers[i]].toString() + '</li>';
    } //Gathering all the information from the filled columns from the las row
  message = 'Summary'+ "\n" + '<li>' + '<b>Deployment Request Id</b>: ' + lr + '</li>' + message;
  //Adding additional text to the body 
       GmailApp.sendEmail(email, subject,'', {htmlBody:message})
  //Triggering the E-mail

Therefore, once a user submits a form, the script will be triggered and an automated email will be sent.

Picture no.5 Test email

Automating the process of sending an email after a Google Form submission significantly improves the efficiency of the team, as it saves a considerable amount of time which is used for manually sending an email to Release Managers for approval. Additionally, the chances for errors are almost none, as there is no manual and double typing of the request. On top of it, the request is always sent to the right person and the functionality allows adding multiple recipients.

If you want to check how the functionality works, and/or try it for yourself, you can contact us.

Hristijan Tasevski
Hristijan Tasevski