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:
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.
CodePudding user response：
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)