A step-by-step tutorial on sending automatic emails based on spreadsheet data

Introduction

Do you manually send emails based on data in Google Sheets? With Google Apps Script, you can automate email notifications and save time!

With this guide, you’ll learn how to:
✅ Send emails automatically from Google Sheets 📧
✅ Customize email content based on spreadsheet data ✍
✅ Schedule emails to send at specific times ⏰

By the end, you’ll have a fully automated email notification system. 🚀

---

Step 1: Open Google Apps Script

1️⃣ Open your Google Sheet
2️⃣ Click Extensions > Apps Script
3️⃣ Delete any existing code and paste the script below

---

Step 2: Write a Script to Send Emails from Google Sheets

Here’s a simple script to send emails based on data in Google Sheets:

function sendEmailNotifications() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Emails"); // Change to your sheet name
  var data = sheet.getDataRange().getValues(); // Get all data
  
  for (var i = 1; i < data.length; i++) { // Start from row 2 (skip headers)
    var email = data[i][0]; // Column A: Email Address
    var name = data[i][1];  // Column B: Name
    var message = data[i][2]; // Column C: Message
    var status = data[i][3];  // Column D: Status (Sent/Pending)
    
    if (status !== "Sent") { // Only send if not already sent
      MailApp.sendEmail({
        to: email,
        subject: "Automated Notification",
        body: "Hello " + name + ",\n\n" + message + "\n\nBest Regards,\nYour Team"
      });

      sheet.getRange(i + 1, 4).setValue("Sent"); // Mark as Sent
    }
  }
}

---

Step 3: Structure Your Google Sheet

Before running the script, format your sheet as follows:

A (Email)	B (Name)	C (Message)	D (Status)
user@example.com	John Doe	Your report is ready.	Pending
test@email.com	Jane Smith	Please review the document.	Sent
another@email.com	Bob Brown	Reminder: Meeting at 3 PM.	Pending

📌 Column D (“Status”) ensures that each email is sent only once.

---

Step 4: Run the Script Manually

To test the script:
1️⃣ Click the Run button ▶ in Apps Script Editor
2️⃣ Grant permissions when prompted
3️⃣ Check your email inbox to confirm receipt

---

Step 5: Automate the Script with Triggers

To send emails automatically at specific times:

1️⃣ In Apps Script Editor, click Triggers (Clock icon)
2️⃣ Click + Add Trigger
3️⃣ Choose sendEmailNotifications
4️⃣ Select:

• Time-driven
• Daily / Hourly / Custom interval
  5️⃣ Click Save

Now, the script will run automatically at your chosen intervals! ⏰

---

Bonus: Send Emails with Attachments

Want to send attachments from Google Drive? Use this script:

function sendEmailWithAttachment() {
  var file = DriveApp.getFileById("YOUR_FILE_ID"); // Replace with your file ID
  var email = "recipient@example.com";
  
  MailApp.sendEmail({
    to: email,
    subject: "Automated Email with Attachment",
    body: "Please find the attached document.",
    attachments: [file.getAs(MimeType.PDF)]
  });
}

📌 Find your file ID:
1️⃣ Open Google Drive
2️⃣ Right-click a file > Get Link
3️⃣ Copy the long ID from the URL

---

Conclusion: Automate Email Alerts in Google Sheets

You now know how to:
✅ Send emails from Google Sheets 📧
✅ Customize email content with spreadsheet data ✍
✅ Automate emails with time-based triggers ⏰
✅ Add attachments from Google Drive 📎