Create Reports with Google Apps Script

Problem

Google Apps are extremely popular and useful especially when it comes to work on the same document simultaneously.

Very often, people who don’t have dedicated system for they work (e.g. like student organisations, people associated by mail or Facebook groups), they simply create a spreadsheet and work together at the same time. It works for singing up for events, writing down each others contact information, calculate spending, etc.
Probably for most of that tasks there are a lot of better dedicated tools but people choose Google Spreadsheet because they are familiar with that system and it is really easy to use.

Sometimes we have to publish results of that work to people outside of our group. Sending them read-only link to the spreadsheet is a solution but it really doesn’t look professional. Another example is when we have to create a report out of data in the spreadsheet.

Solution

To deal with that problem we can create a HTML report site which fetches data from our spreadsheet.
HTML site is not very easy to create (for a non-programmer) but it gives big flexibility. In that solution spreadsheet is simply a data source and a HTML site is a view.

But what if we need to process data before publishing? No problem, we can either create new page in the spreadsheet and process data with functions or use Google Apps Script which allows to write JS scripts and manipulate Google Docs data.

In this example our use sample data from UCI Machine Learning Repository.
I put part of Wine Quality data in the spreadsheet

Of course if only simple data is needed and we don’t need custom appearance for our report we can publish the data as above with File -> Publish to the web... option in the Google Spreadsheet.

But if we want to combine multiple data sources, perform some additional calculations or hide that the data is stored in the spreadsheet then we can create a website with Google Apps Script. To follow the tutorial copy the spreadsheet to your Google Drive.

To create the first script click Tools -> Script Editor then choose Script as Web App.
Function doGet() is invoked when deployed page is being opened.
We will deal with deploying website later, now we focus on fetching data from the spreadsheet so our Code.gs should look like:

function doGet() {  
}

Our Wine Quality spreadsheet contains defined data range (Data -> Named and protected ranges...) named RangeForScript. Now we can easily use that range in our code:

function getFromNamedRange(spreadSheet, sheetName, rangeName) {  
  var DATA_OFFSET = 1
  var sheet = spreadSheet.getSheetByName(sheetName);
  var range = spreadSheet.getRangeByName(rangeName);

  var headersRange = sheet.getRange(range.getRow(), range.getColumn(), 1, range.getLastColumn());
  var dataRange = sheet.getRange(range.getRow() + DATA_OFFSET, range.getColumn(), range.getLastRow(), range.getLastColumn());

  return [headersRange, dataRange];
}

function testGetFromNamedRange() {  
  var spreadsheetId = '0AoxwAeoFn9JIdEtkUkRVR3JOT0h0Y1JtT2xfUlJKZ2c'; // id of the spreadsheet from the URL

  var spreadSheet = SpreadsheetApp.openById(spreadsheetId);;
  var sheetName = 'Wine Quality';
  var rangeName = 'RangeForScript';

  var ranges = getFromNamedRange(spreadSheet, sheetName, rangeName);
  Logger.log(ranges[0].getValues());
  Logger.log(ranges[1].getValues());
}

From the URL of the spreadsheet we extract the key value and place it in the spreadsheetId variable. Then we get spreadsheet object and pass it to getFromNamedRange function. In the function we just split defined named range into range of headers and range of data. Then contents of ranges is written to Logger output (results of the testGetFromNamedRange can be seen by running the script and displaying View -> Logs... (Ctrl+Enter)).

We managed to fetched all data needed, now we are ready to create a HTML page.

<html>  
  <head>
    <style>
      body {
        padding-top: 10px;
        padding-bottom: 20px;
        margin-left: 20px;
        margin-right: 20px;
      }
    </style>
    <title>Wine Quality</title>
  </head>
  <body>
    <table>
      <thead>
        <tr>
        <? for (var i = 0; i < headers[0].length; ++i) { ?>
          <th><?= headers[0][i] ?></th>
        <? } ?>
        </tr>
      </thead>
      <tbody>
      <? for (var i = 0; i < data.length; ++i) { ?>
        <tr>
        <? for  (var j = 0; j < data[i].length; ++j) { ?>
          <td>
            <?= data[i][j] ?>
          </td>
        <? } ?>
        </tr>
      <? } ?>
      </tbody>
    </table>
  </body>
</html>  

Two variables are used in the code but they are not defined yet. Let’s assign them in the doGet() function:

function getHeaders(ranges) {  
  return ranges[0].getValues();
}

function getData(ranges) {  
  return ranges[1].getValues();
}

function doGet() {  
  var spreadsheetId = '0AoxwAeoFn9JIdEtkUkRVR3JOT0h0Y1JtT2xfUlJKZ2c'; // id of the spreadsheet from the URL
  var ss = SpreadsheetApp.openById(spreadsheetId);

  var t = HtmlService.createTemplateFromFile('index');
  var headersAndData = getFromNamedRange(ss, 'Wine Quality', 'RangeForScript')
  t.headers = getHeaders(headersAndData);
  t.data = getData(headersAndData);
  return t.evaluate();
}

In function doGet() HTML template from index.html is created and in template variables are assigned.
Now our report is ready to deploy. Click File -> Manage versions... -> Save New Version to create a new code version, then go to Publish -> Deploy as web app... and after following the link given by dialog a page like below can be seen:

Wine Quality View Example

So we reach our primary target. Now it’s time to tune up the appearance.
The easiest way to do that is to use twitter-bootstrap. However, it’s not as easy as usual because of Google Scipt file limitations. Fortunately, Google Apps Script provides libraries management and a library for the bootstrap already exist.
A quick google search leads us to GAS-Bootstrap.

First we have to install the library. Click Resources -> Manage libraries... and search for it by library ID given on the library page (MKvZneZcEte-sNijM1TaY_eMffa6w-w2J).

Next just by following the instructions on the library page we modify our template and doGet() function:

function getContentForTwitterBootstrap() {  
  var content = GASBootstrap.getBootstrapContents(
    "http://bootstrap_path/img/glyphicons-halflings.png", // provide your server path to avoid hotlinking
    "http://bootstrap_path/img/glyphicons-halflings-white.png"
    );
  return content;
}

function doGet() {  
  var spreadsheetId = '0AoxwAeoFn9JIdEtkUkRVR3JOT0h0Y1JtT2xfUlJKZ2c'; // id of the spreadsheet from the URL
  var ss = SpreadsheetApp.openById(spreadsheetId);

  var t = HtmlService.createTemplateFromFile('index');
  var headersAndData = getFromNamedRange(ss, 'Wine Quality', 'RangeForScript')
  t.headers = getHeaders(headersAndData);
  t.data = getData(headersAndData);
  t.bootstrapContent = getContentForTwitterBootstrap();
  return t.evaluate();
}
  <head>
    <script src="http://ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js"></script>
    <?!=bootstrapContent?>
    <style>
      body {
        padding-top: 10px;
        padding-bottom: 20px;
        margin-left: 20px;
        margin-right: 20px;
      }
    </style>
  </head>

In the Code.gs new variable with a content for header is added. In index.html value of the new variable is printed in the head section.

Finally, we use bootstrap features to tune up our layout.

    <div class="body">
      <div class="container-fluid">
        <div class="row-fluid">
          <div class="span2">
          </div>
          <div class="span2"><
            <img style="padding: 10px;" src="http://url_to_logo"/>
          </div>
          <div class="span8">
            <div class="row">
              <h3>Wine Quality</h3>
            </div>
          </div>
        </div>
        <div class="row-fluid">
          <table class='table table-striped table-bordered table-hover'>
            <thead>
            <tr>
            <? for (var i = 0; i < headers[0].length; ++i) { ?>
              <th><?= headers[0][i] ?></th>
            <? } ?>
            </tr>
            </thead>
            <tbody>
            <? for (var i = 0; i < data.length; ++i) { ?>
              <tr>
              <? for  (var j = 0; j < data[i].length; ++j) { ?>
                <td style='mono'>
                  <?= data[i][j] ?>
                </td>
              <? } ?>
              </tr>
            <? } ?>
            </tbody>
          </table>    
        </div>
      </div>
    </div>

And after deployment of the new version we can see the result.

Full code of Code.gs and index.html template is available on my github!

Detailed description of all Google Apps Script API functions can be found in the official documentation.