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

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

---

(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.

---

I don't think this can be done currently. The current APIs (both the List and Cell API) allow changing data, but not formatting.

The entire APIs are described here. Nothing about formatting:

• http://code.google.com/apis/spreadsheets/data/3.0/reference.html
• http://code.google.com/apis/documents/docs/3.0/reference.html

Many people requesting this in the groups but never get an answer from Google:

• http://groups.google.com/group/Google-Docs-Data-APIs/browse_thread/thread/14aef72447ba48ce/9c2143fb4c8a3000?lnk=gst&q=color#9c2143fb4c8a3000
• http://www.mail-archive.com/google-docs-data-apis@googlegroups.com/msg02569.html

---

This helped for me enormously: https://cloud.google.com/blog/products/application-development/formatting-cells-with-the-google-sheets-api

---

Google Apps Script looks like it might bring some of this functionality, in the Range class specifically:

https://developers.google.com/apps-script/reference/spreadsheet/range

Importantly, I have not figured out how to bind (and/or execute) a Google Apps Script to the sheet that I'm currently creating and filling using the Google Drive API and Google Sheets API.

I'm not suggesting porting your app to Google Apps Script, but I'm seriously considering it myself at this point. I'm hoping maybe someone else has some thoughts on the last missing piece of hooking the API up with the Google Apps Script.