Search code examples
pandascsvgoogle-sheetsgoogle-visualizationgoogle-query-language

Text not read when using pd.read_csv() on a Google Sheet

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.

Solution

  • 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: