WESLEY CHUN: Hey, developers, this is Wesley Chun from Google
here to introduce you to the newest
version of the Google Sheets API launched at Google I/O 2016.
In this episode, we're going to show you
how to use a new v4 API by walking
through a simple script that populates
rows from a relational database into a Google Sheet.
Let's take a quick step back before getting started.
We know that spreadsheets have been a computing killer
app for decades, growing beyond the business world.
And for nearly as long, they have been programmable,
so it's no surprise it was one of Google's first APIs.
Spreadsheets are a great place for a structured data,
so it makes sense working with them programmatically,
because data is generally created
and may live outside of a spreadsheet,
but can be easily viewed and manipulated there.
While previous API versions like v3
were good at creating new sheets within existing spreadsheets,
writing new rows of data, and, of course, reading data,
that's pretty much all you could do.
Now that kind of API seems incomplete
when you consider that spreadsheets
are way more than that.
With that said, we're excited to announce
we've launched a new version that can do much more.
Think of what you can do from a spreadsheet user's interface.
An API should allow your code to do most of the stuff
that you can do with sheets on mobile or the desktop.
That's the real power of v4-- the ability to do way more,
yet still keep things simple for developers.
To get started with the API, we need a SQL database
that has data.
Our codelab has the perfect one, so click the link
and download the zip file in step 2.
Extract the db.SQLite life from the Start folder,
whose schema you see here, and you're ready to go.
Now, you can do the codelab when you're
ready for a deeper hands-on using JavaScript and Node,
and it will help that you already know its database.
Today, we're using Python.
Here's a preview of the database rows
we're going to push into the new Google sheet that
comes from that database.
To keep this a short and sweet intro,
there won't be any formatting this time.
We're just inserting the raw data.
Before we can go to the computer,
those new to using Google API should catch our intro video
on setting up new projects.
While today's script is in Python,
you can use any language supported by Google API's
client libraries.
Be sure to watch the common code walkthrough video,
because we're going straight to the API code you see here.
Once you've got a project with the sheets API enabled,
let's go to the computer and see how to use it.
As we just said, most everything through line 16
is part of the boilerplate, except
for the necessary imports we need
for this particular script.
And of course the scope on line 10.
Because we're using the sheets API,
we need to have the necessary scopes here.
Also, make sure you have the sheets
API enabled in the dev console.
Otherwise, none of this is going to work.
All right, let's move straight to the API code.
That's on line 18 through 22.
So on line 18 is where we get the service
endpoint for Google Sheets, so you can talk to the API.
And then we use the spreadsheets.create function
to create the spreadsheet with a bare minimum of information--
just a title with a time stamp in the request body.
And what you get back is a spreadsheet ID, which we save,
and then let the user know on line 22
that it has been created.
Now let's turn our attention to the actual database.
So in the next chunk of code, lines 24 through 31,
we're not going to touch the sheets API.
We're just going to grab the data that we have
sitting in the SQLite database.
So you can see just a bunch of field names.
We make the connection to the database, grab a cursor,
execute our select to grab all the rows,
and then insert the headers in front of the rows,
so that's what insert 0 is.
And then the last step is to basically assign
this to the JSON payload that we're
going to pass through the API, but we're
going to strip out the last two columns, which are the created
date and the update date.
Now that we have the data from the database
and we have a blank spreadsheet, the next step
is to add all those values to the spreadsheet.
And the way you do that is by using
spreadsheets.values.update, pass in the spreadsheet ID,
tell it what's the first cell that the data is going
to go into, the data in the request body,
and then this value input option, which
we use as raw here.
There's another option called User Entered.
User Entered is for the situation where you're
going to pretend the user actually sat at the keyboard
with a sheet in front of them and are typing in the values.
The difference there is that if the cells
have been pre-formatted for numbers, or strings,
or have formulas, then all those things
will take effect, versus the raw data, which is just
throwing in the values without any concern for what
kind of functionality is already in the sheet.
So once that's done, we have a print statement
to tell the user that the data has been sent to the sheet.
And then just to confirm, we're going
to call spreadsheets.values.get and pull out
all the values that we wrote in, and then we print that out
to the user.
All right, now let's go to the command line and try it out.
But before we do that, let's double check what
is in the database.
See the schema, you can confirm that,
and then we can see what rows we have.
All right, so we have five rows.
These are the same five rows that should go into the sheet
when we run our Python script, and that we're
going to dump out on the command line because of print
statements.
So there's multiple ways of confirming
that it read the right data.
Now let's run the script.
You can run this script in Python 3 or Python 2,
it doesn't matter.
The code that we wrote is compatible with both.
So the first thing you should see
when you run the script is OAuth, and what happens
is your browser window will pop up
saying that this script would like to have access
to view and manage your spreadsheets in Google Drive,
which we want, so click Allow.
And then down below, you'll see the output of the code,
because it runs pretty fast.
All right, so it created the spreadsheet here,
and then wrote this data, which we read in down here.
Now, that's great that we can see the output of what's
supposed to be in our sheet.
But how do we really know we have a sheet
and this isn't just some fake demo?
Well, there's no better place to check than your Google Drive
and see that you really did create a new sheet right here.
So double-click it, and look, it's the same data.
And if we close this and our OAuth window
and run the same script again, and boom, another one.
And if you click on that, it's got the same data, too.
So one last thing.
You may be wondering why we had to create a blank sheet,
read the data from the database, and then call update
to throw the values in there, and wondering if it's possible
whether or not we can do all of that with just the call
to create.
And the answer is yes, but it's a little bit more complicated
than that, because when you use create,
you have to pass in the JSON structure
for every single cell instead of being able to provide basically
a list of lists of just the data.
So the best practice is to use the values update
method instead.
So congratulations, you just wrote your very first script
using the Google Sheets API.
So that's it.
You now know how to use the new API
to get data into and out of a Google sheet
with not too many lines of code.
Look for an upcoming episode where
we format sheets created by today's script.
Check out the official docs you see at the first link.
There, you'll find everything you need to get started.
I'd also recommend the samples page
to get a better idea of the common operations
in the corresponding JSON payloads you
need to send to the API.
Finally, the links down at the bottom
will get you to more developer videos
in this and other series.
The Google Sheets engineering team
is proud to introduce their next generation API.
Now it's your chance to build new apps you weren't
able to do before, plus update those existing apps to take
advantage of all the new features.
Be sure to let us know how it goes in the comments below.
And don't forget to subscribe to our channel.
This is Wesley Chun from Google, and we'll
see you the next time on the Launchpad Online.