Automated email responses using Gmail and google sheets with Google apps script

Automated email responses can be set up using Google Scripts, a scripting platform developed by Google for light-weight application development in the G Suite platform. This platform allows us to automate tasks across Google products including Gmail, Google Sheets, and Google Forms.

In this article, we will create a script that sends automated email responses using Gmail when a certain condition in Google Sheets is met. Specifically, if the term “freshers” is found in a certain column.

Prerequisites:

Access to Google Sheets and Gmail.

Basic knowledge of JavaScript, as Google Apps Script is based on JavaScript.

Step 1: Create a Google Sheet

Create a new Google Sheet and have some sample data ready. For instance, you can have columns for Name, Email, and Type, where “Type” might contain the term “freshers”.

Step 2: Open Google Apps Script Editor

Go to Extensions > Apps Script.

Delete any code in the script editor and replace it with the code below.

Step 3: Write the Script

Here’s a basic example of a script that sends an email if the term “freshers” is found in column C.

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getRange("A2:C"); // Assuming data starts at row 2
  var values = range.getValues();
  values.forEach(function(row) {
    var name = row[0]; // Assuming name is in column A
    var email = row[1]; // Assuming email is in column B
    var type = row[2]; // Assuming type is in column C
    if (type.toLowerCase() === 'freshers') {
      var subject = 'Welcome Freshers!';
      var body = `Dear ${name}, \n\nWelcome to our community! \n\nBest Regards, \nYour Team`;

      GmailApp.sendEmail(email, subject, body);
    }
  });
}

Explanation:

SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); – Gets the currently active sheet.

sheet.getRange(“A2:C”); – Gets the range starting from A2 to C in the active sheet.

values.forEach(function(row) {…}); – Iterates over each row in the range.

GmailApp.sendEmail(email, subject, body); – Sends an email to the specified email address with the given subject and body.

Step 4: Set Trigger

In the Apps Script, go to the left panel and click on the clock icon, which is the Triggers icon.

Click on + Add Trigger at the bottom-right.

Choose sendEmails for the function to run.

Choose Time-driven for the event source.

Choose the type of time-driven trigger (e.g., Hour timer, Day timer, etc.) and its frequency.

Save the trigger.

Step 5: Grant Permissions

You will be asked to grant permissions to the script to access your Gmail and Sheets. Click on Review Permissions.

Choose your Google Account and grant the necessary permissions.

Read more articles on Google Apps Script