Build an Automated Email Workflow using Google Apps Script

Step1: Automation Script for Departments

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 to code.

//Categories

var cat1 = "Service Requests"

var cat2 = "Product Information"

var cat3 = "Franchise Enquiry"

var cat4 = "Order Status"

//Backed Email Addresses

var email1 = "department1@gmail.com";

var email2 = "department2@gmail.com";

var email3 = "department3@gmail.com";

var email4 = "department4@gmail.com"

Here I have started out by declaring the different categories under which the queries can be made. Next I have declared the different email addresses for the respective departments.

var START_ROW = 2;

var START_COLUMN = 1;

Here we are going to declare two variables that we are going to use to get the data from the Google Sheet.

function SendEmailtoBackend(){

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 I have declared a new function called SendEmailtoBackend(), after which we will be getting the spreadsheet using the getActiveSpreadsheet() function. Next we will be getting the sheet using the getSheetByName() function inside which we will be passing the name of the sheet.

Next we will be using the getRange() function inside which we will be passing the start row and start column after which we will be using the getValues() function to get the values for the specified range.

data1.forEach(function(row,i){

var name = row[1]

var number = row[2];

var category = row[4];

var problem = row[5];

var user_email = row[3];

var subject_email = "New Email received through Form"

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. These details will be then used to send it to the respective department to handle the query of the customer.

if (category == cat1){

var body1 = "Hello Team " + cat1 + " "+

"The query form was filled with the following details" + " "+

"Description : " + problem + " "+

"Name : " + name + " "+

"Email : " + user_email + " "+

"Phone number : " + number + " "+

"Resolve this issue as soon as possible "+ " ";

GmailApp.sendEmail(email1,subject_email,"",{htmlBody:body1})

}

else if(category == cat2){

var body2 = "Hello Team " + cat2 + " "+

"The query form was filled with the following details" + " "+

"Description : " + problem + " "+

"Name : " + name + " "+

"Email : " + user_email + " "+

"Phone number : " + number + " "+

"Resolve this issue as soon as possible "+ " ";

GmailApp.sendEmail(email2,subject_email,"",{htmlBody:body2})

}

else if(category == cat3){

var body3 = "Hello Team " + cat3 + " "+

"The query form was filled with the following details" + " "+

"Description : " + problem + " "+

"Name : " + name + " "+

"Email : " + user_email + " "+

"Phone number : " + number + " "+

"Resolve this issue as soon as possible "+ " ";

GmailApp.sendEmail(email3,subject_email,"",{htmlBody:body3})

}

else if(category == cat4){

var body4 = "Hello Team " + cat4 + " "+

"The query form was filled with the following details" + " "+

"Description : " + problem + " "+

"Name : " + name + " "+

"Email : " + user_email + " "+

"Phone number : " + number + " "+

"Resolve this issue as soon as possible "+ " ";

GmailApp.sendEmail(email4,subject_email,"",{htmlBody:body4})

}

});

}

As we had discussed previously, we want to send a custom email to the respective department specified in the Google Form. To do this we are going to open 4 for loops for each category and if the category matches then the email will be sent to that specific department.

For each department a new email body is designed and the details from the Google Sheet are filled into the Google Sheet. Once the body is ready, an email will be sent to that department.

On successful execution of the script, it will send out the following two emails:

The first email will go to the Customer giving them a confirmation that their query has been registered.

The second email will go to the respective department telling them to look into the query and provide the customer a solution.

Our code is ready 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.

Here you can see, when the customer fills the form, the respective department gets an email telling them the details of the query filled and user details for further contact.

This tells us that our Automated emailing workflow has successfully worked.

Conclusion

In this two part series we saw how we can build an automated emailing workflow using Google Sheets and Google Apps Script. If you haven’t read the first part check out the link below, additionally if you want to copy the entire code, check out the Github link given below.

https://chartmat.com/blog/build-an-automated-email-workflow-using-google-apps-script

GitHub - aryanirani123/Automated-Emailing-Workflow

Feel free to reach out if you have any issues/feedback at aryanirani123@gmail.com.

Build an Automated Email Workflow using Google Apps Script — Part2

Step1: Automation Script for Departments

Step2: Add the Trigger

Step3: Check the Output

Conclusion