Sending Stylized and Automated PDF reports with App Scripts

Intro

Use Case

The Goal

Step 1: Get the Data

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;

  }

Step 2: Create a Google Sheet and write data to it

/*
 * 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();
 }

Step 3: Make It Pretty

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();
  }

Step 4: PDF

/*
* 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

Email Templating

<p>Hello, <b><?= data["name"] ?></b>,</p>
<p>your report from <?= data["date"] ?> is ready!</p>

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

  MailApp.sendEmail({
    to: '<comma separated emails>',
    subject: "<subject>",
    attachments: [ ],
    htmlBody: template
  });

Code.gs

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)
  });

}

Setting the trigger

1. Click on the time icon in the toolbar

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

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

4. Click save

The Report

Final Thoughts