Welcome to the ultimate guide for anyone wondering how to send emails from Google Sheets. Whether you’re a technical expert or someone looking to put your grey matter to work with a little bit of JavaScript and Google Spreadsheet, we’ve got you covered. We’ll teach you how to set up and automate email sending using just a few lines (okay, sometimes a little bit more) of Google Apps Script, without the need for extensions, heavy third-party tools, or complicated code.
Get ready to explore a wide range of use cases and script techniques, as we marry Google Sheets with your email inbox.
Why Send Emails from Google Sheets in the First Place? A Case Study
Put yourself in the shoes of an account manager who needs to automatically send emails from Google Sheets. Now, let’s get more specific about the example.
In general, the manager has a list of potential clients to bill each month through email notifications. And let’s say the company uses Xero, the accounting automation application.
Instead of manually retrieving email addresses to remind each client about an upcoming invoice, you can use Google Sheets to automate the process. Add the name and email address of each contact 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 data importing from Xero. With this, you can have all the contact information in one place and easily manage email communication. For more details, check out How to Integrate Xero and Google Sheets on the Coupler.io blog.
You can also customize the emails to make them more personal and engaging, increasing your chances of receiving payment for the invoice on time.
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 personalized emails.
But be sure to note the limitations of your Google account mentioned later in the article. In production, sending emails from Google Sheets is suitable for micro-businesses and maybe freelancers who have only a few clients. If you need to send a large volume of emails, you should use an appropriate MTA.
How to Send Emails from Google Sheets?
Before we begin, let’s assume you’ve already extracted the necessary data from Xero using Coupler and imported it into a spreadsheet.
To simplify things, our example spreadsheet only contains six columns: the Business Name, Email Address, Invoice Number, Invoice Amount, Due Date, and Subject of the recipients.
Yes, our sample sheet only has one client company, but that’s for testing purposes. You don’t want to send a whole batch of emails only to realize there was an error right from the start.
Additionally, we designed the script to go through the entire active sheet. It checks the data and sends emails to all the companies and addresses listed on the sheet. But we’ll discuss this later.
Now, let’s see how to make it work.
Click on Extensions in the toolbar, then on Apps Script – the tab to write your script automatically opens.
Below, you’ll find the script to create the email message and send it to the recipients on your spreadsheet. Of course, the script is tailored to our sample 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, as well as a loop and logging to go through the entire sheet. Here are the details.
1. Retrieve Data from the Spreadsheet
function sendEmails() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const dataRange = sheet.getRange("A2:F");
const data = dataRange.getValues();
}The script first communicates with the Google Spreadsheet service to get the currently active sheet.
Then, it sets a cell range from which to extract the data (in this case, it extracts data from columns A to F, starting from the second row). The getValues() method is then used to convert the range into a two-dimensional array of values.
2. Iterate through Rows
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];
// Rest of the code
}The function then enters a loop, iterating through each data row.
Each row is an array, and specific indices from that array are assigned to constants representing various pieces of information (business name, email address, invoice number, etc.).
3. Skip Invalid Rows
if (!emailAddress) {
console.log(`Row ${i + 2}: Email address is missing. Skipping this row.`);
continue;
}The script checks if the email address is missing for a given row. If so, it logs a message and moves on to the next iteration.
4. Generate the Email Message
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;
}If the email address is valid, the script then constructs an email message for that row using the createEmailMessage function.
This function takes the business name, invoice number, invoice amount, and due date to create a formatted string (message) that will serve as the body of the email.
5. Send the Emails
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}`);
}Lastly, it attempts to send the email using Google’s MailApp service.
The script logs a message indicating whether the email was successfully sent or if there was an error. The recipient of the email is the email address extracted from the row’s data, the subject is also derived from the row’s 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 email body.
Automating the Process
So far, so good, but the above script only runs manually unless we set triggers. Fortunately, Google has made setting up triggers for automation easy. Here are the steps.
- In the Apps Script editor, click on the triggers icon on the left sidebar (it looks like a small clock).
- Click on the “Create New Trigger” link or the “Add Trigger” button on the bottom right corner of the triggers page.
- In the “Select function to run” dropdown, select the
sendEmailsfunction. - In the “Select event source” dropdown, choose “Time-driven”.
- In the “Select type of time-based trigger” dropdown, choose the desired frequency (e.g., “Day timer” for daily email sending, “Week timer” for weekly email sending, etc.).
- If applicable, choose the desired time range or day of the week.
- Click “Save” to create the trigger.
After saving, click on the “Deploy” button on the top-right corner and follow the instructions to label and set the deployment. You’ll need to grant permission to run the automation and also for the script itself.
Regarding the given trigger, since our example pertains to sending invoice reminders, let’s assume these are monthly and it’s appropriate to send them on the 1st of every month between 1 PM and 2 PM. But of course, you can change the frequency and timing according to your needs.
Conclusion
By following these steps, you now have a fully automated Google Sheets that sends emails. What’s more interesting is that you can even embed simple email templates into the scripts and add conditional logic to make your workflow more efficient.
To emphasize once again, any kind of mass email campaign from Google Sheets is not recommended. But if you’re just a startup or have a limited number of clients, i.e., less than 100, automating Google Sheets to send emails to multiple recipients can be helpful. If you need more information, check out Google’s developer pages.
We hope you found our guide useful. Until next time!
