How to use ValueRenderOption in google-api-python-client for google sheets

Question

Here is a code snippet:

import ast
from oauth2client import file, client, tools
from oauth2client.service_account import ServiceAccountCredentials
from httplib2 import Http
from apiclient.discovery import build
from apiclient import discovery

SCOPE = 'https://www.googleapis.com/auth/spreadsheets'
CREDJSON = "some-cred-file-downloaded-from-dev-console.json"

def google_credentials(jsoncred=CREDJSON, scope=SCOPE):
    return ServiceAccountCredentials.from_json_keyfile_name(jsoncred, scope)


def csv_arrays(creds, key):

    (SHEETS, sheets) = sheets_fetch(creds, key)

    wks = sheets
    def create_filename(arg):
        filename = key.prefix + "_" + arg.get("properties, {}).get("title","Sheet1")
        filename = filename.replace(" ", "_")
        print (filename)
        return filename
    return [(create_filename(ws), ast.literal_eval(repr(SHEETS.spreadsheets().values().get(spreadsheetId=key.key, range=(ws.get("properties", {}).get("title", "Sheet1"))).execute().get('values',[])))) for ws in wks] #.decode("utf-8")

def sheets_fetch(creds, key):
    print('Now doing:', key)
    SHEETS = build('sheets', 'v4', http=creds.authorize(Http()))
    sheet_metadata = SHEETS.spreadsheets().get(spreadsheetId=key.key).execute()
sheets = sheet_metadata.get('sheets', '')

    return (SHEETS, sheets)

I have no problems getting the content of the cells. My problem is that they come formatted (as this is the default value) and I want them UNFORMATTED?

How do I set the option in the query of the return statement from the csv_array function?

https://developers.google.com/sheets/reference/rest/v4/ValueRenderOption

As a side note, is there a way to get the cells value in other way than as a string ast-ed to a list?

Sam Berlin · Accepted Answer · 2016-08-10 02:46:07Z

6

To set a valueRenderOption of UNFORMATTED_VALUE with the python client, use:

SHEETS.spreadsheets().values().get(spreadsheetId=key.key, range=myRange, valueRenderOption='UNFORMATTED_VALUE').execute()

Additionally, when using UNFORMATTED_VALUE, the response will be typed as it is in Sheets (e.g, numbers will be numbers, bools will be bools, strings will be strings). Dates are either strings or numbers, depending on DateTimeRenderOption, see the Date/Time Serial Numbers Guide for more information on dealing with the dates as numbers.

Because the values are already typed properly, you shouldn't need to do any additional parsing with ast or repr.

edited Aug 10, 2016 at 2:46

answered Aug 9, 2016 at 19:18

Sam Berlin

3,80316 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sebastien Over a year ago

The first version works. I need to remove both the repr and ast to make "get the data typed as its actual type, instead of always as a string" can you update you answer so I can accept it?

Collectives™ on Stack Overflow

How to use ValueRenderOption in google-api-python-client for google sheets

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related