Build an Automated Email Workflow using Google Apps Script-Part 1

Sample Google Form

The Google Form that I will be using for this blog is a very simple Google Form that takes the details of the user and the problem that they are facing. The Google Form contains the following questions:

Name of the user

Phone number of the user

Email address of the user

Category of the Problem

Short description of the problem.

If you want to work with the form, you can click here.

You might be thinking what is the use of the Category of the Problem. In the company there are four important departments that are mentioned in the form. The aim is to send an email to only that department that is selected by the user in the Google Form.

Sample Google Sheet

The Google Sheet that I will be using contains the responses filled in by the user in the Google Form. The Google Sheet looks something like this.

If you want to work with the Google Sheet, click here.

Step1: Automation Script for Users

While you are in the Google Sheet, let’s open up the Script Editor to write some Google Apps Script. To open the Script Editor, follow these steps:

Click on Extensions and open the Script Editor.

2. This brings up the Script Editor as shown below.

We have reached the script editor lets code.

Our project is divided into two parts, a Google Apps Script code that sends a confirmatory email to the user thanking them for filling the Google Forms and another Google Apps Script file that will send an email to the department telling them to solve the query entered by the user.

The best part of this workflow is that everything is run by triggers in Google Apps Script. This means the entire process that we are building right now does not need any manual work.

var START_ROW = 2;

var START_COLUMN = 1;

function SendEmailtoUser() {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet1 = ss.getSheetByName("Form Responses 1");

var data1 = sheet1.getRange(START_ROW,START_COLUMN,sheet1.getLastRow()-1,6).getValues();

Here we are going to start off by declaring two global variables that we are going to use to get the data from the Google Sheet. Next we will be opening up a new function called SemEmailtoUser() inside which we will begin by getting the SpreadSheet. To do this we will be using the getActiveSpreadSheet() function.

Next we will be getting the first Google Sheet using the getSheetByName() function, by passing the name as a parameter. To get the data we will be using the getRange() function. Inside this function we will be passing the first row and first column followed by getting the last row using the getLastRow() function. After getting the range, we will be getting the values using the getValues() function.

data1.forEach(function(row,i){

var name = row[1];

var number = row[2];

var category = row[3];

var problem = row[4];

var email = row[5];

Next we are going to be using a forEach function to iterate through the data that we just got. After that we will be getting all the user details individually using row[i]. Using this we will be getting the name, phone number, category, problem and the email address of the user.

var subject = "Query recieved for ", category;

var EmailBody = "Dear " + name + "," + " "+

"We recieved your problem with the following details" + " "+

"Description : " + problem + " "+

"Category : " + category + " "+

"A Customer Service Executive will contact you shortly "+ " "

"Regards,"+ " "+

"XYZ Solutions."+ " ";

GmailApp.sendEmail(email,subject,"",{htmlBody: EmailBody});

});

}

Next we will be declaring the subject of the email. After declaring the subject we will move towards declaring the email body. The motive of this email is to give the user a confirmation that they have filled the form successfully. The body of the email just contains their details and gives them a confirmation that a company executive will contact them shortly.

Once we are done designing the email body, we move towards writing the code to send the email to the user. We will be using the GmailApp to send the emails to the users. Inside this we will be passing the email address of the user, subject and the email body.

Our code is complete and good to go.

Step2: Add the Trigger

As we had talked about it previously, our main aim is to automate this process. To automatically send these emails we will be using triggers. To add a trigger follow these steps:

Got to the Triggers section in the Apps Script Editor.

2. Here I have specified the function that I want the trigger to run. After this you have to specify the event type, that is going to be on form submit. So whenever a user fills the form the code will automatically send the email to the user.

Once you are done setting up the Trigger, go ahead and click on Save.

Step3: Check the Output

Everything is ready and good to go, let's go ahead and fill the form and see if the code works.

Here I have filled the form with all the required details.

On successful execution, you can see that I have received an email containing all the details that I filled in the form.

Promo- Welcome to Chartmat! Are you tired of struggling to make sense of your data in Google Sheets? Do you want to turn your sheets into powerful apps and dashboards that can help you make better decisions and drive your business forward? Look no further! With Chartmat, you can easily transform your Google Sheets into beautiful and interactive dashboards and apps. Whether you're a small business owner, a marketer, or a data analyst, Chartmat has something for everyone. With our easy-to-use platform and extensive range of features, you'll be able to turn your data into actionable insights in no time. So why wait? Sign up for Chartmat today and start unlocking the full potential of your data! Check out the link given below to know more about charmat!

https://chartmat.com/

Conclusion

In this blog we saw how we can use Google Apps Script to send emails automatically on the submission of the Google Forms.In the next blog we will be taking a look at how to send the email to the department that is specified in the form.

You can find the code for this blog by clicking on the GitHub link given below.

GitHub - aryanirani123/Automated-Emailing-Workflow

You can't perform that action at this time. You signed in with another tab or window. You signed out in another tab or…github.com

Feel free to reach out if you have any issues/feedback via Chartmat Community