Sending emails from Sheets in Google Sheets - Step-by-Step Guide

Introduction

This feature helps you send emails directly from your Google Sheets. It saves time by automating email sending based on your spreadsheet data without switching apps.

When you want to send personalized emails to a list of contacts stored in your sheet.

When you need to send reminders or notifications automatically from your spreadsheet data.

When you want to send a summary report by email based on your sheet's calculations.

When you manage event invitations and want to email attendees from the sheet.

When you want to send follow-up emails after collecting responses in your sheet.

Steps

Step 1: Open

- Google Sheets file where you want to send emails

Your spreadsheet is ready for adding email script

Step 2: Click

- Extensions menu, then select Apps Script

The Apps Script editor opens in a new tab

Step 3: Delete any code in the script editor and type

- Apps Script editor

You have a blank script to write your email sending code

💡 Use simple code like MailApp.sendEmail to send emails

Step 4: Copy and paste this example code

- Apps Script editor

Code is ready to send emails from sheet data

💡 Replace placeholders with your actual sheet column names and email content

Step 5: Click

- Save icon in Apps Script editor

Your script is saved

Step 6: Click

- Run menu, then select your function name

Script runs and sends emails based on your sheet data

💡 The first time you run, you will need to authorize the script to send emails

Step 7: Return

- Google Sheets tab

You see your sheet and can check if emails were sent

Before vs After

Before

Sheet has columns with email addresses and message details but no emails sent

After

Emails are sent to addresses listed in the sheet with personalized messages

Settings Reference

Authorization

📍 Apps Script editor when running script first time

Allow the script to send emails on your behalf

Default: Prompt on first run

Trigger setup

📍 Apps Script editor > Triggers (clock icon)

Automate when emails are sent without manual running

Default: No trigger by default

Common Mistakes

Not authorizing the script to send emails

The script cannot send emails without permission and will fail silently

Run the script and follow the authorization prompts to allow email sending

Using wrong column names or ranges in the script

Emails may be sent with wrong or empty data

Check your sheet column headers and update the script to match exactly

Running the script without testing on a small sample

You might send many unwanted emails by mistake

Test the script on a few rows before sending to all contacts

Summary

You can send emails directly from Google Sheets using Apps Script.

Write and run a simple script to automate email sending based on your sheet data.

Remember to authorize the script and test carefully before sending many emails.

Practice

1. What is the main purpose of using Google Apps Script to send emails from Google Sheets?

easy

A. To protect the sheet with a password

B. To send emails automatically using data from the sheet

C. To create charts and graphs in the sheet

D. To sort data alphabetically

Solution

1. Step 1: Understand the role of Google Apps Script

   Google Apps Script allows automation in Google Sheets, including sending emails.

2. Step 2: Identify the purpose related to emails

   The script reads data from the sheet and sends emails automatically, saving time.

3. Final Answer:

   To send emails automatically using data from the sheet -> Option B

4. Quick Check:

   Sending emails = To send emails automatically using data from the sheet [OK]

Hint: Remember: Apps Script automates tasks like emailing [OK]

Common Mistakes:

• Confusing email sending with data sorting
• Thinking Apps Script only creates charts
• Assuming it protects sheets with passwords

2. Which of the following is the correct way to send an email using Google Apps Script in Google Sheets?

easy

A. MailApp.sendEmail(recipient, subject, body);

B. EmailApp.sendMail(recipient, subject, body);

C. Mail.sendEmail(recipient, subject, body);

D. SendMailApp.email(recipient, subject, body);

Solution

1. Step 1: Recall the correct Google Apps Script email function

   The correct service is MailApp with the method sendEmail.

2. Step 2: Verify the syntax

   The syntax is MailApp.sendEmail(recipient, subject, body); which matches MailApp.sendEmail(recipient, subject, body);.

3. Final Answer:

   MailApp.sendEmail(recipient, subject, body); -> Option A

4. Quick Check:

   Correct function = MailApp.sendEmail(recipient, subject, body); [OK]

Hint: Use MailApp.sendEmail() to send emails [OK]

Common Mistakes:

• Using wrong service names like EmailApp or SendMailApp
• Mixing method names like sendMail instead of sendEmail
• Incorrect order or spelling of parameters

3. Given this script snippet in Google Sheets:

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var email = sheet.getRange('A2').getValue();
  var message = sheet.getRange('B2').getValue();
  MailApp.sendEmail(email, 'Hello', message);
}

What happens when you run sendEmails()?

medium

A. Nothing happens because sendEmail is not a valid method

B. An error occurs because getRange needs row and column numbers

C. The script sends emails to all rows in the sheet

D. An email is sent to the address in cell A2 with the message from B2

Solution

1. Step 1: Analyze the script's actions

   The script gets the email from cell A2 and message from B2, then sends an email.

2. Step 2: Confirm the method usage

   getRange('A2') and getRange('B2') are valid, and MailApp.sendEmail is correct.

3. Final Answer:

   An email is sent to the address in cell A2 with the message from B2 -> Option D

4. Quick Check:

   Email sent to A2 with B2 message = An email is sent to the address in cell A2 with the message from B2 [OK]

Hint: Check cell references and MailApp method correctness [OK]

Common Mistakes:

• Thinking getRange('A2') is invalid syntax
• Assuming the script sends emails to all rows automatically
• Believing sendEmail is not a valid method

4. You wrote this script to send emails from your sheet:

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var email = sheet.getRange('A2').getValue();
  var message = sheet.getRange('B2').getValue();
  MailApp.sendEmail(email, 'Subject', message)
}

But it does not send any emails. What is the likely problem?

medium

A. You forgot to authorize the script to send emails

B. Missing semicolon after sendEmail line causes script failure

C. getRange('A2') is incorrect syntax

D. MailApp.sendEmail requires four parameters

Solution

1. Step 1: Check syntax and parameters

   The script syntax is mostly correct; missing semicolon is not fatal in Apps Script.

2. Step 2: Consider authorization requirements

   Google Apps Script requires user authorization to send emails; without it, emails won't send.

3. Final Answer:

   You forgot to authorize the script to send emails -> Option A

4. Quick Check:

   Authorization needed for sending emails = You forgot to authorize the script to send emails [OK]

Hint: Always authorize scripts before sending emails [OK]

Common Mistakes:

• Assuming missing semicolon stops script
• Thinking getRange('A2') is invalid
• Believing sendEmail needs four parameters

5. You want to send personalized emails to multiple recipients listed in column A, with messages in column B, starting from row 2. Which script correctly sends emails to all rows with data?

hard

A. function sendAllEmails() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var lastRow = sheet.getLastRow();
for (var i = 2; i <= lastRow; i++) {
var email = sheet.getRange('A' + i).getValue();
var message = sheet.getRange('B' + i).getValue();
MailApp.sendEmail(email, message, 'Hello');
}
}

B. function sendAllEmails() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var lastRow = sheet.getLastRow();
for (var i = 1; i < lastRow; i++) {
var email = sheet.getRange(i, 1).getValue();
var message = sheet.getRange(i, 2).getValue();
MailApp.sendEmail(email, 'Hello', message);
}
}

C. function sendAllEmails() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var lastRow = sheet.getLastRow();
for (var i = 2; i <= lastRow; i++) {
var email = sheet.getRange(i, 1).getValue();
var message = sheet.getRange(i, 2).getValue();
MailApp.sendEmail(email, 'Hello', message);
}
}

D. function sendAllEmails() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var lastRow = sheet.getLastRow();
for (var i = 2; i <= lastRow; i++) {
var email = sheet.getRange(i, 1).getValue();
var message = sheet.getRange(i, 2).getValue();
MailApp.sendEmail('Hello', email, message);
}
}

Solution

1. Step 1: Check loop range and indexing

   The loop must start at row 2 to skip headers and go to lastRow inclusive.

2. Step 2: Verify getRange usage and sendEmail parameters

   Using getRange(row, column) is correct. sendEmail parameters are (email, subject, message).

3. Final Answer:

   Loops from row 2 to lastRow and sends emails with correct parameters -> Option C

4. Quick Check:

   Loop from 2 to lastRow, sendEmail(email, subject, message) = function sendAllEmails() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var lastRow = sheet.getLastRow(); for (var i = 2; i <= lastRow; i++) { var email = sheet.getRange(i, 1).getValue(); var message = sheet.getRange(i, 2).getValue(); MailApp.sendEmail(email, 'Hello', message); } } [OK]

Hint: Loop from row 2; use sendEmail(email, subject, message) [OK]

Common Mistakes:

• Starting loop at row 1 including headers
• Swapping subject and message parameters
• Using incorrect getRange syntax