Set cell format in Google Sheets spreadsheet using its API & Python

Question

I am using gd_client.UpdateCell to update values in a google spreadsheet and am looking for a way to update the formatting of the cell i.e. color, lines, textsize.....

Can this be done?

Are there examples or other documentation available?

Anything to get me going would be great

Answer 1

(Feb 2017) As of Google I/O 2016, developers can now format cells in Google Sheets using the latest API (v4). Here's a short Python example that bolds the 1st row (assuming the file ID is SHEET_ID and SHEETS is the API service endpoint):

DATA = {'requests': [
    {'repeatCell': {
        'range': {'endRowIndex': 1},
        'cell':  {'userEnteredFormat': {'textFormat': {'bold': True}}},
        'fields': 'userEnteredFormat.textFormat.bold',
    }}
]}

SHEETS.spreadsheets().batchUpdate(
        spreadsheetId=SHEET_ID, body=DATA).execute()

I also made a developer video on this subject if that helps (see below). BTW, you're not limited to Python, you can use any language supported by the Google APIs Client Libraries.

The latest Sheets API provides features not available in older releases, namely giving developers programmatic access to a Sheet as if you were using the user interface (frozen rows, cell formatting[!], resizing rows/columns, adding pivot tables, creating charts, etc.). If you're new to the API, I've created a few videos with somewhat more "real-world" examples:

• Migrating SQL data to a Sheet plus code deep dive post
• Formatting cells using the Sheets API plus code deep dive post
• Generating slides from spreadsheet data plus code deep dive post

As you can tell, the Sheets API is primarily for document-oriented functionality as described above, but to perform file-level access such as import/export, copy, move, rename, etc., use the Google Drive API instead.