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

What you'll learn

What you'll need

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

Download Zip

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

git clone https://github.com/googlecodelabs/sheets-api.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.

First, let's 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 enter navigate to the codelab's start directory.
  2. Enter the following command to install the Node.js dependencies.
npm install
  1. Enter the following command to start the server:
npm start
  1. Open a web browser and navigate to http://localhost:3000

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 used 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 see the final result.

  1. Use this wizard to create or select a project in the Google Developers Console and automatically turn on the API. Click Continue, then Go to credentials.
  2. On the Add credentials to your project page, click the Cancel button.
  3. At the top of the page, select the OAuth consent screen tab. Select an Email address, enter a Product name if not already set, and click the Save button.
  4. Select the Credentials tab, click the Create credentials button and select OAuth client ID.
  5. Select the application type Web application, enter the name "Google Sheets API Quickstart".
  6. In the text box Authorized JavaScript origins enter the value http://localhost:3000 and then click the Create button.
  7. Take note of the resulting client ID, as you will need it in the next step. You don't need to download the file.

Before we can start exporting this data to Google Sheets, we 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 the Google Sign-in for Websites, a simple JavaScript library you can add to an existing web app.

Open the file views/layout.handlebars in a text editor, which defines the layout for each page, 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>

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

This code sets the OAuth2 client ID to use, the scope to request, and include the Google Sign-in library. In this case we are requesting the scope https://www.googleapis.com/auth/spreadsheets, since the applications needs read and write access to the user's spreadsheets.

Next we need to drop in 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, we need to add a little bit of 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 the Sign in button, and authorize access to your Google account. Your name and email address should be displayed in the application's header.

We want to keep track of the spreadsheets our application has created, so that as the data in our application changes the spreadsheets can be updated. In order to do that we need to create a table in our database to store information about the spreadsheets and add some controls to our UI.

Within the models/ directory create a new 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 uses the Sequelize ORM to define a new table that stores the ID, sheet ID, and name of the spreadsheets we create.

Next, we need to fetch all of the spreadsheets we've stored when the index page loads, so we 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']]
  };
  Sequelize.Promise.all([
    models.Order.findAll(options),
    models.Spreadsheet.findAll(options)
  ]).then(function(results) {
    res.render('index', {
      orders: results[0],
      spreadsheets: results[1]
    });
  });
});

Next, we need to 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://www.gdgdocs.org/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, we need to wire up the create 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 and you should see the new spreadsheets section on the right-hand side of the screen.

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

The Google Sheets API provides the ability to create and update spreadsheets, and to start using it we need to install the Google APIs Node.js client library and the companion auth library. Run the following commands in your console:

npm install googleapis --save
npm install google-auth-library --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 googleAuth = require('google-auth-library');
var util = require('util');

var SheetsHelper = function(accessToken) {
  var authClient = new googleAuth();
  var auth = new authClient.OAuth2();
  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, spreadsheet) {
    if (err) {
      return callback(err);
    }
    // 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, we need to 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);
    });
  });
});

Reload the application in your browser and click the Create button.

A new spreadsheet is created and displayed in the list. Click on the spreadsheet's name to open it, and you'll see that it has one sheet called Data which is currently blank.

Now that we are creating spreadsheets, let's start formatting them nicely, starting with a header row. We'll have the application add this header row right 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 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, which we'll define in a minute.

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 also defines the corresponding fields in the Order object (AKA database columns) which we'll use later on.

Finally let's define the buildHeaderRowRequest method we 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, which has the column's title as the value and which sets the formatting to bold. All of these cells are assembled together into an UpdateCells request and returned. The fields parameter is required and specifies exactly which fields of the CellData object to look at when applying the changes.

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

Of course, all of this creating and formatting a spreadsheet is meaningless if you don't put any actual data into it.

First let's add a new route to routes.js that will kick off 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.findById(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 which will transform the records to cells and make the API requests. Add the following code the sheets.js to do just that:

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 two requests. The first is an UpdateSheetPropertiesRequest which resizes the sheet to ensure there are enough rows and columns to fit the data it's about to write. The next is another UpdateCells request, which sets the cell values and formatting.

The buildRowsForOrders function is where we 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 a number value as well as number format to ensure the values are displayed correctly. Additionally, the status field has a data validation set in order to display a dropdown of the allowed status values. Although not particularly useful in this codelab, adding data validation to the spreadsheet can be useful if you want to allow users to edit the rows and later impact them back into your application.

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

You've now got your application exporting to Google Sheets, but honestly a similar result could have been achieved by exporting CSVs and manually importing them into Google Sheets. What separates this API-based approach from CSV 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 easily customize and extended.

To get started, we'll need to add a new sheet to our spreadsheets to contain the pivot table and chart. It's best to keep the sheets of raw data separate from the aggregations and visualizations, so that your syncing code can just focus 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 createSpreadsheet method, we'll need capture the ID of the "Pivot" sheet and use it to build some 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, which creates 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 the Create button again. The resulting spreadsheet should have a new sheet containing an empty pivot table and chart.

Click the Sync button to add data to the spreadsheet, and watch the pivot table and chart come to life with real data.

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

Possible improvements

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

Learn More