Intro
Google App Scripts
is a great tool which is often overlooked or, at least, is not as known as other Google services. However, most of the time it gets the job done very smoothly!
It has many of Google’s most used APIs built in making it very useful and easy for all sorts of different integrations and use cases. But you're not limited to just Google’s ecosystem…you can easily connect to other APIs that might be useful to you.
This blog post is geared toward people who've previously interacted with App Scripts and/or know a little bit of javascript.
If you're a total beginner, no worries.
For how to get started with App Scripts and to find another great use case for it, you can read one of our previous
blog posts
.
Use Case
You're running your company’s analytics team and you were asked to provide daily email reports with various metrics from Google Analytics that stakeholders need to look at every morning.
You could ask your capable but small-and-always-busy IT team for help. They may respond by saying it's certainly possible but they're busy, this would not be the priority, and something about servers and cron jobs…
You understand what they're saying but you just can’t afford to start rolling it out 2 months from now. Then you find a blog post and you're fairly familiar with javascript…
The Goal
We want to send a PDF report attached to an email every day. We want to make it somewhat presentable and throw in a chart or two. PDFs are nice because most email clients allow you to preview it very quickly.
To make this work we’ll need to connect to a few different APIs and internal App Scripts services.
We’ll use Google Analytics Reporting API, Google Sheet API, Google Drive API, App Scripts’ email service and App Scripts’ templating service.
For all TL;DR types
here
is the complete code which is explained below.
Step 1: Get the Data
Both versions (v3 and v4) of GA reporting API are build into App Scripts. In this case we're going to use v3 which might be better known and slightly easier to configure.
All code that deals with data pulling is located in
DataSource.gs
file.
getData: function(params) {
// the only dimension we want is date,
// also sort by descending date
var options = {
'dimensions': 'ga:date',
'sort': '-ga:date'
};
// use params to get viewId,
// set date range for the last 30 days,
// specify requested metrics
var report = Analytics.Data.Ga.get("ga:" + params["viewId"],
"31daysAgo", "yesterday",
"ga:users,ga:sessions,ga:bounceRate",
options);
var data = [
// this will act as a header
[ "Date", "Users", "Sessions", "Bounce Rate %" ]
];
for (var i = 0; i < report.rows.length; i++) {
// format date column so Google Sheets is able to recognize it as a date
report.rows[i][0] = CommonUtils.formatDate(CommonUtils.parseYYYYMMDDDate(report.rows[i][0]), 'yyyy-MM-dd' );
data.push(report.rows[i])
}
return data;
}
This code pulls GA metrics user, sessions and bounce rate broken down by date for the last 30 days. The GA Reporting API returns date in the ‘yyyyMMdd’ format.
To make it more readable, and for Google Sheets to recognize it as a date, we need to convert it into ‘yyyy-MM-dd’ format. To do that, we first need to parse it to a javascript Date object, which is done by the
CommonUtils.parseYYYYMMDDDate
function, then format it with
CommonUtils.formatDate.
The function returns array of arrays, where every array represents a row of data.
Step 2: Create a Google Sheet and write data to it
We have pulled our data in step 1, now we need to write it somewhere. Because we want to stylize it later our best bet is to use Sheets API, which is again built into App Scripts.
We've prepared our data in such a way that it’ll be very easy to write into the newly created sheet.
All code that deals with Google Sheets is in the
SheetUtils.gs
file.
/*
* createSheet: creates a new Google Sheet, writes and stylizes data
* name: name of the Google Sheet document
* data: array of arrays representing table data format
*/
createSheet: function(name, data) {
// create Google Sheet file
var ss = SpreadsheetApp.create(name);
// grab the first sheet of the document
var sheet = ss.getSheets()[0];
// write data to the sheet
for (var i = 0; i < data.length; i++){
sheet.appendRow(data[i]);
}
// make sure everything has been applied before continuing
SpreadsheetApp.flush();
// return the id of the create Google Sheet
return ss.getId();
}
The code is pretty simple, we create a new spreadsheet with some predefined name and obtain the first sheet (usually there are 3 by default).
Then we loop through our data and use
appendRow
to write data to the sheet.
Last step is to flush everything we’ve done, so we can be sure everything got updated and return spreadsheet id for future reference.
Step 3: Make It Pretty
We have data in our spreadsheet now, which is exactly where we want it to be. Built in spreadsheet services allow you to stylize and format your sheet as if you were doing it through the UI in Google Sheets. We have prepared a sample stylizing function (but much more is possible if you get into the details of the service). More can be found
here
.
stylize: function(ssId) {
// load spreadsheet
var ss = SpreadsheetApp.openById(ssId);
// grab the first sheet of the document
var sheet = ss.getSheets()[0];
// make header bold and create border at the bottom
sheet.getRange("A1:D1")
.setFontWeight("bold")
.setBorder(false, false, true, false, false, true, "black", SpreadsheetApp.BorderStyle.SOLID);
// make column A formatted as date
sheet.getRange("A2:A")
.setNumberFormat('yyyy MMM dd');
// make column D's number formats unified
sheet.getRange("D2:D")
.setNumberFormat('#.0');
// create a line chart with users and sessions
var chartBuilder = sheet.newChart();
chartBuilder.addRange(sheet.getRange("A1:C"))
.setChartType(Charts.ChartType.LINE)
.setPosition(1, 6, 0, 0)
.setOption('title', 'Users and sessions');
sheet.insertChart(chartBuilder.build());
// auto-resize each column, columns start with 1
for (var i = 0; i < 4; i++){
sheet.autoResizeColumn(i+1);
}
// make sure everything has been applied before continuing
SpreadsheetApp.flush();
}
First, we make the header bold and set a border at the bottom of the first 4 cells.
Next, we can format out date column in a format we want and/or prefer.
Our last column is bounce rate which is a decimal point number, to make the whole column more readable, we can set a unified number format with 1 decimal point.
With just a few lines we can throw in a little chart showing users and sessions with date as an x-axis.
Lastly, resize all columns to make sure they're wide enough to fit all the text.
Step 4: PDF
We know you can export Google Sheet documents to PDF if you're in the UI. Turns out you can do exactly the same through Drive API.
Once we've finished styling our sheet we just need to download it as a PDF. The actual exporting from Drive is not built in as part of a Drive service in App Scripts, but because App Scripts allows us to craft our own HTTP requests, we can create one that does this functionality for us.
All code that deals with Drive API is in
DriveUtils.gs
file.
/*
* downloadXLSX function accepts fileId parameter and downloads a file
* with that fileId
* fileId: id of a Google Sheet file
*/
downloadAsPdf: function(fileId) {
// get file in Drive by file id
var file = Drive.Files.get(fileId);
// set export type to PDF
var url = file.exportLinks[MimeType.PDF];
// set http headers and options
var options = {
headers: {
Authorization:"Bearer "+ ScriptApp.getOAuthToken()
},
muteHttpExceptions : true // Get failure results
}
// call the API to get the exported file
var response = UrlFetchApp.fetch(url, options);
var status = response.getResponseCode();
var result = response.getContentText();
// check if request succeeded
if (status != 200) {
// get additional error message info, depending on format
if (result.toUpperCase().indexOf("<HTML") !== -1) {
var message = result;
}
else if (result.indexOf('errors') != -1) {
message = JSON.parse(result).error.message;
}
throw new Error('Error (' + status + ") " + message );
}
// get bytes/file from the response
var doc = response.getBlob();
return doc;
}
Step 5: Putting it all together
All we need to do now is to send the report via email and we're done.
Email Templating
To easily craft a beautifully styled email we used HTML service, which is App Scripts very useful templating engine.
Here is our very simple template:
<p>Hello, <b><?= data["name"] ?></b>,</p>
<p>your report from <?= data["date"] ?> is ready!</p>
<?= >
denotes a parameter to be inserted in place of the tags. If we use the following object
{“name”: Anže, “date”: “2017-01-16”}
as template data
then the rendered template will look like this:
More on advanced features of templating engine like loops can be found
here
.
The code that does this is also pretty simple (
EmailTemplateUtils.gs
):
var EmailTemplateUtils = {
/*
* evaluate: evaluates a template against data and returns rendered html
* templateName: name of the template without the .html extension
* data: js object with parameters to be binded to the html template
*/
evaluate: function(templateName, data) {
var t = HtmlService.createTemplateFromFile(templateName);
t.data = data;
var c = t.evaluate();
var html = c.getContent();
return html;
}
}
Sending the Email
This part is extremely easy. App Scripts does everything for us in one line of code:
MailApp.sendEmail({
to: '<comma separated emails>',
subject: "<subject>",
attachments: [ ],
htmlBody: template
});
Code.gs
In
Code.gs
there is a single function called
main
which puts all of the steps together:
function main() {
var yesterday = CommonUtils.getDate(1);
var reportName = "my_report_" + CommonUtils.formatDate(yesterday, "yyyyMMdd");
// STEP 1
// set up from what view you want the data to be pulled
var dataParams = { "viewId": "<viewId>" }
var data = DataSource.getData(dataParams);
// STEP 2
var ssId = SheetUtils.createSheet(reportName, data);
// STEP 3
SheetUtils.stylize(ssId);
// STEP 4
var fileBlob = DriveUtils.downloadAsPdf(ssId);
fileBlob.setName(reportName + '.pdf');
// either delete created sheet or move it to trash
//DriveUtils.moveToTrash(ssId);
DriveUtils.deleteForever(ssId);
// STEP 5
var emailParams = {
name: "Anže",
date: CommonUtils.formatDate(yesterday, "yyyy-MM-dd")
}
MailApp.sendEmail({
to: 'anze@analyticspros.com',
subject: "My Report (" + CommonUtils.formatDate(yesterday, "yyyy-MM-dd") + ")",
attachments: [ fileBlob ],
htmlBody: EmailTemplateUtils.evaluate("emailTemplate", emailParams)
});
}
What we see is just a sequence of all the steps described above up to the point when we send the email.
Setting the trigger
Almost done! A great thing about App Scripts is it also allows you to set a trigger for a specific function. We'll use a time based trigger so our
main
function in
Code.gs
runs every day at 9 AM.
- Click on the time icon in the toolbar
- Click on the “Click here to add one now.”
- Set up the trigger like it’s shown in the screenshot:
- Click save
And there you have it. Daily PDF reports from GA using App Scripts. Serverless, reliable, easy to set up.
The Report
You don’t need to wait until the next day to get the report, in the toolbar you can select main function and press the play button. You should get the report sent over to your email in a few seconds. It should look something like this:
Final Thoughts
For this example we chose Google Analytics as our data source, but as you have seen it’s fairly easy to switch to any other API either already built in or some that are publicly available. For some inspiration, a popular choice would be to use BigQuery as a data source and report on various types of data you might have stored there. Is a BigQuery API built into App Scripts? Yes it is! Whichever Google’s APIs you end up using, don’t forget to enable them first in App Scripts and second in the GCP project associated with your script.
In this example we had to enable Google Analytics API (v3) and, as shown in the screenshot, Drive API. After you enable it in App Scripts make sure to click on “Google API Console” to also enable the same APIs globally for the GCP project. Let us know if you wish to see more App Scripts blog posts or have specific questions regarding this one.