Sending Stylized and Automated PDF reports with App Scripts

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.

 

  1. Click on the time icon in the toolbar

 

  1. Click on the “Click here to add one now.”

 

  1. Set up the trigger like it’s shown in the screenshot:

 

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

One Comment

Leave a Comment