CODEWITHSUNDEEP
python-3.xcsvdataframegoogle-sheets
iSiddharth
iSiddharth

Reputation: 163

How do I get a someone else's Google Spreadsheet to my Pandas Data Frame?

I have an Open-Source Live Data, but it's in the format of Google Spreadsheet and not CSV or JSON. How do I get this Live Spreadsheet's data in my Pandas Data Frame?

HERE is the link to that Open Source Live Spreadsheet.

Thank You

Upvotes: 0

Views: 321

Answers (1)

Tanaike
Tanaike

Reputation: 201378

  • You want to retrieve the values from the Web published Google Spreadsheet.
    • The URL is like https://docs.google.com/spreadsheets/d/e/###/pubhtml#.
  • You want to put the retrieved values to the dataframe.
  • You want to achieve this using Python.

In this answer, I would like to propose the following flow.

  1. Modify the endpoint. By this modification, the values can be retrieved as the CSV data.

    • From: https://docs.google.com/spreadsheets/d/e/###/pubhtml#
    • To: https://docs.google.com/spreadsheets/d/e/###/pub?output=csv

  2. Retrieve the values from the modified endpoint as the CSV data.

  3. Put the CSV data to the dataframe.

Sample script:

import io
import pandas as pd
import requests

url = 'https://docs.google.com/spreadsheets/d/e/2PACX-1vSc_2y5N0I67wDU38DjDh35IZSIS30rQf7_NYZhtYYGU1jJYT6_kDx4YpF-qw0LSlGsBYP8pqM_a1Pd/pub?output=csv'
res = requests.get(url)
df = pd.read_csv(io.BytesIO(res.content), sep=',')

Reference:

Upvotes: 1

Related Questions