SPEAKER: Welcome back to our short series
on using G Suite APIs.
This video is intended for anyone interested in preparing
for their future careers.
And in this episode, I will walk you
through how to integrate a web-based ordering system
with a Google Sheet.
Note that this video's description contains
a wonderful Codelab with this exact use case created
by my peer, Eric [INAUDIBLE],, along with other helpful links.
So to start, 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.
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.
My name's [INAUDIBLE] and this has been a brief introduction
to G Suite's developer tools.
Cheers.
[MUSIC PLAYING]