SPEAKER: It's common for spreadsheets
that help us run pivotal operations to reach limitations
when they begin to take the place of a database.
Performance issues begin to be noticed in speed or search.
This is where Google's BigQuery database comes in, especially
to help centralized data from multiple sources
or process very large volumes of rows and columns
when data exceeds spreadsheet quotas.
In this episode, I will share how
to use an app script to upload multiple Google
spreadsheets at once into a database
and then build dynamic dashboards
for your stakeholders to find insights using a free Google
product called Data Studio.
Note that this video's description
has a lot of excellent links to check out, including the script
and instructions on how to set this up with greater detail.
Welcome to the "Sheets to Apps" show
where we talk about how to tame these tabular beasts
into productive applications.
BigQuery is a SQL database in Google Cloud's platform.
It's incredibly fast and has a free tier
of 10 gigabytes of data a month, which allows organizations
to quickly prototype if this is a valuable solution for them
when it comes to hosting their data sets in a database.
First step is to log in to BigQuery.
The link is provided in this video.
There's a free sandbox if it's your first time.
Then create a new data set in BigQuery called sheets_upload.
Next, make a copy of the spreadsheet linked
in the video and leave column A, C, D. and E
as is for your first test.
Next, copy your BigQuery project ID
and use it to replace the values in column B of the spreadsheet
with your own project ID.
Next, note column E is called Append,
which has dropdown options of false or true.
This tells the script that you are adding data
to an existing table.
Choosing false will tell the script to create a new table
and will override an existing table with the same name.
Next, locate the Sheets to BigQuery menu
at the top of your sheet and select Upload.
You will be asked to authorize your script.
And after a few seconds, the upload will be complete
and column F of your sheet, called Status,
will be populated with the timestamp of the upload
for verification purposes.
To ensure the upload worked, let's visit BigQuery
and refresh your browser.
Next, expand your Sheets upload data set.
You will find two tables.
One is called CA financial data and
the other just financial data.
Next, click the CA financial data table and then details
to verify that there are 12 total rows.
This is because we uploaded six rows
from a tab in your sheet called California and then appended
another six from another tab called New California.
You can repeat this process with your own spreadsheets
by changing column A with your own spreadsheet URLs
and choosing false in the Append column to create a new table.
To visit the code, select Tools, Script Editor
from the spreadsheet's top menu.
A couple of things to note.
First, if you choose the file called code,
you will find the logic that displays the custom menu called
Sheets to BigQuery.
Next, the following constants locate the position
of the columns in the sheet.
Next, the forEach method processes each row.
And upon completion, it updates the status column
with a timestamp.
Next, the function Sheets to BigQuery uploads
the data from your spreadsheet into BigQuery,
bypassing all the necessary parameters such as the URL
of the sheet, the project ID, the data set, and table
name in BigQuery, as well as confirmation
if a sheet should be a new table or instead appended
to an existing one.
Do note you must have at least edit permissions
to the BigQuery data set you want to write values too.
And there you have it, a way to bulk upload multiple
spreadsheets into BigQuery.
Do note that you can also upload spreadsheets directly
from BigQuery's interface one by one.
However, this app script automates this for you,
which is handy when you need to perform frequent uploads.
Now for your stakeholders, the magical part of all of this
is being able to access this centralized data
from a dynamic and filterable report using Data Studio.
Note that you can create formulas
to perform important calculations at scale directly
in the dashboard.
We connect Data Studio with BigQuery
with just a few clicks thanks to its built in connectors.
Next, add tables, filters, design elements,
and then publish it to your desired audience
based on permissions settings.
You can even schedule the dashboard
to email stakeholders with the latest data
that you upload on a routine basis.
Well, I hope this walkthrough inspired
you to try moving a manual data analytics process that
presently involves pulling data from many different
spreadsheets, especially very large ones with tons
of formulas.
And if you found this episode helpful,
friends, please click Like and Subscribe to receive
alerts of future episodes.
Cheers.
[MUSIC PLAYING]