Guide to Automate Sending Email Spreadsheets on a Recurring Schedule

August 16, 2021 Twinkle Kapoor How To

Before we start, I have a simple question for you: How boring and difficult does sending emails manually feel? Exhausting, right? Well, I have a solution for you to automate emailing Google spreadsheets on a recurring schedule.

This doesn’t just automate the work, but also reduces human errors and simplifies your work. The plugin does the recurring work without ever forgetting to send emails, unlike humans, while you just lean back and relax.

The Email Spreadsheets plugin for Google sheets will mainly assist you to automate reporting of spreadsheet data and dashboards by email.

If your job requires you to email a lot of spreadsheets to colleagues manually, this add-on will be your time and effort savior. The best part is: it runs on Google cloud which delivers your spreadsheet reports even when you’re offline or holidaying.

The Email spreadsheets help you schedule reports that are automatically emailed on a recurring schedule. You can use it to email entire workbooks, specific sheets inside a workbook, or even a range of cells.

Automate Emailing Google Spreadsheets

Hop on to the Google add-on store and install Email Google Sheets. Now, open any Google spreadsheet on your Google drive, go to the Add-ons menu list and select Email Spreadsheet from the dropdown. Proceed further by choosing rules to create your first scheduled email report.

Here’s a 3-step guide to help you email schedule your spreadsheet report.

#Step 1: Select Sheet Export Options

  1. The expansion of the “Select Sheets” dropdown will give you an option to select one or more sheets that you would like to incorporate in the Email. Though each sheet is attached as a separate file in the email, you can use the option “Merge all sheets” to create a single file using all sheets in the workbook.
  • Select the export format from the available options of PDF, Excel (xlsx), OpenDocument or CSV formats. The “Email without attachment” option can be used to embed a specific range of cells in the body without including any sheet as an attachment.

  • In case you PDF as the export option, you are given a few more options. For instance, you can change the paper orientation (Portrait or landscape), the paper size or alter the print margins to include more content on a page. You also have the option to show gridlines, sheet names, notes, and page number in the exported file.

TIP: Before shooting further, use the Preview button to test how exported files would look in different export formats.

#Step 2:  Write Email Template

Next, we proceed further by creating an email template that will be sent with your reports. One or more email recipients can be specified in the TO, CC, or BCC fields. It is strictly suggested to separate multiple email addresses by a comma.

You can also specify dynamic email recipients based on cell values in the spreadsheet. For example, if the email address of the recipient is specified in cell B2 of a sheet titled “Employee Shifts”, you can put {{Employee Shifts!B2}} in the TO field, and the add-on will pull the dynamic value from the cell at the time of sending email report.

These double bracket enclosed dynamic cell values can also be used in any of the email fields like subject, email body, and the sender’s name. You can prevent sharing the complete workbook by including dynamic cell values as well as ranges that make it easy for you to send portions of the spreadsheet. For example, considering writing {{Employee Wages!B2:F9}} to include a specific range (B2:F9) from the wages sheet.

Also, the add-on converts the range to an HTML table, retaining all the display formatting with CSS, and embeds it into the email.

Standard HTML tags like H1, IMG, A, B, EM, and more can be included to embed images and rich formatting in your emails.

Tip: Use the Test button to send a test email with the exported files before setting up the schedule.

#Step 3: Create the Email Schedule

You can visually set up recurring schedules using the Google add-ons that also offer a scheduler. You can also set up advanced schedules like:

  • Send a recurring email on the last working day of the month.
  • Plan to send email reports every alternate day and end the reporting after 15 days.
  • Schedule to send quarterly emails and share email reports on the first Monday of the quarter.

You’re all done! Once you specify the schedule, hit the save button and your email report will be scheduled.

If you would like to edit your current email report or schedule a new report, go to the add-ons menu again, choose Email Spreadsheets and Rules.


Leave a Reply

Your email address will not be published. Required fields are marked *