Search code examples
pythongoogle-sheetsgspread

Gspread is retrieving all strings from a Google Sheet named range even when some cells are numbers

When I use Gspread to open a Google Sheet and retrieve a named range, what is returned is a list of lists where every data point is a string. Here is a sample sheet:

Google Sheet sample

The cells a1:e5 are a named range called NamedRange1.

I can import NamedRange1 as follows:

wks = gc.open("Gspread_test").sheet1
data_range = wks.get("NamedRange1") # arrives as list of lists

What appears in Python, although the source is a mix of strings and numbers, is a list of lists that is all strings:

[['Able', 'Baker', 'Charlie', 'Delta', 'Echo'],
 ['Blue', 'New York', '3', '', '100'],
 ['Green', 'New Jersey', '4', '5', '100'],
 ['Red', 'New York', '', '6', '100'],
 ['Orange', 'New Jersey', '7', '', '100']]

How can I retrieve the data from Google Sheets so that numbers come in as numbers (and strings as strings)?

Solution

  • Based on spreadsheets.values.get, you can configure valueRenderOption on how values should be represented in the output.

    enter image description here

    Hence, If you want to read the range values as int, set valueRenderOption to UNFORMATTED_VALUE. (Default render option is FORMATTED_VALUE)

    data_range = wks.get("NamedRange1", value_render_option='UNFORMATTED_VALUE')

    Additional Reference: Gspread get(range_name=None, **kwargs)