Syncing Google Sheets to a database via REST API’s

Alex Alex 18 May 2020
Syncing Google Sheets to a database via REST API’s

In this article, I will walk you through how to integrate a web-based ordering system with a Google Sheet. 

Imagine you work for a bike-sharing startup that has a bike ordering web app built on Node.js. If you're familiar with JavaScript, Node.js is great since it runs on JavaScript code outside of a browser to develop things like web servers. The key functionality of this app is keeping track of changes in inventory, unit price, and the status of each bike order. The goal now is to have this information synced with a Google Sheet along with real-time pivot tables and charts for easy visualization.

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. Syncing Google Sheets to a database via REST API’sIn 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.Syncing Google Sheets to a database via REST API’s We then move on to downloading the Google API's Node.js client library and the OAuth 2 library.Syncing Google Sheets to a database via REST API’s 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. Syncing Google Sheets to a database via REST API’sThe 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.Syncing Google Sheets to a database via REST API’s

Finally, we created a function, which converts the order objects into cells and adds data validation for the different data types.Syncing Google Sheets to a database via REST API’s 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.Syncing Google Sheets to a database via REST API’s

 Let's take a quick look at each. Syncing Google Sheets to a database via REST API’s

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.Syncing Google Sheets to a database via REST API’s And the last one is the build/add chart request, which creates a bar chart called Revenue per Product.Syncing Google Sheets to a database via REST API’sAnd there we have it, a lovely synchronous process between a database and visualizations in Google Spreadsheets.

Comments (0)

    No comments yet

You must be logged in to comment.