Sending emails from Sheets in Google Sheets - Dashboard Guide

Dashboard Mode - Sending emails from Sheets

Goal

Send personalized emails automatically using data from Google Sheets to save time and avoid manual errors.

Sample Data

Name	Email	Order ID	Order Date	Status
Alice	[e-mail]	1001	2024-06-01	Shipped
Bob	[e-mail]	1002	2024-06-02	Processing
Carol	[e-mail]	1003	2024-06-03	Delivered
David	[e-mail]	1004	2024-06-04	Cancelled
Eve	[e-mail]	1005	2024-06-05	Shipped

Dashboard Components

• KPI Card: Total Orders = =COUNTA(A:A)-1 shows 5 orders.
• KPI Card: Orders Shipped = =COUNTIF(E:E, "Shipped") shows 2 shipped orders.
• Script Button: A button labeled "Send Emails" triggers a Google Apps Script function to send emails to customers with their order details.
• Script Function: The script reads each row, composes a personalized email, and sends it using MailApp.sendEmail().

function sendOrderEmails() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getRange(2, 1, sheet.getLastRow() - 1, 5).getValues();
  data.forEach(row => {
    const [name, email, orderId, orderDate, status] = row;
    if (email && status !== 'Cancelled') {
      const subject = `Order Update for Order #${orderId}`;
      const body = `Hi ${name},\n\nYour order placed on ${orderDate} is currently: ${status}.\nThank you for shopping with us!`;
      MailApp.sendEmail(email, subject, body);
    }
  });
}

Dashboard Layout

+----------------------+---------------------+
| Total Orders (5)     | Orders Shipped (2)  |
+----------------------+---------------------+
|                      Send Emails Button                      |
+-------------------------------------------------------------+

Interactivity

Clicking the "Send Emails" button runs the script that reads the current sheet data and sends emails only to customers whose status is not "Cancelled". If you update the data (add/remove orders or change status), the KPIs update automatically and the script sends emails based on the latest data.

Self Check

If you add a filter to show only orders with status "Shipped", which components update?

• The Orders Shipped KPI does not update to show the count of visible shipped orders (counts all data).
• The Send Emails script will send emails only to the filtered visible rows if modified to respect the filter (otherwise it sends to all non-cancelled).
• The Total Orders KPI remains the same.

Key Result

Dashboard to track order counts and send personalized order status emails directly from Google Sheets.