Google Sheetsspreadsheet~15 mins

Sending emails from Sheets in Google Sheets - Real Business Scenario

Choose your learning style9 modes available

Learn Why Deep Sheet Try Challenge Scenario Recall Dash

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.

Progress0 / 5 steps

Sample Data

Name	Email	Purchase Date	Amount
Alice Johnson	alice@example.com	2024-05-10	120
Bob Smith	bob@example.com	2024-04-22	75
Carol Lee	carol@example.com	2024-05-15	200
David Kim	david@example.com	2024-03-30	50
Eve Martinez	eve@example.com	2024-05-05	180
Frank Wright	frank@example.com	2024-04-28	90
Grace Chen	grace@example.com	2024-05-20	130
Henry Adams	henry@example.com	2024-05-25	110

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

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

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

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

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

Final Result

Name           | Email              | Purchase Date | Amount | Last Month Purchase | Email Sent
---------------------------------------------------------------------------------------------
Alice Johnson  | alice@example.com  | 2024-05-10    | 120    | TRUE                | TRUE
Carol Lee     | carol@example.com  | 2024-05-15    | 200    | TRUE                | TRUE
Eve Martinez  | eve@example.com    | 2024-05-05    | 180    | TRUE                | TRUE
Grace Chen   | grace@example.com  | 2024-05-20    | 130    | TRUE                | TRUE
Henry Adams  | henry@example.com  | 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