[MUSIC PLAYING]
WESLEY CHUN: Hi.
This is Wesley Chun, engineer here at Google.
Today we're going to show you how
to do something you've never been
able to do before-- format spreadsheet cells using Google
Sheets API.
Yeah, that's right.
You couldn't do this in older API releases.
And I've got a stack of Stack Overflow questions to prove it.
So I hope you're excited as I am.
To keep it short and sweet, I won't
be discussing reading or writing a Sheet, nor import export.
Those are covered in other episodes, one of which
is where we show you how to migrate toy orders from a SQL
database to a Google Sheet.
We're going to reuse the same Sheet created in that video.
So check it out and run that script first.
Check out the Sheet that it's created.
Doesn't that look totally bland?
Well, today you're going to learn
how to format those cells to make it more presentable.
What do you think?
Way better, right?
To get the sheet to look like this,
you have to send formatting commands to the API.
For this sample, there are four.
One, freeze the top row.
Two, bold the top row.
Three, format the prices in column E as financial values.
And then lastly, adding a fixed values and cell
validation for the order status in column F.
What do those commands look like?
Well, each request is a JSON structure represented here
in this pseudo code as a Python dictionary.
You can group as many of them as desired into an array,
like a Python list, and send them together in one
API call to spreadsheets.batchUpdate.
Let's look at each of the four requests for our Sheet
now, but more generically in JavaScript.
Frozen rows are a Sheet property.
So we need a command that updates Sheet properties.
After much research we decided to name
it updateSheetProperties.
More specifically, set the frozen row count attribute
of a Sheet's grid properties to freeze
the top n rows of the sheet.
For us, n is one.
Before moving on let's talk about Sheet
ranges because we need them for the remaining requests.
First, a range is made up of a Sheet ID and indexes.
Next, when you create a brand new spreadsheet,
the default Sheet that's created for you has an ID of zero.
Like most programming languages, counting
starts at zero, not one.
So here, you can see column names and row numbers
and mentally convert them to zero-based indexes.
Also, ranges are exclusive of all end indexes.
That means that they go up to but do not
include the end index value.
Lastly, know that you can leave out
an index when you want to take the default.
We'll discuss each situation as it comes up.
Time for a quiz.
See the blue highlighted range in the Sheet?
Now, this range JSON object you see here?
Well, what should we fill in for each value?
We'll start with the Sheet ID, assuming
this is the default Sheet.
That's right.
The ID is 0.
In fact, if you know you're accessing this default Sheet,
you can leave out the Sheet ID completely.
The range starts at column D, which is at index zero, one,
two, three.
It ends at column E, which is four.
But since the end index is excluded,
we need to bump that up to five.
Similarly, the starting row is three, but that's at index two.
Finally, it ends at row four, which is at three.
But we need to bump that up to four.
Good job.
Now you know how to set up ranges.
To see more examples and learn more about grid ranges,
see the documentation.
Now we can talk about the other three requests,
starting with bolding that frozen row.
The verb is repeat cell, meaning apply this format
to all cells in a given range, which
is the first row in the first Sheet.
Now that you know all about ranges,
you can see that these are the correct values representing
the first row.
However, we recommend you drop both column indexes.
Hard coding columns is a bad idea
because if you add more columns they're not going to be bolded.
By leaving them out the entire row
will be bolded, regardless of the number of columns.
If you're accessing the first default
Sheet, meaning its ID is zero, you
can leave off the Sheet ID too.
Finally, all start indices default to zero,
so we can also drop start row index.
The only one you really need is endRowIndex.
Next is what you want changed in the cells.
In our case, it's the textFormat.
Specifically, its bold property, toggling it to True.
Finally, Fields specifies which field should be effected.
Think of it like a bitmask or a field mask.
Here we don't want to change any existing
formatting other than the cell's bold setting.
For example, if you only had user entered format without
the .textFormat.bold as your Fields element,
you're going to lose things like the cell's background color,
the vertical alignment, and other properties.
Check our docs to see other formatting examples,
as well as to find out more about field masks.
Now let's format the toy unit costs in column E. For range,
I left off the Sheet ID because we know it's zero.
We're formatting rows two through six,
meaning start and end indexes of one and six.
But we should drop the endRowIndex for the same reason
as described before-- to bold all of the first row.
Instead of omitting the endColumnIndex,
we leave off the endRowIndex so as to format
the rest of the column, even if new rows are added.
Column E indexes are four and five, and both are needed.
Now, we're changing the user entered format also.
But this time, it's number format instead of text format,
giving the US Dollar currency format
with a dollar sign, comma separated thousands, and two
decimal places.
The Fields mask is what you expect, changing only
the cell's number format.
Getting easier?
All right.
More on date, time, and number formats
can be found in the documentation.
The last formatting request is setDataValidation,
adding fixed fields for toy order status and cell
validation of those values.
Range is as before, but now for column F.
As before, we want to format the entire column so
leave off the endRowIndex.
The rule is that the cell should or must
contain one of a list of valid values pending
shipped or delivered.
Should means weak enforcement, while must
is strict enforcement.
That means that if a user enters something other than these,
you'll get an alert like this.
Enforcement is controlled by the strict variable, which
if you omit defaults to false, which we did here
and in the Python script.
Use the inputMessage attribute if you want a custom message
when users go to the cell.
It's not important to me, so I'm leaving it
out using the system default.
If you choose strict enforcement with inputMessage
set to what we have here and uncommented,
users will get this message.
If you take the default, like I did,
users will get this dialog text instead.
The showCustomUi flag causes a pull-down list of options
to be displayed.
Now, it's a pretty poor experience without it
because users are not going to know
what available choices are, so I recommend
you always use it too.
OK, that's it.
Now you know how to format cells using the Google Sheets API.
For a change, we won't be going to the computer
today since constructing the JSON payload makes up
most of our short, 70-line Python script.
If you take a look at the code deep dive,
you can confirm that, other than the boilerplate
and a single call to the API, all the heavy lifting
is done in constructing these four requests, which
consist of a Python list of dictionaries.
Check out the first link to see more on JSON payloads,
specifically common operations that you will do with the API.
The second link takes you to the Sheets API
concepts, quickstarts, and other guides to help you get started.
The last link takes you directly to the reference docs.
Once you get this sample working and want another challenge,
use the API to create a Column G with the total cost
header in cell G1.
Set the cell G2 with the formula to calculate
the costs based on the toys ordered and cost in columns D
and E. And then use the Autofill command to copy
that formula down Column G. Hint-- you only
need the range attribute.
More on this challenge in the code review post
mentioned earlier.
We hope you learned a lot about formatting with the Sheets
API in this video and have gotten
a taste of how much more powerful
this API is compared to previous versions.
Now you can build those awesome apps
that you weren't able to do in the past.
This is Wesley Chun from Google reminding
you to subscribe to our channel and leave any comments below.
Have fun with the Sheets API and we'll see you next time.
[MUSIC PLAYING]