Google Sheets isn’t just for numbers; it can also send emails automatically with just a few clicks or lines of code. Whether you’re notifying clients, sending reminders, or creating personalized messages, Sheets offers powerful automation tools. By combining your spreadsheet data with Gmail, you can send emails directly from your sheet. In this article, we’ll explore different ways to send emails from Google Sheets, from simple triggers to full mail merges.
What Does It Mean by Sending Email from Google Sheets?
Sending email from Google Sheets means automating personalized email delivery directly from your spreadsheet using Google Apps Script. Instead of manually copying and pasting data into an email, you can send custom messages to multiple recipients in just one click. This is especially useful for sending updates, reports, notifications, or reminders. Google Sheets lets you send emails easily, whether it’s a quick alert or a mail merge.
Sending Emails Automatically from Google Sheets
You can set up Google Apps Script to automatically send emails based on a schedule, like weekly reminders or monthly reports. This method works best for scheduled emails, such as weekly reminders or monthly reports, that run automatically without user interaction.
Suppose we have a dataset containing: Business Name, Email, Invoice Number, Due Date, and Invoice Amount. Now we will run a script with a trigger to send emails automatically.
➤ Click Extensions > Apps Script from the menu bar.
➤ Put the below code in and click Save.
function sendInvoiceReminders() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const lastRow = sheet.getLastRow();
const dataRange = sheet.getRange(2, 1, lastRow - 1, 5);
const data = dataRange.getValues();
data.forEach((row, index) => {
const businessName = row[0];
const emailAddress = row[1];
const invoiceNumber = row[2];
const invoiceAmount = row[4];
const dueDateStr = Utilities.formatDate(new Date(row[3]), Session.getScriptTimeZone(), "EEE dd-MMM-yyyy");
if (!emailAddress || !emailAddress.toString().includes("@")) return;
let amountStr = invoiceAmount;
if (typeof invoiceAmount === "number") {
amountStr = `$${invoiceAmount.toFixed(2)}`;}
const subject = `Invoice Reminder: ${invoiceNumber}`;
const message = `Dear ${businessName},
This is a friendly reminder that your invoice ${invoiceNumber} with amount ${amountStr} is due on ${dueDateStr}.
Please ensure that your payment is made by the due date to avoid any late fees.
Thank you for your attention.`;
MailApp.sendEmail(emailAddress, subject, message);
console.log(`Email sent to ${emailAddress} for invoice ${invoiceNumber}`);});}
➤ Now, to create a trigger, hit the Triggers option from the left corner.
➤ Choose Add Trigger.
➤ Now, select sendInvoiceReminders in the Choose which function to run from the drop-down list.
➤ Select Head from the Choose which deployment should run drop-down list.
➤ Similarly, select Time-driven from Select event source, Month timer from Select type of time based trigger, 1st from Select day of month, 1pm to 2pm from Select time of day and click Save.
As a result, your script will now automatically run according to the trigger schedule, sending personalized invoice reminders from your Google Sheet. An example of the email sent is shown in the image below.
Sending Emails Based on Condition
Sometimes, you don’t want to email everyone, only those who meet specific conditions, like overdue payments or low grades. By setting up conditional logic in your Google Sheets, you can control exactly who receives an email. Below, we’ll learn how to send emails when certain conditions are met, like when a specific cell value changes, a date is reached, or a checkbox is ticked.
Criteria 1: Changing Cell Value
In this part, we will send emails based on our cell value. We’ll modify the previous script to send two different types of emails: invoice reminders for outstanding payments and payment confirmations for invoices marked as paid.
Our dataset will remain similar to the previous example, containing: Business Name, Email, Invoice Number, Due Date, and Invoice Amount. The key difference is that an “Invoice Amount” of $0 will indicate a paid invoice, triggering a payment confirmation email, while any other amount will trigger an invoice reminder.
➤ Open the Apps Script window just like the previous method, put the code below and click Run.
function sendInvoice() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const lastRow = sheet.getLastRow();
const dataRange = sheet.getRange(2, 1, lastRow - 1, 5);
const data = dataRange.getValues();
data.forEach((row, index) => {
const businessName = row[0];
const emailAddress = row[1];
const invoiceNumber = row[2];
const dueDate = new Date(row[3]);
const invoiceAmount = row[4];
if (!emailAddress || !emailAddress.toString().includes("@")) return;
const dueDateStr = Utilities.formatDate(dueDate, Session.getScriptTimeZone(), "EEE dd-MMM-yyyy");
const amountStr = (typeof invoiceAmount === "number") ? `$${invoiceAmount.toFixed(2)}` : invoiceAmount;
let subject = "";
let message = "";
if (invoiceAmount === 0) {
subject = `Invoice Cleared: ${invoiceNumber}`;
message = `Dear ${businessName},
We are pleased to inform you that your invoice ${invoiceNumber} has been marked as paid in full.
Thank you for your prompt payment!
Best regards,
Your Company`;
} else {
subject = `Invoice Reminder: ${invoiceNumber}`;
message = `Dear ${businessName},
This is a friendly reminder that your invoice ${invoiceNumber} with amount ${amountStr} is due on ${dueDateStr}.
Please ensure that your payment is made by the due date to avoid any late fees.
Thank you for your attention.`;}
MailApp.sendEmail(emailAddress, subject, message);
console.log(`Email sent to ${emailAddress} for invoice ${invoiceNumber}`);});}
Finally, an email is sent when an invoice has been marked as paid (Invoice Amount is $0 in the sheet).
Others with remaining invoice amounts will get an email reminding them of the due date and amount.
Criteria 2: Date
Here, we will send emails from Google Sheets according to the date. From the image below, you can check the Due Date and Today’s date. In this script, we will identify invoices that are more than 30 days past their due date from today’s date. Then, automatically calculate and notify the client about the applied late fee.
➤ Open the Apps Script window just like the previous method, put the code below, and click Run.
function sendLateFeeNotices() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const lastRow = sheet.getLastRow();
const data = sheet.getRange(2, 1, lastRow - 1, 5).getValues();
const today = new Date();
today.setHours(0, 0, 0, 0);
data.forEach(row => {
const businessName = row[0];
const emailAddress = row[1];
const invoiceNumber = row[2];
const dueDate = new Date(row[3]);
const invoiceAmount = row[4];
if (!emailAddress || !emailAddress.toString().includes("@")) return;
if (typeof invoiceAmount !== "number" || invoiceAmount <= 0) return;
dueDate.setHours(0, 0, 0, 0);
const diffInDays = Math.floor((today - dueDate) / (1000 * 60 * 60 * 24));
if (diffInDays > 30) {
const lateFee = invoiceAmount * 0.2;
const totalAmount = invoiceAmount + lateFee;
const dueDateStr = Utilities.formatDate(dueDate, Session.getScriptTimeZone(), "EEE dd-MMM-yyyy");
const subject = `Late Fee Notice: Invoice ${invoiceNumber} Overdue`;
const message = `Dear ${businessName},
Your invoice ${invoiceNumber}, originally due on ${dueDateStr}, is now more than 30 days overdue.
A 20% late fee has been applied as per our policy.
Original Amount: $${invoiceAmount.toFixed(2)}
Late Fee (20%): $${lateFee.toFixed(2)}
Total Due: $${totalAmount.toFixed(2)}
Please settle this amount as soon as possible to avoid further charges.
Thank you for your prompt attention.`;
MailApp.sendEmail(emailAddress, subject, message);
console.log(`Late fee notice sent to ${emailAddress} for invoice ${invoiceNumber}`);}});}
As a result, your script will now automatically identify invoices overdue by more than 30 days and send personalized late fee notices, including a 20% penalty calculation.
Criteria 3: Checkmark
If you want more control over which invoice reminders are sent, you can use checkboxes in your Google Sheet. Just checkmark the rows you want, and the script will only send emails for those entries. This is great for sending reminders one by one or following up with specific clients.
Here, the dataset will remain the same as before, but with one extra column for checkboxes to mark which rows should get an email.
➤ Open the Apps Script window just like the previous method, put the code below, and click Run.
function sendCheckedInvoices() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const lastRow = sheet.getLastRow();
const data = sheet.getRange(2, 1, lastRow - 1, 6).getValues();
data.forEach((row, i) => {
const client = row[0];
const email = row[1];
const invoiceId = row[2];
const dueDate = row[3];
const amount = row[4];
const isChecked = row[5];
if (isChecked === true && email && email.toString().includes("@")) {
const dueDateStr = Utilities.formatDate(new Date(dueDate), Session.getScriptTimeZone(), "dd-MMM-yyyy");
const amountStr = (typeof amount === "number") ? `$${amount.toFixed(2)}` : amount;
const subject = `Reminder: Invoice ${invoiceId} Due`;
const body = `Hello ${client},
This is a reminder that your invoice ${invoiceId} with amount ${amountStr} is due on ${dueDateStr}.
Please make the payment before the due date.
Thank you.`;
MailApp.sendEmail(email, subject, body);
console.log(`Email sent to ${email} for invoice ${invoiceId}`);}});}
Finally, the script sends personalized reminder emails only to clients with checked boxes in the Google Sheet.
Sending Emails When Google Sheets is Updated
If you want, you can also send emails when your Google Sheets are updated. By using Google Apps Script with an “on edit” trigger, you can automatically send personalized email notifications whenever any edit is made. This way, only the selected employees with an update will receive the email.
Imagine we have a dataset containing Employee Name, Email, Team, and Task. Now, when we update the task column with a new entry, an email notification will be automatically sent to the assigned employee informing them of their new task.
➤ Open the Apps Script window just like the previous method, put the code below, and click Save.
function Taskassign(e) {
const cell = e.range;
const sheet = cell.getSheet();
if (cell.getColumn() === 4 && e.value) {
const row = cell.getRow();
const rowData = sheet.getRange(row, 1, 1, 4).getValues()[0];
const employeeName = rowData[0];
const email = rowData[1];
const team = rowData[2];
const task = rowData[3];
if (!email || !email.toString().includes("@")) return;
const subject = `📝 New Task Assigned to You`;
const body = `Hello ${employeeName},
You have been assigned a new task in Team ${team}.
Task: ${task}
Please start working on it as soon as possible.
Regards,
Team Lead`;
MailApp.sendEmail(email, subject, body);
console.log(`Task assigned to ${employeeName} at ${email}: ${task}`);}}
➤ Now, to create a trigger, hit the Triggers option from the left corner.
➤ Choose Add Trigger.
➤ Now, configure the trigger settings as follows:
- Select Taskassign in the Choose which function to run from the drop-down list.
- Select From spreadsheet from Select event source.
- Select On edit from Select event type.
- For Failure notification settings, you can choose Notify me immediately to be alerted of any issues.
- Click Save.
➤ Hence, let’s add a task for Sophia in the Task column.
As a result, Sophia will receive an automatic, personalized email as a task is added to her in the Google Sheet.
Mail Merge in Google Sheets
Mail merge in Google Sheets lets you send personalized emails to many recipients quickly by combining your spreadsheet data with email templates. This method allows you to use a single email draft as a template, automatically populating the recipient with specific data from your sheets.
Suppose we have a dataset containing Name, Email, Course, Grade, and an additional column, Email Sent, to track sent emails. Now, we will create a script that sends personalized emails to each recipient based on their data and updates the “Email Sent” column to keep track of which emails have been sent.
➤ Navigate to Gmail and click the Google Apps icon in the top right corner of your browser.
➤ Then select Gmail.
➤ Once in Gmail, click on the Compose button to start writing a new email.
➤ In the new message window, put Your Result for {{Course}} in the subject section.
➤ Inside the message body, write down the message following the image.
➤ Now, close the message by clicking the cross(╳) icon.
➤ Thus, the message will be saved as a draft.
➤ Open the Apps Script window just like the previous method, put the code below, and click Run.
const RECIPIENT_COL = "Email";
const EMAIL_SENT_COL = "Email Sent";
function onOpen() {
SpreadsheetApp.getUi().createMenu('Mail Merge')
.addItem('Send Emails', 'sendEmails')
.addToUi();}
function sendEmails(subjectLine, sheet = SpreadsheetApp.getActiveSheet()) {
if (!subjectLine) {
subjectLine = Browser.inputBox("Mail Merge", "Enter the subject of the Gmail draft to use:", Browser.Buttons.OK_CANCEL);
if (subjectLine === "cancel" || subjectLine === "") return;}
const emailTemplate = getGmailTemplateFromDrafts_(subjectLine);
const data = sheet.getDataRange().getDisplayValues();
const headers = data.shift();
const emailSentColIdx = headers.indexOf(EMAIL_SENT_COL);
const obj = data.map(r => headers.reduce((o, k, i) => (o[k] = r[i] || '', o), {}));
const out = [];
obj.forEach((row, rowIdx) => {
if (row[EMAIL_SENT_COL] === '') {
try {
const msgObj = fillInTemplateFromObject_(emailTemplate.message, row);
GmailApp.sendEmail(row[RECIPIENT_COL], msgObj.subject, msgObj.text, {
htmlBody: msgObj.html,
attachments: emailTemplate.attachments,
inlineImages: emailTemplate.inlineImages});
out.push([new Date()]);
} catch (e) {
out.push([e.message]);}
} else {
out.push([row[EMAIL_SENT_COL]]);}});
sheet.getRange(2, emailSentColIdx + 1, out.length).setValues(out);
function getGmailTemplateFromDrafts_(subject_line) {
const draft = GmailApp.getDrafts().filter(d => d.getMessage().getSubject() === subject_line)[0];
const msg = draft.getMessage();
const inlineImgs = msg.getAttachments({ includeInlineImages: true, includeAttachments: false });
const attachments = msg.getAttachments({ includeInlineImages: false });
const htmlBody = msg.getBody();
const imgObj = inlineImgs.reduce((o, i) => (o[i.getName()] = i, o), {});
const matches = [...htmlBody.matchAll(/<img.*?src="cid:(.*?)".*?alt="(.*?)"[^\>]+>/g)];
const inlineImagesObj = {};
matches.forEach(m => inlineImagesObj[m[1]] = imgObj[m[2]]);
return { message: { subject: subject_line, text: msg.getPlainBody(), html: htmlBody }, attachments, inlineImages: inlineImagesObj };}
function fillInTemplateFromObject_(template, data) {
let str = JSON.stringify(template);
str = str.replace(/{{[^{}]+}}/g, key => escapeData_(data[key.replace(/[{}]+/g, "")] || ""));
return JSON.parse(str);}
function escapeData_(str) {
return str
.replace(/[\\]/g, '\\\\')
.replace(/[\"]/g, '\\\"')
.replace(/[\/]/g, '\\/')
.replace(/[\b]/g, '\\b')
.replace(/[\f]/g, '\\f')
.replace(/[\n]/g, '\\n')
.replace(/[\r]/g, '\\r')
.replace(/[\t]/g, '\\t');}}
After running the script, you will now see a new custom menu titled Mail Merge in your spreadsheet’s menu bar.
➤ Choose Send Emails from the Mail Merge option.
➤ A new window will pop up named Mail Merge.
➤ Enter the exact subject line of your Gmail draft template Your Result for {{Course}}, and hit OK.
As a result, the script will read your sheet, fill in the email draft with row data, and send personalized emails to each person. The image below is an example of a personalized email sent, where {{Course}} has been replaced with “Mathematics” and {{Name}} with “Liam”, reflecting the data from the first row of your Google Sheet.
Furthermore, upon successful email delivery, the script will automatically update the Email Sent column in your Google Sheet for each processed row with the current date.
Frequently Asked Questions
How do I stop duplicate emails from being sent?
You can add a column like “Email Sent” to your sheet and update it after each email is sent. The script can then skip rows where this column is marked.
Is it possible to attach files to the emails sent from Google Sheets?
Yes, you can attach files stored in Google Drive using Apps Script. Just include the file ID or path in your sheet and reference it in the script.
How many emails can I send per day from Google Sheets?
Gmail has daily sending limits. For regular Gmail accounts, it’s about 100 recipients/day, and for Google Workspace accounts, up to 1,500. Going beyond this will pause your script until the limit resets.
Concluding Words
With the help of Apps Script, Google Sheets becomes a powerful platform for automating email communication. Whether you’re automating emails, sending based on conditions, or using mail merge, these methods can make your work easier. If you face any issues or have questions, feel free to let us know in the comment section below.