SPEAKER: At just about every organization,
it's someone's job to prepare slide decks that contain lots
of informative data points.
Now this process often involves writing queries and then
manually adding the results into each slide.
But who wants to spend all that time copying and pasting?
Well, we're about to make your life a whole lot easier.
In this video, we'll walk through how
you can use Google Apps Scripts to automate
data driven slide development.
[MUSIC PLAYING]
Let's imagine you're a data analyst on the e-commerce team
for a huge retailer.
Each week, you're tasked with preparing Google Slides
for the director to present to the broader team.
The problem, this is a super time consuming task.
You're going to need to query the e-commerce data to come up
with the total sales, new customers, number of orders,
and repeat purchase rate.
But wait.
That's not it.
There are more than 20 different departments,
and each one needs their own slide.
My thumb and index fingers are hurting just
thinking about all those Control-C and Control-Vs.
Lucky for you, your company already
has their e-commerce data living inside of BigQuery.
And one of the many great things about BigQuery
is the flexibility it offers in terms
of interacting with your data.
BigQuery has a variety of APIs, client libraries, and out
of the box integrations with different Google services,
all of which make it easy to access your data where
and when you need it.
In this tutorial, we'll be using BigQuery's API
as a service that's natively available in Google Apps
Script.
All right.
Ready?
Let's jump in.
So the first step in automating the development of these slides
is to define our metrics using SQL.
Let's say it will calculate total sales for each slide
by filtering our orders to a specific department on a given
week, then summing up the total sales of all the items ordered.
Now, to ensure we're consistently
calculating our metrics, we can go ahead
and create a user-defined function inside of BigQuery.
With our function defined, we can run it any time
we need to calculate total sales simply
by feeding in a department name and week timestamp.
But what about actually pushing the results
of this function into a slide?
One option for programmatically developing slides
is to create a template slide that we'll use for formatting.
Here, I've created a pretty simple template
where variables that will be programmatically pushed
into our slide are surrounded by distinguishable characters,
like these double curly brackets.
In my template, I can control things like where on the slide
these variables should be printed
and the color of the text.
Great.
So we have our template all ready to go.
Now to actually run the calculations
and populate this template, we'll
turn to Google Apps Script.
Google Apps Script is a scripting environment
for lightweight JavaScript development within the Google
Workspace platform.
Inside the Apps Script IDE, we're
able to add Google services to our program
directly through the UI.
This will make interacting with the APIs
much easier since there's less setup than using their HTTP
interfaces.
Once we've added the services we'll be using,
BigQuery, Drive, and Slides, we're ready to start coding.
First, we'll need to make a copy of the template slide deck
to use as our weekly presentation.
Next, we'll loop through each department, create a new slide,
and then call this function.
Zooming in a bit, you can see that this function
goes into our new slide and iterates through each metric.
For each metric, we'll use this helper function
to call the BigQuery API and run the UDS
that we created earlier.
Finally, we'll replace the string containing
the curly brackets and our metric name
with the result from BigQuery.
When the loop completes, we'll have
one slide for each department with all our template
and metrics filled in appropriately.
And the finishing touch, since you
need this available for the director at the beginning
of each week, let's go ahead and add a trigger to run the script
every Monday morning.
With integrations across Google services,
you can deliver your data wherever and whenever
the business needs it.
Leveraging Google Apps Scripts to automate data
fueled document development opens up huge opportunities
to save analysts time when it comes to quarterly business
reviews or financial reporting.
Ready to get started?
Check out the link below to see some code samples.
[MUSIC PLAYING]