3

I wish to read a Google sheet to pandas. There are number of questions already answered on this topic, but all of the answers (using gspread, basically) require credentials. Here I don't have (or want to have) the credentials, but have a shared link opened for view.

I can even download the buffer (as HTML) with urllib.request

So, given a shared link, or a buffer, but no credentials, how can I parse the Google sheet to a Python data structure?

Thank you,

Ori5678
  • 499
  • 2
  • 5
  • 15

1 Answers1

2

You can use any of the following endpoints.

/export endpoint:

  • Direct endpoint url

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

  • Export links returned by File:get in the drive api.

    https://docs.google.com/spreadsheets/export?id=[SPREADSHEET_ID]&exportFormat=[FORMAT]

Google vizualization endpoint:

  • Endpoint: /gviz/tq

  • Export parameters: ?tqx=out:(FORMAT)

  • Sample url:

    https://docs.google.com/spreadsheets/d/[SPREADSHEET_ID]/gviz/tq?tq=(URL_ENCODED_QUERY)&sheet=(SHEETNAME)&range=(A1NOTATION)&tqx=out:(FORMAT)

where

  • SPREADSHEET_ID is your spreadsheet id
  • FORMAT is one of CSV,PDF(html,json supported only in gviz/tq endpoint;ods,xlsx,zip supported only in drive export endpoints)
  • SHEET_ID is sheet id as seen in the url #gid=SHEET_ID
  • SHEET_NAME - Name of the sheet
  • A1NOTATION - Range string likeA1:G5

() suggests optional parameters and [] suggests required parameter.

Typically, you'd use csv as export format to get the file and use pandas.readcsv(), but json, html and xlsx are just as valid

TheMaster
  • 45,448
  • 6
  • 62
  • 85