How to Email Google Sheets Automatically on a Recurring Schedule
The Email Spreadsheets add-on for Google Sheets can save office workers a ton of time by automating the reporting of spreadsheet data and dashboards by email. With this add-on, you can schedule reports to be sent automatically on a recurring schedule, so you don’t have to manually email spreadsheets to colleagues anymore.
With Email Spreadsheets, you can schedule reports and it will automatically send them by email on a recurring schedule. You can email entire workbooks, specific sheets inside a workbook or even range of cells. Watch the video to get started.
And because the add-on runs on the Google Cloud, your spreadsheet reports will be delivered even while you are offline or on vacation.
Email Google Sheets Automatically
For this example, our Google Spreadsheet has two sheets - the first sheet contains a data table and the second sheet contains an image chart with a neatly formatted table. We’ll build a scheduled workflow that will email the sheets data, including charts, on the first Monday of every week.
Step 1: Select Sheets to Export
Install the Email Google Sheets addon from Google marketplace. Next, open any Google Spreadsheet in your Google Drive, go to the Extensions menu inside the sheet, choose Email Spreadsheets from the dropdown. Click Open to launch the app and click the Create Workflow button to create your first scheduled email report.
You’ll be presented with a list of sheets available in the current workbook. Select one or more sheets that you would like to send with the scheduled email. You may export sheets in PDF, Excel, CSV or a PNG image. Each sheet is attached as a separate file in the email but you can choose the “Entire Workbook” option to create a single file from all sheets in the workbook.
You may also use dynamic markers to customize the file name of the exported files. For instance, the marker - will append the current date and month to the exported sheet name.
Tip: If your Google Sheet table is large, you can specify the cell range in A1 notation (like A1:G14) and only the specified range would be exported.
Step 2: Custom PDF Export Settings
The Email Google Sheets addon lets you customize the PDF layout that is exported from Google Sheets. You can change the paper orientation (Portrait or Landscape), the paper size or alter the print margins to fit more content on a page. You can choose to show gridlines, notes, sheet names and page numbers in the exported file.
Step 3: Write the Email Template
Next, we create an email template that will be sent with your reports. You can specify one or email recipients in the TO, CC, or BCC fields.
You can also specify dynamic email recipients based on cell values in the spreadsheet. For instance, if the email address of the recipient is specified in cell B2 of a sheet titled “Employee Shifts”, you can put in the To field, and the add-on will pull the dynamic value from the cell at the time of sending the email report.
These dynamic cell values enclosed inside double curly braces can be used inside any of the email fields including subject, email body, and the sender’s name.
The email body can include dynamic cell values as well as ranges that make it easy of you to send portions of the spreadsheet without sharing the full workbook. For instance, you can write to include only the specific range (B2:F9) from the Wages sheet. Internally, the add-on converts the range to an HTML table, retaining all the display formatting with CSS, and embed it into the email.
Charts and Timelines can be embedded into the email body using a special marker - you can find these markers inside the markers dropdown of the email editor. Business can also add their own logo and signature in the email body.
Tip: Use the Test Email button to send an email with the exported files before setting up the schedule.
Step 4: Setup the Email Schedule
The Email Google Sheets add-on includes an email scheduler to help you set up recurring schedules visually.
You can schedule and send emails hourly, daily, weekly, monthly or even on a yearly recurring basis. It is also possible to exclude dates and your spreadsheet won’t be emailed on the specified dates.
That’s it. Save the workflow and it will be activated instantly. You can also schedule multiple emails from the same Google Spreadsheet by adding more workflows.
The Email Spreadsheets add-on is a powerful tool that can help you automate the reporting of spreadsheet data and dashboards by email. To learn more about the Email Spreadsheets add-on and to download it, please visit the Google Workspace Marketplace.
Email Google Sheets - How it works?
The add-on is written in Google Apps Script. It uses the Google Sheets API to convert sheets to PDF files and uses the Gmail API for sending the converted files as attachments.
source:https://ift.tt/LIqUFmb
Comments
Post a Comment