How do i access a public google spredsheet into csv panda dataframe?

Question

url: https://docs.google.com/spreadsheets/d/e/2PACX-1vSz8Qs1gE_IYpzlkFkCXGcL_BqR8hZieWVi-rphN1gfrO3H4lDtVZs4kd0C3P8Y9lhsT1rhoB-Q_cP4/pubhtml

import pandas as pd 
data = pd.read_csv('https://docs.google.com/spreadsheets/d/e/2PACX-1vSc_2y5N0I67wDU38DjDh35IZSIS30rQf7_NYZhtYYGU1jJYT6_kDx4YpF-qw0LSlGsBYP8pqM_a1Pd/pub?output=csv',usecols=[2,4,5,6,7,8])
df = pd.DataFrame(data)
df.columns = ['Date', 'Age','Gender','City','District','State']
df.to_csv('dataset.csv')

i got the following error for this code

  File "C:\Users\Anaconda3\lib\site-packages\spyder_kernels\customize\spydercustomize.py", line 786, in runfile
    execfile(filename, namespace)

  File "C:\Users\Anaconda3\lib\site-packages\spyder_kernels\customize\spydercustomize.py", line 110, in execfile
    exec(compile(f.read(), filename, 'exec'), namespace)

  File "C:/Users/Desktop/Data Science/Machine Learning/Day2/covdata.py", line 2, in <module>
    data = pd.read_csv('https://docs.google.com/spreadsheets/d/e/2PACX-1vSc_2y5N0I67wDU38DjDh35IZSIS30rQf7_NYZhtYYGU1jJYT6_kDx4YpF-qw0LSlGsBYP8pqM_a1Pd/export?output=csv',usecols=[2,4,5,6,7,8])

  File "C:\Users\Anaconda3\lib\site-packages\pandas\io\parsers.py", line 702, in parser_f
    return _read(filepath_or_buffer, kwds)

  File "C:\Users\Anaconda3\lib\site-packages\pandas\io\parsers.py", line 413, in _read
    filepath_or_buffer, encoding, compression)

  File "C:\Users\Anaconda3\lib\site-packages\pandas\io\common.py", line 202, in get_filepath_or_buffer
    req = _urlopen(filepath_or_buffer)

  File "C:\Users\Anaconda3\lib\urllib\request.py", line 222, in urlopen
    return opener.open(url, data, timeout)

  File "C:\Users\Anaconda3\lib\urllib\request.py", line 531, in open
    response = meth(req, response)

  File "C:\Users\Anaconda3\lib\urllib\request.py", line 641, in http_response
    'http', request, response, code, msg, hdrs)

  File "C:\Users\Anaconda3\lib\urllib\request.py", line 569, in error
    return self._call_chain(*args)

  File "C:\Users\Anaconda3\lib\urllib\request.py", line 503, in _call_chain
    result = func(*args)
File "C:\Users\Anaconda3\lib\urllib\request.py", line 649, in http_error_default
    raise HTTPError(req.full_url, code, msg, hdrs, fp)

HTTPError: Not Found

Hello @novmbr29, are you able to retrieve the Sheet as a CSV? If so, how are you doing it? You might want to check [this](https://stackoverflow.com/a/50802790/11772035) as well. Cheers! — ale13, Apr 17 '20 at 08:58

score 0 · Answer 1 · answered Apr 16 '20 at 16:22

0

To access google sheets from python you can use: gspread: https://gspread.readthedocs.io/en/latest/

answered Apr 16 '20 at 16:22

juanblo

99
6

score 0 · Answer 2 · answered Apr 16 '20 at 16:24

It's a litte more complicated than this because we have to do a API with Google Sheets specifying your Google Account Credentials. Thus, we won't get the table with your method. If you want build your own API, take a look into references:

https://developers.google.com/sheets/api/quickstart/python

Or, as mentioned, use gspread lib:

https://github.com/burnash/gspread

How do i access a public google spredsheet into csv panda dataframe?

2 Answers2