Welcome to the ultimate guide for everyone who’s ever wondered how to send emails from Google Sheets. We cover everything you need to know, catering to both tech experts and those willing to get their gray cells running with some JavaScript and Google Spreadsheet combo.
You’ll learn how to set up and automate email sending using a few lines, okay sometimes a bit more than a few lines, of Google Apps Script, bypassing the need for an add-on, bulky third-party tools, or convoluted code.
Get ready to explore a wide range of use cases and scripting techniques as we marry Google Sheets and your email inbox.
Why send emails from Google Sheets in the first place? An exemplary case study.
We’ll put you in the shoes of an account manager who needs to automatically send emails from google sheets. Now, let’s be more specific about the exemplary case.
Typically, there’s a list of potential clients the manager needs to invoice every month via email notifications. And let’s say the business uses Xero, the accounting automation app.
Instead of manually pulling email addresses to remind each client about an upcoming invoice, you can use Google Sheets to automate the process. Add each contact’s name and email address to a spreadsheet, and use it to keep track of the progress.
To make things even easier, you can use a tool like Coupler.io to automate the data import from Xero. With that, you can have all the contact information in one place and easily manage email communication. If you want the details, check How to Integrate Xero and Google Sheets on Coupler’s blog.
Also, you can customize the emails to make them more personal and engaging, increasing your chances of getting the invoice paid in a timely manner.
So if you’re looking for a way to streamline your efforts and improve invoicing, consider using Google Sheets and automation tools like Coupler.io to manage your contacts and send out personalized emails.
But be sure to note the limitations of your Google account mentioned later in the article. On production, sending emails from Google Sheets is best for micro-businesses, and maybe freelancers, who only have a few clients. If you need to send at a volume, you need to use a proper MTA.
How to send emails from Google Sheets?
Before we start, let’s assume that you already pulled the necessary data from Xero using Coupler, then imported the data into a spreadsheet.
To keep things nice and simple, our exemplary spreadsheet contains only six columns, listing the recipients’ Business Name, Email Address, Invoice Number, Invoice Amount, Due Date, and Subject.
Yes, our sheet has only one company client, but that’s for testing purposes. You don’t want to send an entire batch of emails only to realize something’s been faulty from the get-go.
Additionally, we designed the script to loop through the entire active sheet. It keeps checking the data and sends emails to all businesses and addresses in the sheet. But more on that later.
Now, let’s see how to get it to work.
Click Extensions in the toolbar, then Apps Script – the tab to write your script opens automatically.
Below, you’ll find the script to create the email message and send it to the recipients in your sheet. Of course, the script is custom to our exemplary sheet.
function sendEmails() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const dataRange = sheet.getRange("A2:F");
const data = dataRange.getValues();
for (let i = 0; i < data.length; i++) {
const row = data[i];
const businessName = row[0];
const emailAddress = row[1];
const invoiceNumber = row[2];
const invoiceAmount = row[3];
const dueDate = row[4];
const subject = row[5];
console.log(`Row ${i + 2}: ${businessName}, ${emailAddress}, ${invoiceNumber}, ${invoiceAmount}, ${dueDate}, ${subject}`); // Logging the data
if (!emailAddress) {
console.log(`Row ${i + 2}: Email address is missing. Skipping this row.`);
continue;
}
const message = createEmailMessage(businessName, invoiceNumber, invoiceAmount, dueDate);
function createEmailMessage(businessName, invoiceNumber, invoiceAmount, dueDate) {
const message = `Dear ${businessName},
This is a friendly reminder that your invoice number ${invoiceNumber} with an amount of $${invoiceAmount} is due on ${dueDate}.
Please make sure to complete your payment on time to avoid any late fees.
Thank you for your prompt attention to this matter.`;
return message;
}
try {
MailApp.sendEmail(emailAddress, subject, message);
console.log(`Row ${i + 2}: Email sent to ${emailAddress}`);
} catch (error) {
console.log(`Row ${i + 2}: Error sending email to ${emailAddress}. Error message: ${error.message}`);
}
}
}Let’s break down the code
The script consists of two main functions: createEmailMessage and sendEmails, and there’s a loop and log to iterate through the entire sheet. Here are the details.
1. Fetch Spreadsheet Data
The script first communicates with Google’s Spreadsheet service to get the currently active sheet.
function sendEmails() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const dataRange = sheet.getRange("A2:F");
const data = dataRange.getValues();Then, it defines a range of cells from which to pull data (in this case, it’s fetching data from columns A through F, starting from the second row). The .getValues() method is then used to convert the range into a 2-dimensional array of values.
2. Iterate Over Rows
The function then enters a loop, iterating over each row of data.
for (let i = 0; i < data.length; i++) {
const row = data[i];
const businessName = row[0];
const emailAddress = row[1];
const invoiceNumber = row[2];
const invoiceAmount = row[3];
const dueDate = row[4];
const subject = row[5];Each row is an array, and specific indices of this array are assigned to constants representing various pieces of data (business name, email address, invoice number, etc.).
3. Skip Invalid Rows
The script checks if the email address is missing for any row. If it is, it logs a message and skips the current iteration. Also, the script logs valid data.
console.log(`Row ${i + 2}: ${businessName}, ${emailAddress}, ${invoiceNumber}, ${invoiceAmount}, ${dueDate}, ${subject}`); // Logging the data
if (!emailAddress) {
console.log(`Row ${i + 2}: Email address is missing. Skipping this row.`);
continue;
}4. Generate Email Message
If the email address is valid, the script then constructs an email message for that row using the createEmailMessage function.
const message = createEmailMessage(businessName, invoiceNumber, invoiceAmount, dueDate);
function createEmailMessage(businessName, invoiceNumber, invoiceAmount, dueDate) {
const message = `Dear ${businessName},
This is a friendly reminder that your invoice number ${invoiceNumber} with an amount of $${invoiceAmount} is due on ${dueDate}.
Please make sure to complete your payment on time to avoid any late fees.
Thank you for your prompt attention to this matter.`;
return message;
}This function takes in the business name, invoice number, invoice amount, and due date to create a formatted string (message) which will serve as the body of the email.
5. Send Emails
Finally, it attempts to send the email using Google’s MailApp service.
try {
MailApp.sendEmail(emailAddress, subject, message);
console.log(`Row ${i + 2}: Email sent to ${emailAddress}`);
} catch (error) {
console.log(`Row ${i + 2}: Error sending email to ${emailAddress}. Error message: ${error.message}`);
}
}
}The script logs a message indicating whether the email was successfully sent or if there was an error. The email recipient is the email address fetched from the row data, the subject is also derived from the row data, and the message body is the string returned by createEmailMessage.
The createEmailMessage function, which is nested inside sendEmails, is a helper function used to create the email message. It uses template literals to insert the provided arguments (business name, invoice number, invoice amount, due date) into a formatted string, which it then returns. This message serves as the body of the email.
Automating the process
So far so good, but the script above will only run manually if we don’t set any triggers. Luckily, Google made the triggered automation simple to configure. Here are the steps.
- In the Apps Script editor, click the Triggers icon on the left sidebar (it looks like a small clock).
- Click the create new trigger link or the Add Trigger button in the bottom right corner of the Triggers page.
- In the Choose which function to run dropdown menu, select the sendEmails function.
- In the Select event source dropdown menu, choose Time-driven.
- In the Select type of time-based trigger dropdown menu, choose the frequency you want (e.g., Day timer for daily emails, Week timer for weekly emails, etc.).
- If applicable, choose the desired time range or day of the week.
- Click Save to create the trigger.
After saving, click the Deploy button in the top right corner, and follow the instructions to label and define the deployment. You’ll need to give the automation permissions to run, and the same goes for the script itself.
As for the given trigger, since our example is about sending upcoming invoice reminders, we assume these are monthly, and it’s appropriate to send them every 1st of the month between 1 pm and 2 pm. But of course, you can change the frequency and timing based on your needs.
How to send an email with an attachment
To show you how to send emails with attachments, we’ll reuse the exemplary sheet with invoice reminders and refactor the snippet to include attachments. Now, we’re starting with the sendEmailReminders function, and the email template comes later in the code.
We should highlight that the snippet below pulls email attachments from a Google Drive folder, where it’s critical to include the folder ID and follow the given naming convention. More on that under the Let’s break down the code section.
function sendEmailReminders() {
// Get the active sheet
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// Get the data range (excluding the header row)
var dataRange = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn());
// Get the values from the data range
var data = dataRange.getValues();
// Loop through the rows of data
for (var i = 0; i < data.length; i++) {
var row = data[i];
// Get the values from each column
var businessName = row[0];
var email = row[1];
var invoiceNumber = row[2];
var invoiceAmount = row[3];
var dueDate = row[4];
var subject = row[5];
// Email body
var emailBody = "Dear " + businessName + ",\n\n" +
"This is a reminder that Invoice #" + invoiceNumber + " for $" + invoiceAmount + " is due on " + dueDate + ".\n" +
"Please find the attached invoice.\n\n" +
"Thank you for your prompt attention to this matter.";
// Attach the corresponding invoice - You need to provide the correct folder ID where your invoices are stored
var invoiceFolderId = 'YOUR_FOLDER_ID_HERE';
var invoiceFolder = DriveApp.getFolderById(invoiceFolderId);
var invoiceFiles = invoiceFolder.getFilesByName(invoiceNumber + '.pdf'); // Assuming invoice files are in PDF format
var invoiceFile;
if (invoiceFiles.hasNext()) {
invoiceFile = invoiceFiles.next();
} else {
// If the invoice file is not found, you can skip this row or log an error
console.error("Invoice file not found for invoice number: " + invoiceNumber);
continue;
}
// Send the email with the attachment
MailApp.sendEmail({
to: email,
subject: subject,
body: emailBody,
attachments: [invoiceFile]
});
}
}Let’s break down the code
1. Define the sendEmailReminders function:
function sendEmailReminders() {The line declares a function named sendEmailReminders, which will contain the code to send email reminders with attachments.
2. Get the active sheet:
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();The line retrieves the active sheet from the currently open Google Sheets document.
3. Get the data range (excluding the header row):
var dataRange = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn());This line gets the range of data in the sheet, excluding the header row. It starts from the second row (row 2) and the first column (column 1) and extends to the last row and the last column of the sheet.
Side Note: When you create your custom sheet you need to modify the dataRange to match your sheet.
4. Get the values from the data range:
var data = dataRange.getValues();This line retrieves the values (contents) from the range of cells defined in the previous step.
5. Loop through the rows of data:
for (var i = 0; i < data.length; i++) {
var row = data[i];This for loop iterates through each row in the data array. The variable row represents the current row of data in each iteration.
6. Get the values from each column:
var businessName = row[0];
var email = row[1];
var invoiceNumber = row[2];
var invoiceAmount = row[3];
var dueDate = row[4];
var subject = row[5];The lines above extract the values from each column in the current row of data. Each value is later assigned to a variable for easy reference in the script. The given values are custom to our exemplary sheet.
7. Create the email body:
var emailBody = "Dear " + businessName + ",\n\n" +
"This is a reminder that Invoice #" + invoiceNumber + " for $" + invoiceAmount + " is due on " + dueDate + ".\n" +
"Please find the attached invoice.\n\n" +
"Thank you for your prompt attention to this matter.";The code constructs the email body using the values extracted from the sheet. The email body is a string that includes the business name, invoice number, invoice amount, and due date.
8. Get the corresponding invoice file:
var invoiceFolderId = 'YOUR_FOLDER_ID_HERE';
var invoiceFolder = DriveApp.getFolderById(invoiceFolderId);
var invoiceFiles = invoiceFolder.getFilesByName(invoiceNumber + '.pdf');
var invoiceFile;
if (invoiceFiles.hasNext()) {
invoiceFile = invoiceFiles.next();
} else {
console.error("Invoice file not found for invoice number: " + invoiceNumber);
continue;
}These lines retrieve the invoice file associated with the current row.
First, the invoice file folder is accessed using its folder ID. Make sure to replace YOUR_FOLDER_ID_HERE with the actual folder ID.
Note that the naming convention in our example is (invoiceNumber + '.pdf'), and the actual invoice is named ‘123456.pdf’. If your attachment files are named differently, you’ll need to update the formatting.
Next, the script searches for a file with the same name as the invoice number (assuming the file is in PDF format).
If the file is found, it is assigned to the invoiceFile variable; otherwise, the script logs an error message and moves on to the next row.
9. Send the email with the attachment:
MailApp.sendEmail({
to: email,
subject: subject,
body: emailBody,
attachments: [invoiceFile]
});This line sends the body and the email attachments, and here are the results you should get. Also, all the automation steps apply as previously described, and it makes sense to keep the monthly trigger since this is about sending invoice reminders.
Send an HTML email with GSheets
To send an HTML email, you can make some, pretty simple, modifications to the script previously described. Here it is:
function sendEmailReminders() {
// Get the active sheet
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// Get the data range (excluding the header row)
var dataRange = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn());
// Get the values from the data range
var data = dataRange.getValues();
// Loop through the rows of data
for (var i = 0; i < data.length; i++) {
var row = data[i];
// Get the values from each column
var businessName = row[0];
var email = row[1];
var invoiceNumber = row[2];
var invoiceAmount = row[3];
var dueDate = row[4];
var subject = row[5];
// HTML email body
var emailBodyHtml = "<p>Dear " + businessName + ",</p>" +
"<p>This is a reminder that Invoice #" + invoiceNumber + " for $" + invoiceAmount + " is due on " + dueDate + ".</p>" +
"<p>Please find the attached invoice.</p>" +
"<p>Thank you for your prompt attention to this matter.</p>";
// Attach the corresponding invoice - You need to provide the correct folder ID where your invoices are stored
var invoiceFolderId = 'YOUR_FOLDER_ID_HERE';
var invoiceFolder = DriveApp.getFolderById(invoiceFolderId);
var invoiceFiles = invoiceFolder.getFilesByName(invoiceNumber + '.pdf'); // Assuming invoice files are in PDF format
var invoiceFile;
if (invoiceFiles.hasNext()) {
invoiceFile = invoiceFiles.next();
} else {
// If the invoice file is not found, you can skip this row or log an error
console.error("Invoice file not found for invoice number: " + invoiceNumber);
continue;
}
// Send the email with the attachment
MailApp.sendEmail({
to: email,
subject: subject,
htmlBody: emailBodyHtml,
attachments: [invoiceFile]
});
}
}In this version, the plain-text emailBody is replaced with an emailBodyHtml variable that contains HTML-formatted content.
When calling the MailApp.sendEmail() function, the body property is also replaced with the htmlBody property, which will send the email as an HTML email.
Side Notes:
We’re using var instead of const in the script for the sake of convenience. Either option would work, though definitely, const is more readable. Anyway, feel free to reformat the script and replace the concatenation operator with ${expression} placeholders at string interpolation.
Also, none of the scripts include bcc functions as these weren’t necessary for our examples. But of course, it’s also possible to add, and even automate, bcc with these types of scripts.
Finally, you can spice things up by adding custom CSS styles, see the updated var emailBodyHtml = function below.
// HTML email body with CSS styles
var emailBodyHtml = "<html><head><style>" +
"body {font-family: Arial, sans-serif;}" +
"p {font-size: 14px;}" +
".invoice-info {font-weight: bold; color: #4a4a4a;}" +
"</style></head><body>" +
"<p>Dear <span class='invoice-info'>" + businessName + "</span>,</p>" +
"<p>This is a reminder that Invoice #<span class='invoice-info'>" + invoiceNumber + "</span> for $<span class='invoice-info'>" + invoiceAmount + "</span> is due on <span class='invoice-info'>" + dueDate + "</span>.</p>" +
"<p>Please find the attached invoice.</p>" +
"<p>Thank you for your prompt attention to this matter.</p>" +
"</body></html>";Pro Tips:
- Go easy with the
<style>, some mailbox providers might not support and render heavily-stylized emails properly, or at all. - Use Mailtrap Email Testing to see how much mailbox providers support your HTML emails.
- To stress again, If you need to send at a volume, it’s always best to use a proper MTA instead of the
sendEmailfunction. - On that note, mind your Gmail account limitations. These are different for Google Workspace and/or your private account.
How to send trigger emails from Google Sheets?
When a cell reaches a certain value
We’ll show you how to send an automatic email based on a cell value. In our example, the invoice will be sent when the Invoice Amount cell reaches ‘0’ indicating a churned customer.
The given cell value triggers a custom churn email to immediately reach out to the customer. And again, the automation steps stay the same, you can still use the monthly trigger for these emails.
Keep in mind that the script is the same one we used for sending HTML emails and attachments. But it has another layer of functionality to introduce the described conditional logic. Here’s the code.
function sendEmailReminders() {
// Get the active sheet
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// Get the data range (excluding the header row)
var dataRange = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn());
// Get the values from the data range
var data = dataRange.getValues();
// Loop through the rows of data
for (var i = 0; i < data.length; i++) {
var row = data[i];
// Get the values from each column
var businessName = row[0];
var email = row[1];
var invoiceNumber = row[2];
var invoiceAmount = row[3];
var dueDate = row[4];
var subject = row[5];
// Determine the email body based on invoice amount
var emailBodyHtml;
if (invoiceAmount == 0) {
// Churn customer email
subject = "Churn customer notification";
emailBodyHtml = "<html><head><style>" +
"body {font-family: Arial, sans-serif;}" +
"p {font-size: 14px;}" +
".invoice-info {font-weight: bold; color: #4a4a4a;}" +
"</style></head><body>" +
"<p>Dear " + businessName + ",</p>" +
"<p>We've noticed that your invoice amount is $0. We're sorry to see you go and would appreciate any feedback on why you've decided to discontinue using our services.</p>" +
"<p>If there's anything we can do to improve our services or win back your business, please let us know.</p>" +
"<p>Thank you for your past business.</p>" +
"</body></html>";
} else {
// Regular invoice email
emailBodyHtml = "<html><head><style>" +
"body {font-family: Arial, sans-serif;}" +
"p {font-size: 14px;}" +
".invoice-info {font-weight: bold; color: #4a4a4a;}" +
"</style></head><body>" +
"<p>Dear <span class='invoice-info'>" + businessName + "</span>,</p>" +
"<p>This is a reminder that Invoice #<span class='invoice-info'>" + invoiceNumber + "</span> for $<span class='invoice-info'>" + invoiceAmount + "</span> is due on <span class='invoice-info'>" + dueDate + "</span>.</p>" +
"<p>Please find the attached invoice.</p>" +
"<p>Thank you for your prompt attention to this matter.</p>" +
"</body></html>";
}
// Attach the corresponding invoice - You need to provide the correct folder ID where your invoices are stored
var invoiceFolderId = 'YOUR_FOLDER_ID_HERE';
var invoiceFolder = DriveApp.getFolderById(invoiceFolderId);
var invoiceFiles = invoiceFolder.getFilesByName(invoiceNumber + '.pdf'); // Assuming invoice files are in PDF format
var invoiceFile;
if (invoiceFiles.hasNext()) {
invoiceFile = invoiceFiles.next();
} else {
// If the invoice file is not found, you can skip this row or log an error
console.error("Invoice file not found for invoice number: " + invoiceNumber);
continue;
}
// Send the email with the attachment
MailApp.sendEmail({
to: email,
subject: subject,
htmlBody: emailBodyHtml,
attachments: [invoiceFile]
});
}
}Down to the code break
Note that we’re only focusing on what’s in the emailBodyHtml function to avoid repeating what was already described.
The updated part of the code determines the content of the email body based on the invoice amount. If the invoice amount is ‘0’, it’s assumed to be a churn customer, and a different email template is used. Here’s a breakdown of the code:
var emailBodyHtml; It declares a variable named emailBodyHtml and stores the content of the email body. And it’s initially left undefined.
if (invoiceAmount == 0) { ... } The if statement checks if the invoice amount is 0. If it is, the code inside the curly braces (`{}`) will be executed. This block contains the churn customer email template.
subject = "Churn customer notification"; The line sets the email subject to "Churn customer notification" for churn customers.
emailBodyHtml = "<html><head><style>" + ... The line sets the value of the emailBodyHtml variable to the churn customer email template. The template is an HTML string with CSS styles defined in the <head> section. The email body contains placeholders for the business name and a message to the customer.
} else { ... } The else block is executed when the invoice amount is not 0. It sets the emailBodyHtml variable to the regular invoice email template. The email template is an HTML string with CSS styles defined in the <head> section. The email body contains placeholders for the business name, invoice number, invoice amount, and due date.
By using this conditional logic, the script sends a different email body based on whether the invoice amount is 0 (churn customer) or non-zero (regular invoice).
When the cell value changes
Now, we’ll take the same script used above and extend it to cover another use case – a client upgrades, and the account manager needs to send a “Thank You” email with an invoice.
To explain, the idea is that every month the account manager has a new Sheet (Sheet 2 in our example) with the clients’ credentials.
The code will now compare the two sheets, and send an automatic “Thank you” email, instead of the regular invoice email, to clients who upgraded (their Invoice Amount is bigger compared to the previous one).
Also, there’s no need to touch the churn email, as there’s a chance that someone decided to stop using our imaginary service. Here’s the script.
function sendEmailReminders() {
var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
var dataRange1 = sheet1.getRange(2, 1, sheet1.getLastRow() - 1, sheet1.getLastColumn());
var dataRange2 = sheet2.getRange(2, 1, sheet2.getLastRow() - 1, sheet2.getLastColumn());
var data1 = dataRange1.getValues();
var data2 = dataRange2.getValues();
for (var i = 0; i < data1.length; i++) {
var row1 = data1[i];
var row2 = data2[i];
var businessName = row1[0];
var email = row1[1];
var invoiceNumber = row1[2];
var invoiceAmount1 = parseFloat(row1[3].toString().replace('$', '').replace(',', '.'));
var invoiceAmount2 = parseFloat(row2[3].toString().replace('$', '').replace(',', '.'));
if (isNaN(invoiceAmount1) || isNaN(invoiceAmount2)) {
console.error("Invalid invoice amounts for business: " + businessName + ". invoiceAmount1: " + invoiceAmount1 + ", invoiceAmount2: " + invoiceAmount2);
continue;
}
var dueDate = row1[4];
var subject;
var emailBodyHtml;
if (invoiceAmount1 < invoiceAmount2) {
console.log("Sending 'Thank You for Upgrading' email to " + email + ". invoiceAmount1: " + invoiceAmount1 + ", invoiceAmount2: " + invoiceAmount2);
subject = "Thank You for Upgrading";
emailBodyHtml = "<html><body>" +
"<p>Dear " + businessName + ",</p>" +
"<p>Thank you for upgrading! We appreciate your business and look forward to serving you.</p>" +
"<p>Best regards,</p>" +
"<p>Your Company</p>" +
"</body></html>";
MailApp.sendEmail({
to: email,
subject: subject,
htmlBody: emailBodyHtml
});
} else if (invoiceAmount1 == 0) {
console.log("Sending 'Churn customer notification' email to " + email + ". invoiceAmount1: " + invoiceAmount1);
subject = "Churn customer notification";
emailBodyHtml = "<html><head><style>" +
"body {font-family: Arial, sans-serif;}" +
"p {font-size: 14px;}" +
".invoice-info {font-weight: bold; color: #4a4a4a;}" +
"</style></head><body>" +
"<p>Dear " + businessName + ",</p>" +
"<p>We've noticed that your invoice amount is $0. We're sorry to see you go and would appreciate any feedback on why you've decided to discontinue using our services.</p>" +
"<p>If there's anything we can do to improve our services or win back your business, please let us know.</p>" +
"<p>Thank you for your past business.</p>" +
"</body></html>";
MailApp.sendEmail({
to: email,
subject: subject,
htmlBody: emailBodyHtml
});
} else {
console.log("Sending 'Invoice Reminder' email to " + email + ". invoiceAmount1: " + invoiceAmount1 + ", invoiceAmount2: " + invoiceAmount2);
subject = "Invoice Reminder";
emailBodyHtml = "<html><head><style>" +
"body {font-family: Arial, sans-serif;}" +
"p {font-size: 14px;}" +
".invoice-info {font-weight: bold; color: #4a4a4a;}" +
"</style></head><body>"
"<p>Dear <span class='invoice-info'>" + businessName + "</span>,</p>" +
"<p>This is a reminder that Invoice #<span class='invoice-info'>" + invoiceNumber + "</span> for $<span class='invoice-info'>" + invoiceAmount1 + "</span> is due on <span class='invoice-info'>" + dueDate + "</span>.</p>" +
"<p>Please find the attached invoice.</p>" +
"<p>Thank you for your prompt attention to this matter.</p>" +
"</body></html>";
// Attach the corresponding invoice - You need to provide the correct folder ID where your invoices are stored
var invoiceFolderId = 'YOUR_FOLDER_ID_HERE';
var invoiceFolder = DriveApp.getFolderById(invoiceFolderId);
var invoiceFiles = invoiceFolder.getFilesByName(invoiceNumber + '.pdf'); // Assuming invoice files are in PDF format
var invoiceFile;
if (invoiceFiles.hasNext()) {
invoiceFile = invoiceFiles.next();
} else {
// If the invoice file is not found, you can skip this row or log an error
console.error("Invoice file not found for invoice number: " + invoiceNumber);
continue;
}
MailApp.sendEmail({
to: email,
subject: subject,
htmlBody: emailBodyHtml,
attachments: [invoiceFile]
});
}
}
}Down to the code break
1. The script starts by getting references to both Sheet1 and Sheet2.
var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");2. It then retrieves the data from both sheets, starting from the second row to exclude headers.
var dataRange1 = sheet1.getRange(2, 1, sheet1.getLastRow() - 1, sheet1.getLastColumn());
var dataRange2 = sheet2.getRange(2, 1, sheet2.getLastRow() - 1, sheet2.getLastColumn());
var data1 = dataRange1.getValues();
var data2 = dataRange2.getValues();3. The script iterates over the rows in both sheets (assuming the number of rows is the same in both sheets). It compares the invoice amounts from Sheet1 and Sheet2 for each corresponding row.
for (var i = 0; i < data1.length; i++) {
var row1 = data1[i];
var row2 = data2[i];
// Get invoice amounts from both sheets
var invoiceAmount1 = parseFloat(row1[3].toString().replace('$', '').replace(',', '.'));
var invoiceAmount2 = parseFloat(row2[3].toString().replace('$', '').replace(',', '.'));4. The script then checks the conditions for sending different types of emails based on the comparison of invoice amounts from Sheet1 and Sheet2.
if (invoiceAmount1 < invoiceAmount2) {
// Send "Thank You for Upgrading" email
} else if (invoiceAmount1 == 0) {
// Send "Churn customer notification" email
} else {
// Send "Invoice Reminder" email
}The key comparison is if (invoiceAmount1 < invoiceAmount2), which checks if the invoice amount in Sheet1 is less than the invoice amount in Sheet2 for the same row (i.e., the same customer). If true, the script assumes the customer has upgraded and sends a "Thank You for Upgrading" email. Otherwise, it checks for other conditions (churn or regular invoice reminders) and sends the appropriate email.
When a Google Sheets file is updated
Now, it pays to discuss a bit of a different use case, where people other than the account manager have access to the spreadsheet.
Let’s assume that our account manager has a day dedicated to meeting clients and answering their questions. The clients have access to a spreadsheet where they can briefly describe the problem and book a slot.
Okay, there are much better ways to handle client bookings and queries. But we’ll stick to this approach for the sake of the tutorial.
Step 1
First, we need to create a table. As shown below, ours is pretty simple, containing only three rows with Manager, Time slot, and Query.
Step 2
Next, you need Google scripts to trigger emails wherever someone updates the sheet with their query, thus booking a slot. Here’s an example.
//@OnlyCurrentDoc
function processEdit(e) {
MailApp.sendEmail({
to: "john.doe@example.com",
subject: "New booking -- client time slot",
body: "A client has a question for you."
});
}To help you understand what goes on, it pays to discuss the main functions.
//@OnlyCurrentDoc– this annotation signals that you want the script to run only in the specified Google Sheet. Removing the annotation enabled the script across your other files.function processEdit ()– a trigger (we’ll set up in the next step) runs the script with this function. Simply, the function sets the process to get an email each time someone updates the sheet.(e)– this annotation represents the object with data about the edits. It has a range property to signal that a range, or a cell, was edited.MailApp– an object that the function uses to relay the email.
Before moving on, you can test the sheet and the code. If you’re using the exemplary sheet, just populate one of the cells under the Query column, and run the script. The email should arrive in your inbox immediately, but mind you, the script hasn’t been automated yet.
Step 3
It’s time to set and authorize a trigger to send emails automatically. In Apps Script, click the alarm clock icon in the side menu. Then, click “…create a new trigger”.
In the trigger configuration pop-up, select the following criteria.
- processEdit
- Head
- From spreadsheet
- On edit
Note: It’s okay if you keep the Failure notification settings at default, but you can change that if necessary. It won’t affect the overall workings of the trigger or the script. This can apply to any of the Google scripts you might want to automate.
Click Save, authorize the trigger on your account, and you’ll see it in the Triggers list. Also, make sure to hit the Deploy button to activate the trigger.
Now, you can go back to the sheet, populate another cell in the Query column and check if you received the automated notification.
Side Note: You can customize the sheet to your preferences, but this means that you’ll need to customize the script as well.
Other considerations
By now, you should have a fully automated Google Sheet that sends emails. The cool thing is that you can even embed simple email templates into the scripts, plus add some conditional logic to make your workflow more efficient.
To stress yet again, any kind of mass email campaign from Google Sheets isn’t advisable. But if you’re just a start-up, or have a limited number of clients, i.e. <100, automating Google Sheets to send emails to multiple recipients could be useful. Should you need even more information, check Google Developer pages.
We hope you found our guide useful. Until next time!