5

I am trying to read a google sheet using python using the gspread library.

The initial authentication settings is done and I am able to read the respective sheet.

However when I do

sheet.get_all_records()

The column containing numeric like values (eg. 0001,0002,1000) are converted as numeric field. That is the leading zeroes are truncated. How to prevent this from happening?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jithin P James
  • 752
  • 1
  • 7
  • 23

2 Answers2

3

You can prevent gspread from casting values to int passing the numericise_ignore parameter to the get_all_records() method.

You can disable it for a specific list of indices in the row:

# Disable casting for columns 1, 2 and 4 (1 indexed):
sheet.get_all_records(numericise_ignore=[1, 2, 4])

Or, disable it for the whole row values with numericise_ignore set to 'all' :

sheet.get_all_records(numericise_ignore=['all'])
pierreben
  • 239
  • 3
  • 12
1

How about this answer? In this answer, as one of several workarounds, get_all_values() is used instead of get_all_records(). After the values are retrieved, the array is converted to the list. Please think of this as just one of several answers.

Sample script:

values = worksheet.get_all_values()
head = values.pop(0)
result = [{head[i]: col for i, col in enumerate(row)} for row in values]

Reference:

If this was not the direction you want, I apologize.

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • Thank you, this helped. Atleast my purpose is solved – Jithin P James Oct 15 '19 at 11:16
  • @Jithin P James Thank you for replying. At gspread, I think that this workaround can be used. But I apologize that I couldn't propose the direct solution for resolving your issue. – Tanaike Oct 15 '19 at 22:09
  • @Tannaike - I have raised this issue in github, hopefully it will be fixed. https://github.com/burnash/gspread/pull/598#issuecomment-542177794 – Jithin P James Oct 16 '19 at 06:09
  • 1
    @Jithin P James Thank you for replying. `get_all_records()` retrieves the values using `get_all_values()`. The retrieved data is converted to the number with `int()` in the function of `numericise()`. By this, `0001` is converted to `1`. So for example, when `value = int(value)` is modified to `value = value if type(value) is str else int(value)` at [this line](https://github.com/burnash/gspread/blob/6970ca80371470bdf69979a49681747a22c07465/gspread/utils.py#L76), your goal can be achieved. But I'm not sure whether this modification is useful for other users. So I couldn't propose this. – Tanaike Oct 16 '19 at 07:06