Articles

Formatting cells with the Google Sheets API (The G Suite Dev Show)


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

Leave a Reply

Your email address will not be published. Required fields are marked *