Sending Automated Emails using Google Apps Script

One of the major means of communication these days is email. Organizations and individuals send out a huge number of emails every day. Sometimes, we find ourselves in situations where we want to send emails to several people listed on a Google spreadsheet. Can you imagine having to send out an email to hundreds of people by copying and pasting one at a time? (This once happened to me!)

What if there is a way to automate the process so that it can be completed in under a minute?

If this ever happened to you or it is likely to happen to you soon, or you are one of those planning on sending out the end of year emails — just get a cup of coffee and let’s get our hand dirty with some small code.

Basically, with a Google Apps script, you can send out customized messages to several people with a single click and also customize the content per user if need be. In the next 10 minutes, you will see how your life will become very easy by sending out a large number of emails using Google App script. You can thank me later

Getting Started

Requirements:

A browser, a Google account, stable internet connection, a spreadsheet, and the Google Apps script editor

Google Apps Script is a JavaScript cloud scripting language that provides easy ways to automate tasks across Google products and third party services and build web applications.

The good thing about this is you don’t even have to own a computer to do this. It’s a coding language with which you can write small programs to perform custom behaviors that go beyond the standard features of Google Apps. This code is stored and executed on Google’s servers.

To start with, say we wanted to send out emails at the end of the year to thank clients. For example, we have all their names on a spreadsheet.

Sample Message:

Dear User,

As the year winds down, we want to take the opportunity to thank you for helping us eliminate stupid mental efforts and automate our tasks. Having someone like you is an added advantage for us.

I cannot thank you enough for making our tasks simpler, and I look forward to working with you in the coming years.

Best,
Olatunde Garuba

You can find a sample sheet here.

Step 1

Create your own test sample list or copy the spreadsheet from the link above onto your own drive.

Step 2

On the spreadsheet, click on tools, and select script editor.

The script editor is an integrated development environment (IDE). It is used for writing code, debugging, and testing. This is where we will write the code to help automate the task at hand.

Now it's time to get serious!

We will be using two Google Apps script classes called SpreadsheetApp and MailApp.

The SpreadsheetApp class helps us obtain the spreadsheet we want to work with, while the MailApp class is used to send out the emails.

The MailApp class has five (5) methods:

• getRemainingDailyQuota()
• sendEmail(message)
• sendEmail(recipient, subject, body)
• sendEmail(recipient, subject, body, options)
• sendEmail(to, replyTo, subject, body)

But we will be using sendEmail(recipient, subject, body).

This is because we need three different parameters to send an email: an address to send the email to (recipient), the subject of the message (subject), and the message that will be sent out to the recipients (body).

On the script editor, we will be using for-loops, variables (var), and a function.

A function is a named section of a program. It's reusable code that performs a single, related action. Functions provide better modularity for an application. The term function is often used synonymously with operation and command

You can copy and paste the following code into your script editor.

function sendEndOfYearEmails() {
  var spreadSheet = SpreadsheetApp.getActiveSheet();
  var dataRange = spreadSheet.getDataRange();
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  var text = ‘our initial sample text’;
  for (var i = 1; i < data.length; i++) {
    (function(val) {
      var row = data[i];
      var emailAddress = row[1]; //position of email header — 1
      Var name = row[0]; // position of name header — 1
      var message = ‘Dear’ + name + ‘\n\n’ + text;
      var subject = ‘Sending emails from a Spreadsheet’;
      MailApp.sendEmail(emailAddress, subject, message);
      })(i);
   }
}

In the code snippet above:

1. We declared a variable named spreadsheet. This will store the content of the current active spreadsheet, which we obtained using SpreadsheetApp.getActiveSheet().
2. Store the Range corresponding to the dimensions in which the data is present in the active spreadsheet in another variable called dataRange.
3. getValues() was used to get the rectangular grid of values for this range. This returns a two-dimensional array of values, indexed by row, then by column.
4. After this, we looped over the two-dimensional array stored in the data variable, starting at the second index in the data (the first index is the header row). While looping over the data, we obtained the corresponding values to the email address and their names.

Step 3

After this, save the file.

Note: If the red asterisk is visible in the name of the file in the editor, it means you haven’t successfully saved the file.

After saving your file, click run. This will pop up a dialog box asking for app authorization.

Click on review permission to review your permission. This will take you to the Google authorization page.

Click allow and… Voila, all your emails are sent!

Don't forget to check your sent mailbox to confirm.

Wrapping out

Hope you enjoyed following along in this tutorial. If you happen to also be interested in learning more about Git, I wrote about Git Cherry-pick in my previous tutorial.