Python example of how to get formatting information from a cell in Google Sheets API v4?
<p>I've been trying to write my own Google Sheets wrapper, and it's been a frustrating experience so far.  The thing I'm stuck on at the moment is how to get a symmetrical in / out format of sheet data.</p>

<p>Basically, I want to call values().get(), alter the resulting hash, and send that same hash back up to update().</p>

<p>I'm happy to write my own solution to process or coerce the output of values().get() to the structure that batchUpdate() needs, but <strong>I need the formatting information of each of the cells to do that</strong>.</p>

<p>batchUpdate() expects formatting information like this:</p>

<pre><code>bod = {
    'updateCells': {
        'start': {
            'sheetId': 0,
            'rowIndex': 7,
            'columnIndex': 0
        },
        'rows': [
            {
                'values': [
                    {
                        "userEnteredValue": {
                            'stringValue': 'LOL'
                            }, 
                        "userEnteredFormat": {
                            'backgroundColor': {
                                'red': .2,
                                'blue': .75,
                                'green': .75
                            }
                        }
                    },
                    {
                        "userEnteredValue": {
                            'stringValue': 'LOL2'
                            }, 
                        "userEnteredFormat": {
                            'backgroundColor': {
                                'red': .2,
                                'blue': 1,
                                'green': .75
                            }
                        }
                    },
                    {
                        "userEnteredValue": {
                            'stringValue': 'LOL3'
                            }, 
                        "userEnteredFormat": {
                            'backgroundColor': {
                                'red': .2,
                                'blue': 1,
                                'green': 1
                            }
                        }
                    }                    
                ]
            }
        ],
        'fields': 'userEnteredValue,userEnteredFormat.backgroundColor'
    }
}
</code></pre>

<p>How I'm retrieving values currently looks something like this:</p>

<pre><code>import requests
import json
from oauth2client.service_account import ServiceAccountCredentials
from apiclient.discovery import build

#Set up credentials object
auth_key_url = "&lt;JSON CREDENTIALS FILE&gt;"
file_contents = requests.get(auth_key_url).content
key_dict = json.loads(file_contents)
creds = ServiceAccountCredentials.from_json_keyfile_dict(key_dict, ['https://spreadsheets.google.com/feeds'])

#Now build the API object
discoveryUrl = "https://sheets.googleapis.com/$discovery/rest?version=v4"
gsheet = build('sheets', 'v4', discoveryServiceUrl=discoveryUrl, credentials=creds)

result = gsheet.spreadsheets().values().get(spreadsheetId="&lt;A SHEET ID&gt;", range="Sheet1!A1:ZZ").execute()    
</code></pre>

<p>This produces "results", which is a dictionary with 2 keys, "range" and "values", and "values" is a list of lists of the values of the spreadsheet.  These lists do not contain formatting data - just the values in the cells.</p>

<p>Can someone show me, in Python, how I can get cell value, background color, alignment, and other cell formatting information from spreadsheets().values() or from the spreadsheet?</p>

<p>The <code>spreadsheets.values.get</code> endpoint only returns the values. If you want a more complete picture of the spreadsheet (formatting, etc) then you need to use the <code>spreadsheets.get</code> endpoint:</p>

<p><a href="https://developers.google.com/sheets/reference/rest/v4/spreadsheets/get" rel="nofollow">https://developers.google.com/sheets/reference/rest/v4/spreadsheets/get</a></p>

<p>Make sure to pass either <code>includeGridData=true</code> or pass a value for the  <code>fields</code> that includes <code>sheets.data</code> so that the cell data is returned. Pass a value in the <code>range</code> parameter to limit the results to only a specific range.</p>

python google-api-client google-sheets-api