In this article, I will walk you through how to integrate a web-based ordering system with a Google Sheet.
In order to track the Sheets created by the app and keep the data synced to the Sheets, we generate a table in our database. To make this possible, we use a library called Sequelize, which helps map and synchronize data entries to objects within databases, such as MySQL, Postgres, et cetera. In Node, you can containerize code into what is called modules and then import them using
Require as well as
module.export for other files to access.
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. And with a bit of HTML, we will create two buttons, one called Create to generate spreadsheets and another button called Sync to sync that data. We then move on to downloading the Google API's Node.js client library and the OAuth 2 library. With these, you can create an access token to get credentials and initializes the Sheets API client.
Next, we'll use the
spreadsheets.create method for creating the spreadsheet. The
spreadsheets.batchUpdate method is commonly used for nearly every type of manipulation to a spreadsheet because it takes an array of request objects as an input, which contains specific operations to perform. In this example, we mapped the columns headers to fields in the databases order object.
Finally, we created a function, which converts the order objects into cells and adds data validation for the different data types. For example, the column status displays a dropdown with values, such as pending, shipped, and delivered. And there you go. We now have our application exporting to Google Sheets.
Now, I'm waiting for someone to ask, couldn't we have achieved a similar result exporting the data as CSVs and converting them into Google Sheets manually? Well, I'm glad you asked, since the additional benefit in using this API-based approach is that on top of managing sign-ins and automatic syncing, it also has the ability to add complex features to spreadsheets, such as pivot tables and charts automatically. Let me show you this in this final piece.
Note that a best practice is to have two different Sheets created within a spreadsheet, one for the raw data and the second to display the charts. This allows for the code used for syncing to focus on the data exclusively and not on the visualizations. So let's return to our earlier create spreadsheet method and add the following properties for creating a pivot table. We will also need to capture the ID of that second Sheet to build the following three requests, which build the pivot table, format the pivot table, and create a chart.
Let's take a quick look at each.
So this is what the build pivot table request looks like, which describes the columns and values that should be summarized. Then, there's the build form pivot table request, which ensures that data is formatted in a currency. And the last one is the build/add chart request, which creates a bar chart called Revenue per Product.And there we have it, a lovely synchronous process between a database and visualizations in Google Spreadsheets.