Set the format of cells in a spreadsheet using its API and Python

Question

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

Can this be done?

Are there any examples or other documentation?

Whatever I go would be great

Answer 1

I do not think that this can be done at present. Existing APIs (both the list API and the kernel) allow you to modify data, but not format it.

All 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 request this in groups, but do not receive a response 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/ [e-mail] /msg02569.html

Answer 2

(February 2017) Starting with Google I / O 2016 , developers can now format cells in Google Sheets using the latest API (v4). Here's a short Python example that highlights the 1st line (assuming the file identifier SHEET_ID and SHEETS are 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 are not limited to Python, you can use any language supported by the Google API client libraries .

The latest Sheets API provides functions not available in older versions, namely providing developers with programmatic access to the Sheet as if you were using the user interface (frozen rows, formatting cells [!], Resizing rows / columns, adding pivot tables, creating charts and etc.). If you are new to the API, I created some videos with a few more “real” examples:

• Migrate SQL data per sheet plus deep dive post code
• Formatting text using the sheet APIs plus deep immersion of post code
• Create slides from spreadsheet data plus deep dive post code

As you can say, the table API is mainly intended for document- oriented functionality, as described above, but for executing a file- level access, such as import / export, copy, move, rename, etc. instead of the Google Drive API .

Answer 3

Google Apps Script looks like it can use some of these features in the Range class:

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

It’s important to note that I didn’t understand how to link (and / or execute) Google Apps Script with the sheet that I am creating and filling out using the Google Drive API and the Google Sheets API.

I do not suggest porting your application to Google Apps Script, but I am seriously considering it myself at this point. I hope that maybe someone else has thoughts about the last missing element of connecting the API with Google Apps Script.