Send Email from a Spreadsheet

Sponsored by

Stay up-to-date with AI.

The Rundown is the world’s fastest-growing AI newsletter, with over 500,000+ readers staying up-to-date with the latest AI news and learning how to apply it.

Our research team spends all day learning what’s new in AI, then distills the most important developments into one free email every morning.

Sign up with one click.

Since Last Time…

I’ve run the Knoxville Marathon. It was fun, and my 2nd best time (3:19:32). Focusing on speed so far this year, and I’ve got another race next month…

Now to the sheets…

Sheet Setup

Let’s use a very basic example to show how the Apps Script works.

We have columns for email addresses, subject lines and the body of the email. Then we have a checkbox column to denote whether the email has been sent.

There’s an options column too, but that will be for a later tutorial…

Let’s select all our data and create a named range to use in our script. I’ll name it emails. I like using the name box at the top left, but you can open the named ranges sidebar by selecting Data - named ranges from the top menu.

Apps Script Setup

Open up Apps Script by selecting Extensions - Apps Script from the top menu.

The first thing our script needs is to read in the data. So inside our function, appropriately named sendDatEmail(), we grab the values from the named range we just created:

let emails = SpreadsheetApp.getActiveSpreadsheet().getRangeByName("emails").getValues()

Then we just want to loop through each line and send an email based on the info we’ve provided.

We can use a forEach loop for this and pair it with the GmailApp.sendEmail() function.

In it’s most basic form, sendEmail takes a recipient, subject and body to work:

emails.forEach((e)=>{
    if(e[4]){
      GmailApp.sendEmail(e[0],e[1],e[2])
    }
  })

The if statement checks to see whether there’s a checkmark in the send column. If there is, then we won’t resend this email.

This will use the email address you’re currently logged into Google Sheets with to send the email.

Custom Menu

As I often enjoy doing, we can create a custom menu to run this function from.

Here’s the code for that. It’s a boilerplate from Google. Every time we open this spreadsheet, it will create a menu titled, “Send Email”.

We’ve added one item to the menu titled “Send Checked Emails”. And it will run the function we created entitled sendDatEmail().

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp, SlidesApp or FormApp.
  ui.createMenu('Send Email')
      .addItem('Send Checked Emails', 'sendDatEmail')
      .addToUi();
}

Thank you so much!

It means a lot that you’ve read this, and I hope it’s informed and/or entertained you for a few moments today!

Would love to say hi. Here are the best places to find me:

• 📺️ Videos on YouTube

• 💾 Products on Gumroad

• ✉️ Connections on LinkedIn

Need More Help?

• Need a powerful automation tool for Google Sheets? Try Coefficient to automatically import data and sync with your business systems.

• Hire an expert to help complete your next Google Sheets, Apps Script or Google Workplace project.

Em