7

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

wescpy
  • 10,689
  • 3
  • 54
  • 53
Vincent
  • 1,579
  • 4
  • 23
  • 38

4 Answers4

11

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

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.

wescpy
  • 10,689
  • 3
  • 54
  • 53
6

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:

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

wump
  • 4,277
  • 24
  • 25
  • I was hoping it was as simple as setting the formatting variable and getting the put url. Hopefully someone knows what we don't – Vincent May 09 '10 at 17:54
  • 1
    [Here](http://code.google.com/a/google.com/p/apps-api-issues/issues/detail?id=37) is a request for this created in 2007. It's currently marked as WontFix. – Roman Starkov Dec 04 '12 at 03:14
1

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

Starwave
  • 2,352
  • 2
  • 23
  • 30
0

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.

Timothy Johns
  • 1,075
  • 7
  • 17