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 that 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
Image 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.
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 in the picture below:
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 submission. 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.
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 = "hristijan.tasevski@test.com";
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
else{
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.
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.
