Use Google Sheets as your application's reporting tool

1. Overview

6e4b8c5ba82c473b.png

In this codelab, you'll learn how you can use Google Sheets as a custom-reporting tool for your users. You'll modify a sample order tracking application to export to a spreadsheet and then build visualizations using the Google Sheets API. The sample application is built using Node.js and the Express web application framework, but the same basic principles are applicable to any architecture.

What you'll learn

  • Adding Google Sign-in to an application.
  • Installing and configuring the Google APIs Client Library for Node.js.
  • Creating spreadsheets.
  • Exporting database records to a spreadsheet.
  • Creating pivot tables and charts.

What you'll need

  • Node.js installed (>=v8.11.1).
  • The npm package management tool (comes with Node.js).
  • Access to the internet and a browser.
  • A Google Account.

2. Get the sample code

You can either download all the sample code to your computer...

...or clone the GitHub repository from the command line.

git clone https://github.com/googleworkspace/sheets-api-codelab.git

The repository contains a set of directories representing each step along the process, in case you need to reference a working version.

You'll be working off the copy located in the start directory, but you can refer to, or copy files from, the others as needed.

3. Run the sample app

First, get the sample order-tracking application up and running. With the code downloaded, follow the instructions below to install and start the Node.js/Express web application:

  1. Open a command-line terminal on your computer and navigate to the codelab's start directory.
  2. To install the Node.js dependencies, enter the following command:
npm install
  1. To start the server, enter the following command:
npm start
  1. Open a browser and navigate to http://localhost:8080

4b4cef1211293bc9.png

The application provides the ability to create, update, and delete a set of simple order records. We've included a SQLite database with some sample data, but feel free to add, update, and delete orders as you progress through the codelab.

Take a moment to familiarize yourself with the code, and refer to the table below for a general overview of the application's structure:

app.js

Configures the Express web application framework.

config.json

A configuration file, containing the database connection information.

db.sqlite

A SQLite database to store the order records.

models/

Contains the code that defines and loads the database models. This application uses the Sequelize ORM library for reading and writing to the database.

node_modules/

Contains the project's dependencies, as installed by npm.

package.json

Defines the Node.js application and its dependencies.

public/

Contains the client-side JavaScript and CSS files used by the application.

routes.js

Defines the URL endpoints the application supports and how to handle them.

server.js

The entry point into the application, which configures the environment and starts the server.

views/

Contains the HTML templates to be rendered, written using the Handlebars format. The Material Design Lite (MDL) library has been used for layout and visual appeal.

The rest of the codelab walks you through modifying the base application in the start directory, but if you have trouble with a certain step you can switch to that step's directory to view the final result.

4. Create a client ID

Before creating a client ID, you must turn on the Google Sheets API.

  1. Use this wizard to go to the Enable access to API page.
  2. Make sure the correct project is selected in the projects list at the top of the page, and then click Next.
  3. Click Enable.

Next, add an OAuth client ID to your project.

  1. Click Menu menu.png and go to APIs & Services > Credentials (direct link).
  2. Click Create Credentials > OAuth client ID.
  3. For Application type, select Web application and add the name "Google Sheets API Quickstart".
  4. For Authorized JavaScript origins, click Add URI and add the value http://localhost:3000.
  5. Click Create.
  6. Make a note of the client ID shown in the Your Client ID field as you'll need it in the next step. You don't need to download the file.
  1. Click OK.

5. Add Google Sign-in

Before you can start exporting data to Google Sheets, you need the user to sign in to your application with their Google Account and authorize access to their spreadsheets. To do this, we'll be using Google Sign-in for Websites, a JavaScript library you can add to an existing web app.

The file views/layout.handlebars defines the layout for each page. Open it in a text editor and add the following code to the end of the <head> tag:

views/layout.handlebars

<meta name="google-signin-scope" 
      content="https://www.googleapis.com/auth/spreadsheets">
<meta name="google-signin-client_id" content="{YOUR CLIENT ID}">
<script src="https://apis.google.com/js/platform.js" async defer></script>

Overwrite the placeholder {YOUR CLIENT ID} with the OAuth2 client ID you created in the previous step.

This code sets the OAuth2 client ID, the requested scope, and includes the Google Sign-in library. In this case, we're requesting the scope https://www.googleapis.com/auth/spreadsheets since the application needs both read and write access to the user's spreadsheets.

Next add the code that renders the sign-in button and displays the signed-in user's information. Add the following code to views/layout.handlebars, just under <div id="spinner">

views/layout.handlebars

<div id="profile" style="margin: 0 20px;">
  <b class="name"></b><br/>
  <i class="email"></i>
</div>
<div class="g-signin2" data-onsuccess="onSignIn"></div>

Finally, add some client-side JavaScript to populate the profile section once sign-in is complete. Add the following to public/script.js:

public/script.js

function onSignIn(user) {
  var profile = user.getBasicProfile();
  $('#profile .name').text(profile.getName());
  $('#profile .email').text(profile.getEmail());
}

Reload the application in your browser, click Sign in, and authorize access to your Google Account. Your name and email address should be displayed in the application's header.

33c9fbcbe5687c7f.png

6. Add spreadsheet controls

You need to track the spreadsheets your application creates so if the data in the application changes, the spreadsheets can be updated. To do that, create a table in the database to store information about the spreadsheets. You'll also add some controls to the UI.

Within the models/ directory, create a file called spreadsheets.js with the following code:

models/spreadsheets.js

"use strict";

module.exports = function(sequelize, DataTypes) {
  var Spreadsheet = sequelize.define('Spreadsheet', {
    id: {type: DataTypes.STRING, allowNull: false, primaryKey: true},
    sheetId: {type: DataTypes.INTEGER, allowNull: false},
    name: {type: DataTypes.STRING, allowNull: false}
  });

  return Spreadsheet;
};

This code uses the Sequelize ORM to define a new table that stores the ID, sheet ID, and name of the spreadsheets you create.

Next, fetch the spreadsheets you've stored when the index page loads, so you can display them in a list. In routes.js, replace the code for the "/" route with the following:

routes.js

router.get('/', function(req, res, next) {
  var options = {
    order: [['createdAt', 'DESC']],
    raw: true
  };
  Sequelize.Promise.all([
    models.Order.findAll(options),
    models.Spreadsheet.findAll(options)
  ]).then(function(results) {
    res.render('index', {
      orders: results[0],
      spreadsheets: results[1]
    });
  });
});

Next, display the list of spreadsheets in the template. Add the following code to the end of views/index.handlebars, within the existing <div class="mdl-grid">:

views/index.handlebars

<section id="spreadsheets"
         class="mdl-cell mdl-cell--4-col relative">
  <div class="mdl-list">
    <div class="mdl-list__item">
      <span class="mdl-list__item-primary-content mdl-layout-title">
        Spreadsheets</span>
      <span class="mdl-list__item-secondary-action">
        <button class="mdl-button mdl-js-button mdl-button--raised
                       mdl-js-ripple-effect mdl-button--colored"
                rel="create" type="button">Create</button>
      </span>
    </div>
    {{#each spreadsheets}}
    <div class="mdl-list__item">
      <a class="mdl-list__item-primary-content"
         href="https://docs.google.com/spreadsheets/d/{{id}}/edit"
         target="_blank">{{name}}</a>
      <span class="mdl-list__item-secondary-action">
        <button class="mdl-button mdl-js-button mdl-button--raised
                       mdl-js-ripple-effect"
                rel="sync" data-spreadsheetid="{{id}}"
                type="button">Sync</button>
      </span>
    </div>
    {{/each}}
  </div>
</section>

Finally, wire up the create spreadsheet and sync spreadsheet buttons. Add the following code to public/script.js:

public/script.js

$(function() {
  $('button[rel="create"]').click(function() {
    makeRequest('POST', '/spreadsheets', function(err, spreadsheet) {
      if (err) return showError(err);
      window.location.reload();
    });
  });
  $('button[rel="sync"]').click(function() {
    var spreadsheetId = $(this).data('spreadsheetid');
    var url = '/spreadsheets/' + spreadsheetId + '/sync';
    makeRequest('POST', url, function(err) {
      if (err) return showError(err);
      showMessage('Sync complete.');
    });
  });
});

function makeRequest(method, url, callback) {
  var auth = gapi.auth2.getAuthInstance();
  if (!auth.isSignedIn.get()) {
    return callback(new Error('Signin required.'));
  }
  var accessToken = auth.currentUser.get().getAuthResponse().access_token;
  setSpinnerActive(true);
  $.ajax(url, {
    method: method,
    headers: {
      'Authorization': 'Bearer ' + accessToken
    },
    success: function(response) {
      setSpinnerActive(false);
      return callback(null, response);
    },
    error: function(response) {
      setSpinnerActive(false);
      return callback(new Error(response.responseJSON.message));
    }
  });
}

Reload the application in your browser. The new spreadsheets section should now be on the screen.

eb8411f344b1f435.png

Since the database is empty, there are no spreadsheets to show. As well, the create button won't do anything just yet.

7. Create spreadsheets

The Google Sheets API provides the ability to create and update spreadsheets. To start using it, install the Google APIs Node.js client library and the companion authentication library.

Run the following commands in your console (you might need to stop the server first):

npm install googleapis@26.* --save
npm install google-auth-library@1.* --save

Next we'll create a helper class that will use the libraries to create and update our spreadsheets. Create a file called sheets.js in the root directory of the application with the following code:

sheets.js

var {google} = require('googleapis');
var {OAuth2Client} = require('google-auth-library');
var util = require('util');

var SheetsHelper = function(accessToken) {
  var auth = new OAuth2Client();
  auth.credentials = {
    access_token: accessToken
  };
  this.service = google.sheets({version: 'v4', auth: auth});
};

module.exports = SheetsHelper;

Given an OAuth2 access token, this class creates the credentials and initializes the Sheets API client.

Next we'll add a method for creating a spreadsheet. Add the following to the end of sheets.js:

sheets.js

SheetsHelper.prototype.createSpreadsheet = function(title, callback) {
  var self = this;
  var request = {
    resource: {
      properties: {
        title: title
      },
      sheets: [
        {
          properties: {
            title: 'Data',
            gridProperties: {
              columnCount: 6,
              frozenRowCount: 1
            }
          }
        },
        // TODO: Add more sheets.
      ]
    }
  };
  self.service.spreadsheets.create(request, function(err, response) {
    if (err) {
      return callback(err);
    }
    var spreadsheet = response.data;
    // TODO: Add header rows.
    return callback(null, spreadsheet);
  });
};

This method defines a simple Spreadsheet object and calls the spreadsheets.create method to create it on the server.

Finally, add a new route to our application that takes the request from the spreadsheet controls, calls the helper to create the spreadsheet, and then saves a record in the database. Add the following code to the end of routes.js:

routes.js

var SheetsHelper = require('./sheets');

router.post('/spreadsheets', function(req, res, next) {
  var auth = req.get('Authorization');
  if (!auth) {
    return next(Error('Authorization required.'));
  }
  var accessToken = auth.split(' ')[1];
  var helper = new SheetsHelper(accessToken);
  var title = 'Orders (' + new Date().toLocaleTimeString() + ')';
  helper.createSpreadsheet(title, function(err, spreadsheet) {
    if (err) {
      return next(err);
    }
    var model = {
      id: spreadsheet.spreadsheetId,
      sheetId: spreadsheet.sheets[0].properties.sheetId,
      name: spreadsheet.properties.title
    };
    models.Spreadsheet.create(model).then(function() {
      return res.json(model);
    });
  });
});

If you stopped your server above, restart the server using the following command:

npm start

In your browser, navigate to http://localhost:8080 to load the application.

Click Create.

28052ca2b4558907.png

A new spreadsheet is created and displayed in the list. Click the spreadsheet's name to open it, and you'll find it has one blank sheet named Data.

1c66f593d28a70fa.png

8. Add a header row

Now that we're creating spreadsheets, let's format them starting with a header row. We'll have the application add this header row after it creates the spreadsheet. In sheets.js, replace return callback(null, spreadsheet); in the method createSpreadsheet with the following:

sheets.js

var dataSheetId = spreadsheet.sheets[0].properties.sheetId;
var requests = [
  buildHeaderRowRequest(dataSheetId),
];
// TODO: Add pivot table and chart.
var request = {
  spreadsheetId: spreadsheet.spreadsheetId,
  resource: {
    requests: requests
  }
};
self.service.spreadsheets.batchUpdate(request, function(err, response) {
  if (err) {
    return callback(err);
  }
  return callback(null, spreadsheet);
});

This code uses the Sheets API's spreadsheets.batchUpdate method, which is used for nearly every type of manipulation to a spreadsheet. The method takes an array of Request objects as input, each of which contains the specific type of request (operation) to perform on the spreadsheet. In this case, we're only passing a single request to format the header row.

Next we'll need to define the column headers. Add the following code to the end of sheets.js:

sheets.js

var COLUMNS = [
  { field: 'id', header: 'ID' },
  { field: 'customerName', header: 'Customer Name'},
  { field: 'productCode', header: 'Product Code' },
  { field: 'unitsOrdered', header: 'Units Ordered' },
  { field: 'unitPrice', header: 'Unit Price' },
  { field: 'status', header: 'Status'}
];

This code above also defines the corresponding fields in the Order object (similar to database columns) which we'll use later on.

Finally, define the buildHeaderRowRequest method referenced earlier. In the same file add the following:

sheets.js

function buildHeaderRowRequest(sheetId) {
  var cells = COLUMNS.map(function(column) {
    return {
      userEnteredValue: {
        stringValue: column.header
      },
      userEnteredFormat: {
        textFormat: {
          bold: true
        }
      }
    }
  });
  return {
    updateCells: {
      start: {
        sheetId: sheetId,
        rowIndex: 0,
        columnIndex: 0
      },
      rows: [
        {
          values: cells
        }
      ],
      fields: 'userEnteredValue,userEnteredFormat.textFormat.bold'
    }
  };
}

This code loops over each column and creates a CellData object for each one, setting the column's title as the value and the formatting to bold. All cells are assembled into an UpdateCells request and returned. The fields parameter is required and specifies exactly which fields of the CellData object to find when applying the changes.

Reload the application in your browser and click Create. The resulting spreadsheet should include a header row with a column for each defined field.

e3d2f5a6896be910.png

9. Synchronize data to the spreadsheet

Creating and formatting spreadsheets is pointless if you don't add any actual data to it.

First, let's add a new route to routes.js that will begin a sync:

routes.js

router.post('/spreadsheets/:id/sync', function(req, res, next) {
  var auth = req.get('Authorization');
  if (!auth) {
    return next(Error('Authorization required.'));
  }
  var accessToken = auth.split(' ')[1];
  var helper = new SheetsHelper(accessToken);
  Sequelize.Promise.all([
    models.Spreadsheet.findByPk(req.params.id),
    models.Order.findAll()
  ]).then(function(results) {
    var spreadsheet = results[0];
    var orders = results[1];
    helper.sync(spreadsheet.id, spreadsheet.sheetId, orders, function(err) {
      if (err) {
        return next(err);
      }
      return res.json(orders.length);
    });
  });
});

Like the previous route for creating spreadsheets, this one checks for authorization, loads models from the database, and then passes the information to the SheetsHelper. It will then transform the records to cells and make the API requests. Add the following code the sheets.js:

sheets.js

SheetsHelper.prototype.sync = function(spreadsheetId, sheetId, orders, callback) {
  var requests = [];
  // Resize the sheet.
  requests.push({
    updateSheetProperties: {
      properties: {
        sheetId: sheetId,
        gridProperties: {
          rowCount: orders.length + 1,
          columnCount: COLUMNS.length
        }
      },
      fields: 'gridProperties(rowCount,columnCount)'
    }
  });
  // Set the cell values.
  requests.push({
    updateCells: {
      start: {
        sheetId: sheetId,
        rowIndex: 1,
        columnIndex: 0
      },
      rows: buildRowsForOrders(orders),
      fields: '*'
    }
  });
  // Send the batchUpdate request.
  var request = {
    spreadsheetId: spreadsheetId,
    resource: {
      requests: requests
    }
  };
  this.service.spreadsheets.batchUpdate(request, function(err) {
    if (err) {
      return callback(err);
    }
    return callback();
  });
};

Here again we're using the batchUpdate method, this time passing in 2 requests. The first is an UpdateSheetPropertiesRequest that resizes the sheet to ensure there are enough rows and columns to fit the data it's about to write. The second is an UpdateCellsRequest that sets the cell values and formatting.

The buildRowsForOrders function is where you convert the Order objects into cells. Add the following code to the same file:

sheets.js

function buildRowsForOrders(orders) {
  return orders.map(function(order) {
    var cells = COLUMNS.map(function(column) {
      switch (column.field) {
        case 'unitsOrdered':
          return {
            userEnteredValue: {
              numberValue: order.unitsOrdered
            },
            userEnteredFormat: {
              numberFormat: {
                type: 'NUMBER',
                pattern: '#,##0'
              }
            }
          };
          break;
        case 'unitPrice':
          return {
            userEnteredValue: {
              numberValue: order.unitPrice
            },
            userEnteredFormat: {
              numberFormat: {
                type: 'CURRENCY',
                pattern: '"$"#,##0.00'
              }
            }
          };
          break;
        case 'status':
          return {
            userEnteredValue: {
              stringValue: order.status
            },
            dataValidation: {
              condition: {
                type: 'ONE_OF_LIST',
                values: [
                  { userEnteredValue: 'PENDING' },
                  { userEnteredValue: 'SHIPPED' },
                  { userEnteredValue: 'DELIVERED' }
                ]
              },
              strict: true,
              showCustomUi: true
            }
          };
          break;
        default:
          return {
            userEnteredValue: {
              stringValue: order[column.field].toString()
            }
          };
      }
    });
    return {
      values: cells
    };
  });
}

The unitsOrdered and unitPrice fields set both a number value and a number format to ensure the values are displayed correctly. Additionally, data validation is set on the status field to display a list of the allowed status values. Although not useful in this codelab, adding data validation to the spreadsheet can be effective if you plan to allow users to edit the rows and send updates back into your application.

Reload the application in your browser and click Sync next to the spreadsheet link. The spreadsheet should now contain all your order data. Add a new order and click Sync again to view the changes.

ec77f68d38a01ab3.png

10. Add a pivot table and chart

Your application now exports to Google Sheets, but a similar result could have been achieved by exporting a CSV file and manually importing it into Google Sheets. What separates this API-based approach from CSVs is the ability to add complex features to spreadsheets, such as pivot tables and charts. This allows you to leverage Google Sheets as a dashboard to your data that users can customize and extend.

To begin, we'll need to add a new sheet to our spreadsheet to contain the pivot table and chart. It's best to keep the sheet of raw data separate from any aggregations and visualizations so your syncing code can focus solely on the data. In sheets.js, add the following code to the array of sheets being created in SheetsHelper's createSpreadsheet method:

sheets.js

{
  properties: {
    title: 'Pivot',
    gridProperties: {
      hideGridlines: true
    }
  }
}

Later on in the createSpreadsheet method, we'll need to capture the ID of the "Pivot" sheet and use it to build new requests. Add the following code after var requests = [...]:

sheets.js

var pivotSheetId = spreadsheet.sheets[1].properties.sheetId;
requests = requests.concat([
  buildPivotTableRequest(dataSheetId, pivotSheetId),
  buildFormatPivotTableRequest(pivotSheetId),
  buildAddChartRequest(pivotSheetId)
]);

Finally, add the following functions to the file to create requests for building the pivot table, formatting the results, and adding the chart:

sheets.js

function buildPivotTableRequest(sourceSheetId, targetSheetId) {
  return {
    updateCells: {
      start: { sheetId: targetSheetId, rowIndex: 0, columnIndex: 0 },
      rows: [
        {
          values: [
            {
              pivotTable: {
                source: {
                  sheetId: sourceSheetId,
                  startRowIndex: 0,
                  startColumnIndex: 0,
                  endColumnIndex: COLUMNS.length
                },
                rows: [
                  {
                    sourceColumnOffset: getColumnForField('productCode').index,
                    showTotals: false,
                    sortOrder: 'ASCENDING'
                  }
                ],
                values: [
                  {
                    summarizeFunction: 'SUM',
                    sourceColumnOffset: getColumnForField('unitsOrdered').index
                  },
                  {
                    summarizeFunction: 'SUM',
                    name: 'Revenue',
                    formula: util.format("='%s' * '%s'",
                        getColumnForField('unitsOrdered').header,
                        getColumnForField('unitPrice').header)
                  }
                ]
              }
            }
          ]
        }
      ],
      fields: '*'
    }
  };
}

function buildFormatPivotTableRequest(sheetId) {
  return {
    repeatCell: {
      range: { sheetId: sheetId, startRowIndex: 1, startColumnIndex: 2 },
      cell: {
        userEnteredFormat: {
          numberFormat: { type: 'CURRENCY', pattern: '"$"#,##0.00' }
        }
      },
      fields: 'userEnteredFormat.numberFormat'
    }
  };
}

function buildAddChartRequest(sheetId) {
  return {
    addChart: {
      chart: {
        spec: {
          title: 'Revenue per Product',
          basicChart: {
            chartType: 'BAR',
            legendPosition: 'RIGHT_LEGEND',
            domains: [
              // Show a bar for each product code in the pivot table.
              {
                domain: { sourceRange: { sources: [{
                  sheetId: sheetId,
                  startRowIndex: 0,
                  startColumnIndex: 0,
                  endColumnIndex: 1
                }]}}
              }
            ],
            series: [
              // Set that bar's length based on the total revenue.
              {
                series: { sourceRange: { sources: [{
                  sheetId: sheetId,
                  startRowIndex: 0,
                  startColumnIndex: 2,
                  endColumnIndex: 3
                }]}}
              }
            ]
          }
        },
        position: {
          overlayPosition: {
            anchorCell: { sheetId: sheetId, rowIndex: 0, columnIndex: 3 },
            widthPixels: 600,
            heightPixels: 400
          }
        }
      }
    }
  };
}

function getColumnForField(field) {
  return COLUMNS.reduce(function(result, column, i) {
    if (column.field == field) {
      column.index = i;
      return column;
    }
    return result;
  });
}

Reload the application in your browser and click Create. The resulting spreadsheet should have a new sheet containing an empty pivot table and chart.

b7fd3e24ce7e57ef.png

Click Sync to add data to the spreadsheet. The pivot table and chart are also populated with data.

8b2bc0132340a25b.png

11. Congratulations!

You've successfully modified an application to export data to Google Sheets. Users can now build custom reports and dashboards over your data without the need for any additional code, and all while being kept in sync as the data changes.

Possible improvements

Here are some more ideas for making an even more compelling integration:

  • Automatic sync—Rather than have the user click a button to sync, update the spreadsheets automatically whenever orders are changed. You'll need to request offline access from your users, and track which user owns a given spreadsheet.
  • Two-way sync—Allow users to modify order details in the spreadsheet and push those changes back to the application. You can use Google Drive push notifications to detect whether the spreadsheet was updated and the Sheets API's spreadsheets.values.get method to pull in the updated data.

Learn more