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.
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
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.
Personalising the code
Two things you need to note:
- You will need to edit the fields that define the recipients, the email subject and body
- 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.
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.
12 comments
Comment by eliot posted on
i tried a few add ons but opted to script for security. many techs share code, but none demonstrate your empathy for novices. the main problem for us is identifying locations among the code where customization is critical. your walk-through enabled me to cope with a stubborn webmaster in our organization who is refusing to post the google pdf export link, and demands pdfs emailed from me instead.
Comment by Shane posted on
How can I add multiple email recipients to the script?
Comment by Shane posted on
I am unable to view the pdf in the email, what am i doing wrong?
Comment by Stuart Parkins posted on
Hi when i open the pdf files it says they are corrupted and cannot be read? Is there a workaround to prevent this?
Comment by Paul Cronk posted on
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.
Comment by Jonathan Egle posted on
I'm having the same issue... I've tried adjusting security settings. When the permissions are set to "'Anyone with the link," the script will generate an error when I try to email a report.
"Request failed for https://docs.google.com/spreadsheets/d/1JYSdJ4TzgVqr1zWaWGnco2cWZT3SAx2lhpx_ya991hM/export?exportFormat=pdf&format=pdf&size=A4&portrait=true&fitw=true&sheetnames=false&printtitle=false&pagenumbers=false&gridlines=false&fzr=false&gid=0 returned code 400. Truncated server response: <!DOCTYPE html><html lang="en" ><head><meta name="description" content="Web word processing, presentations and spreadsheets"><meta name="viewport" ... (use muteHttpExceptions option to examine full response)"
When I change it to "Only me," it will successfully send an email, but the PDF will be corrupt.
What am I overlooking??? Help!
Comment by Jim Williams posted on
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.
Comment by Will posted on
Thank you! Perhaps getting greedy, but is it possible to capture a screenshot of dashboard and include that in the body of the email?
Comment by Paul Cronk posted on
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.
Comment by Mark posted on
Bless you! I've been wanting this functionality for a year 🙂
Comment by Jim Willians posted on
At last I consider this an early Christmas present!
Many thanks
Comment by Bob Smith posted on
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.