1

I am trying to read a Google Sheet using pandas pd.read_csv(), however when the columns contain cells with text and other cells with numeric values, the text is not read. My code is:

def build_sheet_url(doc_id, sheet_id):
    return r"https://docs.google.com/spreadsheets/d/{}/gviz/tq?tqx=out:csv&sheet={}".format(doc_id, sheet_id)

sheet_url = build_sheet_url(doc_id, sheet_name)
df = pd.read_csv(sheet_url)



> df
   Column1  Column2
0       12       21
1       13       22
2       14       23
3       15       24

This is what the spreadsheet looks like:

Spreadsheet screenshot

I have tried using dtype=str and dtype=object but could not get the text to show in my dataframe. Specifying the encoding encoding='utf-8' did not work either.

TheMaster
  • 45,448
  • 6
  • 62
  • 85
NDen
  • 23
  • 6

1 Answers1

0

This is because query doesn't support mixed data types:

Data type. Supported data types are string, number, boolean, date, datetime and timeofday. All values of a column will have a data type that matches the column type, or a null value. These types are similar, but not identical, to the JavaScript types.

Use the /export end point(or drive-api endpoint instead):

https://docs.google.com/spreadsheets/d/[SPREADSHEET_ID]/export?format=[FORMAT]&gid=(SHEET_ID)&range=(A1NOTATION)

Related:

TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • When using `&gid=(SHEET_ID)` I get a `HTTPError: Bad Request` but replacing by `&sheet=(SHEET_ID)` solved the problem. Thanks @TheMaster! – NDen Oct 06 '22 at 09:23
  • @NDen Consider accepting the answer by clicking the checkmark on the left of this post. Regarding (SHEET_ID), it needs to be a actual sheetId. You can see the url: `&gid=(SHEET_ID)` – TheMaster Oct 06 '22 at 09:36