https://gdsdata.blog.gov.uk/2015/12/14/automating-the-emailing-of-a-google-sheets-dashboard/

Automating the emailing of a Google Sheets dashboard

Along with many of my colleagues, I often create dashboards on Google Sheets to share data to people who don’t have direct access to our Google Analytics (GA) accounts. By adding a piece of Google Apps Script to these spreadsheets you can get those dashboards automatically emailed to your contacts.

A spreadsheet set up for emailing

Adding scripts to sheets

I generally use the Google Analytics Spreadsheet Add-on to Google Docs to create dashboards. This provides a simple interface where you can build queries to the GA API and automate regular calls to update that data. I can then filter and perform calculations on that data and finally display the results as visualisations to a wider but selected audience.

Giving people access to a Google Sheet is one thing. Getting them to regularly review that data is another. So I’ve begun to experiment with emailing the data to them.

Google Apps Script is similar to JavaScript and lets you add functions to Google Apps like Docs, Sheets and Forms. There’s a script editor behind each Google Doc and the scripts run on Google's servers.

Practicalities

Preparing the spreadsheet

You will need to change the permissions on the Google Sheet to 'Anyone with the link. Anyone with the link has access - no sign-in required'. If the email works and contains a pdf but you're unable to open the pdf, then check the permissions on the sheet.

If that security level gives you a problem you could keep the raw data on a spreadsheet with a higher set of permissions and just pull in what you need onto the dashboard Sheet.

Adding the code

The process consists of:

  • opening the Script editor (available on every Google Doc) (number 1 in the screenshot)
  • pasting the script into the window
  • making some small changes to the script (such as the email addresses of the recipients)
  • saving the script and giving the ‘project’ a name (number 2)
  • running the script
  • authorising the script (numbers 3 & 4). You only need to do this the first time you run it
screenshots of the set up
The script needs to be saved and authorised

The code

You can paste the code straight into the script editor window. It then needs to be saved and you need to edit the lines that define the recipients (code line 19), the email subject and body (lines 30 & 33) and the PDF filename (line 65).

You can find the code here on Github.

There are two functions in the code. The first function adds a menu item to the Google Sheet which allows you to manually run the second function. This converts the Sheet to a PDF, attaches it to an email and sends it.

Screenshot of the code
Screenshot of the code pasted into the Google Sheet

Personalising the code

Two things you need to note:

  1. You will need to edit the fields that define the recipients, the email subject and body
  2. The script is set up to convert a single tab of content to a PDF. When a Google Sheet is created the first sheet is given a Google ID number of '...gid=0'. If you want to convert and email a different tab or if you've deleted that first tab you will need to change the gid number on line 55 of the code. You find a tab's gid number at the end of the URL

Triggering the mailing

You can trigger the file conversion and mailing manually or through a schedule. It can be done manually by selecting the new menu item that appears on the Sheet.

The other way would be to add a timed trigger to the script. Just as the Google Analytics Add-on can be scheduled to run and update the data regularly, the email script can be scheduled in a similar way. Triggers are set up on the Script Editor page: select the mailing function and choose a time-driven trigger.

Screenshots of scheduling
The mailing can be triggered manually or automatically

Variations on the script

The original version of this script was slightly more complicated. It took all the worksheets in a Google Sheet and converted each tab in turn to an individual PDF before zipping them together. That zip file was then attached to the email. I’ve removed those parts of the code, but they are still available in the original.

You can convert Sheets to Excel or CSV files rather than PDFs. I’ve noticed some minor display faults when Sheets are converted to an Excel format; but that can be true with PDFs too. There are other formatting configurations that are explained in the script.

In the body text of the email you may need to 'escape' special characters in the text; for example, to display a single quote  use \’ or for a double quote use \”. Otherwise the script will be confused. You can also use \n to add a line break and \t to add a tab.

I'm keen to hear about any other scripts you've found useful to supplement Google Docs.

Paul Cronk works with performance and communication analytics at GOV.UK.

8 comments

  1. Bob Smith

    All this effort with scripts and sharing online spreadsheets, purely to give a private viewing of some data.

    Why don't you just open the data? Maybe do it like your group already do with this? https://www.gov.uk/performance/site-activity . It's about being transparent, open by default, dog-fooding.

    Link to this comment Reply
  2. Jim Willians

    At last I consider this an early Christmas present!

    Many thanks

    Link to this comment Reply
  3. Mark

    Bless you! I've been wanting this functionality for a year 🙂

    Link to this comment Reply
  4. Will

    Thank you! Perhaps getting greedy, but is it possible to capture a screenshot of dashboard and include that in the body of the email?

    Link to this comment Reply
    • Paul Cronk

      I think you have undue confidence in my coding abilities! We’ve occasionally used a (Mac-only) app called Paparazzi! to automate the collection of screenshots, but I don’t think I can help you.

      Link to this comment Reply
  5. Jim Williams

    There's now a Google Sheets Add-on called Email Spreadsheets that now does all this beautifully but allows you to switch between pdf, csv, excel and other formats really easily. There is a FREE version which doesn't give you scheduling but the premium version costs a one off fee of $29.99 which should be affordable for most services I would have thought.

    The premium version gives you email scheduling plus various other configurations including the ability to select ranges to export from sheets plus allows the use of HTML emails as well.

    Link to this comment Reply
  6. Stuart Parkins

    Hi when i open the pdf files it says they are corrupted and cannot be read? Is there a workaround to prevent this?

    Link to this comment Reply
    • Paul Cronk

      From memory I think the answer is to check the permissions on the Google Sheet. I had to open it up to 'Anyone with the link. Anyone with the link has access - no sign-in required', otherwise I got that unhelpful message about PDF corruption.

      Link to this comment Reply

Leave a comment