Sending emails from Sheets in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are an office assistant managing customer communications.

📋 Request: Your manager wants you to send personalized thank-you emails to customers who made purchases last month using data from a Google Sheet.

📊 Data: You have a sheet with customer names, email addresses, purchase dates, and purchase amounts.

🎯 Deliverable: Create a system in Google Sheets that identifies customers who purchased last month and sends them a personalized thank-you email automatically.

Sample Data

Name	Email	Purchase Date	Amount
Alice Johnson	[e-mail]	2024-05-10	120
Bob Smith	[e-mail]	2024-04-22	75
Carol Lee	[e-mail]	2024-05-15	200
David Kim	[e-mail]	2024-03-30	50
Eve Martinez	[e-mail]	2024-05-05	180
Frank Wright	[e-mail]	2024-04-28	90
Grace Chen	[e-mail]	2024-05-20	130
Henry Adams	[e-mail]	2024-05-25	110

1

Step 1: Add a new column named 'Last Month Purchase' to identify if the purchase date is in the previous month.

=AND(C2>=DATE(YEAR(TODAY()),MONTH(TODAY())-1,1),C2<DATE(YEAR(TODAY()),MONTH(TODAY()),1))

Expected Result

TRUE for purchases made in May 2024 if today is in June 2024, FALSE otherwise.

2

Step 2: Filter the sheet to show only rows where 'Last Month Purchase' is TRUE to see customers who bought last month.

Use Data > Create a filter and filter 'Last Month Purchase' column to TRUE.

Expected Result

Only customers with purchases in May 2024 are visible.

3

Step 3: Open the Google Sheets Script Editor to write a script that sends personalized emails to filtered customers.

In Extensions > Apps Script, paste the following code: function sendThankYouEmails() { const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); const data = sheet.getDataRange().getValues(); const today = new Date(); const lastMonth = new Date(today.getFullYear(), today.getMonth() - 1, 1); const lastMonthEnd = new Date(today.getFullYear(), today.getMonth(), 0); for (let i = 1; i < data.length; i++) { const purchaseDate = new Date(data[i][2]); if (purchaseDate >= lastMonth && purchaseDate <= lastMonthEnd) { const email = data[i][1]; const name = data[i][0]; const amount = data[i][3]; const subject = 'Thank You for Your Purchase!'; const message = `Dear ${name},\n\nThank you for your purchase of $${amount} last month. We appreciate your business!\n\nBest regards,\nYour Company`; MailApp.sendEmail(email, subject, message); } } }

Expected Result

Script ready to send personalized thank-you emails to customers who purchased last month.

4

Step 4: Run the script 'sendThankYouEmails' from the Apps Script editor to send emails.

Click the Run button in Apps Script and authorize permissions if prompted.

Expected Result

Emails sent to customers who purchased last month with personalized messages.

5

Step 5: Add a confirmation column 'Email Sent' and update it to TRUE after sending each email in the script.

Modify the script to add: sheet.getRange(i+1, 6).setValue(true); // Assuming column F is 'Email Sent'

Expected Result

'Email Sent' column shows TRUE for customers who received emails.

Final Result

Name           | Email              | Purchase Date | Amount | Last Month Purchase | Email Sent
---------------------------------------------------------------------------------------------
Alice Johnson  | [e-mail]  | 2024-05-10    | 120    | TRUE                | TRUE
Carol Lee     | [e-mail]  | 2024-05-15    | 200    | TRUE                | TRUE
Eve Martinez  | [e-mail]    | 2024-05-05    | 180    | TRUE                | TRUE
Grace Chen   | [e-mail]  | 2024-05-20    | 130    | TRUE                | TRUE
Henry Adams  | [e-mail]  | 2024-05-25    | 110    | TRUE                | TRUE

✓Five customers made purchases last month and received thank-you emails.

✓The system automatically identifies last month's buyers and sends personalized emails.

✓The 'Email Sent' column helps track which customers have been contacted.

Bonus Challenge

Modify the script to send a summary email to your manager listing all customers who received thank-you emails.

Show Hint

Collect customer names and emails in an array during the loop, then use MailApp.sendEmail to send a summary after the loop.

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