How to send custom emails from a spreadsheet using google apps script.

We are in an age where most of us are constantly seeking an easier way to do things, especially in professional settings where there is a lot of repeated task. If you're currently looking for an easier way to send emails directly from your spreadsheet, well, you're in luck because this blog post is for you.

This post will be focusing on Google sheets because it's one of the coolest spreadsheets ever, and together with the google apps script, it turns into the hulk on steroids. If this is your first time hearing about apps script, here's a summary of it.

Now let us begin.

Let us assume that the task of sending a series of acceptance /rejection emails to job applicants was assigned to us, and for each email, we are to include the job position of the candidate and the status of their application.

Step One:

Create a template for the email we want to send in google docs.

You'll notice that in the email template above, we have place-holders for the applicant's name, position applied for, and status(i.e, accepted and rejected)

Step two:

We'll create a spreadsheet with values that we want to replace our placeholders with (Name, Position, and status) and add a send email button at the button of the sheet.

Step Three:

It's time for the fun part. Open the script editor by clicking on 'tools' located in the ribbon section and select script editor. The script editor will be opened in a new tab.

First, we'll write a function that takes applicant details and creates a custom email from the template, then sends an email to the applicant

function createDocument(name, email, position, status){
  //get the email template from google doc using the url of the file
  const templateUrl = 'https://docs.google.com/document/d/1sRHXLlb3nsaiGVodhPNdt0SeYE1Gy-UnB4HQ1r2Wn-8/edit'

  //Get that template
  const template = DocumentApp.openByUrl(templateUrl)

  // Extract the text in the body of the documemt
  const text = template.getBody().getText()

  //Replace all placeholders
  const emailBody = text.replace('#Name', name)
              .replace('#position', position)
              .replace('#status', status)


  MailApp.sendEmail(
    email,
    'Response to Job Application',
    emailBody
  )
}

Next, we'll create a second function that takes rows of data from the spreadsheet and calls the createDocument function on each row. The last action by this function will be to alert us that all emails have been sent.

function sendEmails() {
  //get the spreadsheet with the emails
  const emialSpreadsheet =  SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()

  //get all data inside the spreadsheet
  const data = emialSpreadsheet.getDataRange().getValues()

  //Iteriate through the data
  data.forEach((applicant, index) => {
    if (index > 0) //Skips the first array which is the header
    {
      createDocument(...applicant)
    }
  })

  SpreadsheetApp.getUi().alert('ALL Emails have been sent') //Show an alert on google sheet that the mails have been sent.
}

Finally we are going to assign the function above to the 'send email' button on the spreadsheet.
*Right click on the button
*Click on the three vertical dots to the left and select "Assign Script
*Type in "sendEmails" (this is the name of the function that we wish to assign to the button)

And that's it. Now with the click of a button, you can send as many customized emails as you want. Oh! wait!!, I forgot to mention that Google has a limit on the number of emails you can send using Apps script, which is 100/day for basic google accounts(e.g., gmail.com) and 1500/day for workspace accounts.

Enjoy your new superpower.