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.
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
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.