Use Google Sheets as your application's reporting tool

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.

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.

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.

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.

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.

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

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

• Read the Google Sheets API developer documentation.
• Post questions and find answers on Stack Overflow under the google-sheets-api tag.