[MUSIC PLAYING]
WESLEY CHUN: Hi, this is Wesley Chun, engineer at Google.
As you can guess, today's episode
focuses on Google Slides with a cameo appearance
by Google Sheets.
That's right, you can leverage multiple Google technologies
to help enhance your app.
I don't need to remind you that many of us
use spreadsheets for their utility.
They're part organizer, part database, part data analyzer,
and part visualizer.
A virtual Swiss army knife in the business world.
They help turn data into information.
While presentations aren't able to manipulate the data
in the same way a spreadsheet can,
they certainly take information and make
it more visually appealing and more easily understood
by human beings.
Let us show you how to use both tools to demonstrate
the flow of spreadsheet data into a more presentable format.
To get this show on the road, create a Google Sheet
with some data.
You can make up anything you want that fits your use case.
The example we're using features toy orders
and customer information.
Actually, this data wasn't made up.
It comes from a relational database
that's part of the Google Sheets API code lab.
if you know JavaScript and want to build
a node app that uses Sheets as a custom reporting tool,
give it a try.
It's totally optional, though.
One of the coolest features in Google Sheets
is the Explore button, where you can
select from a number of pre-generated visualizations
of your spreadsheet data.
Using the Explore feature for our toy orders sheet,
we've selected the cool pie chart
you see here representing the number of toys
ordered by customers.
Doesn't this data look like it's begging to be
put in a slide presentation?
Yes, I thought so, too.
So that's what we're going to do.
We'll demo a short Python script,
but you can write the same app in any language supported
by the Google API'S client libraries
and use today's script as just pseudo-code.
Be sure to have the latest Python client
library installed.
Also be sure to have a Google API project in the dev
console with both the Sheets and Slides APIs enabled.
If you're new to the Sheets API and the code lab
we referred to earlier is a bit too hard
core, check out this dev byte video,
which just creates a Sheet based on the toy orders data.
You have to use Explore in Sheets
to add the pie chart on your own, however.
Once your Sheet is ready to go, let's move to the computer
and show you how to generate slides
based on that information.
Let's start by taking a quick look at the toy order
Sheet created in an earlier video, plus the pie chart
added from the Explore feature in Sheets.
We'll create Slides based on both the spreadsheet
cells and the pie chart data.
Now let's look at the code.
We'll start on lines 1 through 18, which are basically
your standard imports and authorization boilerplate,
except for what we need in this app, meaning the pair of scopes
you see line 7 through 10.
Since we're only reading from Sheets,
we only need the read-only scope.
But since we're creating Slides, we
need the slides read-write scope,
and lines 16 through 18, where we're
creating a pair of service end points to both APIs
using the same side HTTP client.
Now the real action starts on 20 to 23.
This is where we fetch all cells from the Sheet
with a call to spreadsheets.values.get.
Now, you can't use my toy order Sheet,
so you must use your own on line 21.
You can also tweak the script to get it from the command line,
prompt the user for it, or get it from a database.
There's many choices here.
Lines 25 through 28 is where you use spreadsheets.get to get
the chart ID.
What's returned is an array of all the Sheets,
but since there's only one Sheet,
we access it at index 0 on line 27.
And since that Sheet only has one chart,
use index 0 to get that on line 28.
And that's all we need from Sheets.
It's all Slides from here on out.
Lines 30 to 36 create a brand-new slide deck.
From the API response, grab the presentation ID,
along with the IDs for the title and the subtitle text
boxes on the first page.
Lines 38 to 44 is where we're creating
two new slides for Sheets data-- a title-only Slide for the cell
table and a blank one for the pie chart.
And also add the title and subtitle on the title page.
45 through 48 is what we send all four of these requests
to the Slides API, saving the IDs of the Slides that
were just created.
Note replies come back in the same order
as the commands sent to the API, so the table Slide ID
comes first, followed by the chart Slide ID.
On lines 50 to 53 we fetch the elements on the table Slide.
Remember, it's a title-only Slide,
so the only object we get back in the array of page elements
is the title text box.
So we get it at index 0.
We'll use it down below on 62.
55 to 66 is where we create the table,
telling the API what slide to put it on,
and how many rows and columns it has.
Also give that Slide a title.
When the API call returns, we grab
the ID of the newly created table on 66.
68 to 74 is where we copy all cells from the Sheet
into the Slide's table.
Remember back on 22 when we use spreadsheets.values.get to get
all of the rows of the Sheet?
Well, that's how we fill in all the cells in the table.
Python developers will note we're
using a double for loop list comprehension to loop
through all the rows-- that's the I loop-- while the J
loop hits each column.
The next big chunk of code from 76 to 94
is where we build the request to import
the chart into its own Slide.
Now regarding the linking mode, linked allows for updates,
meaning if you update the Sheets data,
you can request a refresh of the chart.
Not linked image, as you can guess, is just a [INAUDIBLE].
Now, if you look roughly between 82 and 92,
the size and the transform may seem arbitrary, but it's not.
I basically drew a giant rectangle on a blank slide
and borrowed those values so that the created chart would
fit into a rectangle of the same dimensions.
This is not an uncommon practice if you
don't want to have to do the math to figure out the size
and transform on your own.
And then the last few lines, send the table and chart
request to the API.
And when it's done, we'll let the user know and exit.
Now, you ready to run this?
All right, let's give it a shot, assuming
that we've assigned a proper sheet ID earlier on line 21
and we've already accepted the OAuth 2.0 prompt
granting the two scopes we requested to this app.
Now I'm going to run this with Python 3,
but this code will run on Python 2 without modification.
And you can see each print statement.
I'll put one that request has been executed.
And when everything is done, we should
be able to go to our Drive, see the demo presentation has
been created, and open it up.
Here's our title page with a title and a subtitle,
the table Slide with the title, and the cell data
that we got from our spreadsheet-- exact match,
awesome.
And finally, the chart is now on its own slide.
Now you know how to create Slides
based on spreadsheet data.
Cool.
So that's it.
The code is a bit longer than we're generally
used to, clocking in at about 100 lines of code.
But keep in mind that most of it is
in creating the command payloads to send to the API, rather
than actual logic.
In total, there are two calls for the Sheets API
and five to the Slides API.
Now you know how to import Sheets data
into Slides using its API.
Check out the blog post for a closer look at the code.
To learn more about what you can do with tables,
check out the first link.
The next one is for those who want
to continue looking at importing charts from Sheets
into Slides presentations.
If you're new to the Slides API, the final link
points to an introductory overview
where you can learn basic concepts.
Now that you have the ability to leverage your spreadsheet
data into Slide decks programmatically, go out
and build the next great app.
Be sure to subscribe to our channel and tune
again next time.
This is Wesley Chun from Google, and we'll see you upstairs
in the G Suite.
[MUSIC PLAYING]
Hey, everyone.
Thanks for watching.
Check out these related videos, and we'll see you next time.
[MUSIC PLAYING]