Articles

Generating slides from spreadsheet data (The G Suite Dev Show)

October 12, 2019


[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]

No Comments

Leave a Reply