gspread-pandas in Google Colaboratory

[glenn4s]

gspread- pandas does not appear to work in Google Colaboratory. It seems that there are issues with authentication - which we have been able to work around - as well as accessing spreadsheets once authentication is working.

[aiguofer]

Hey, thanks for raising the issue! I've never used colab, but I'd love to get it working on it. Would you mind sharing how you worked around it? I can play around with it this weekend. Or if you could do a PR that'd be awesome

[glenn4s]

I started with code that I have been using successfully in Jupyter notebooks on my MacBook.

The first problem was where to put the google_secret.json file so that gspread-pandas could find it. It's not clear to me what the file structure of the Colab virtual machine looks like and there is no clear analogy to root/ so I created a directory, put the google_secret.json file in it and used gspread_pandas.conf.get_config to set the path to the config directory.

This seemed to work partially (too complicated to explain here) but ultimately failed to find the credential. I then set the path to the config directory this way: spreadsheet = Spread('me' ,' FileName' , config = gspread_pandas.conf.get_config(conf_dir='/mydir/')) and then gspread-pandas successfully found the credentials.

Next problem was that gspread-pandas cannot find any of my Google sheets. I have passed Spread the name, URL and ID of sheets but always get "SpreadsheetNotFound". I have put the sheets in various places on My Drive: My Drive, the Colab Notebooks directory and other places but always get the SpreadsheetNotFound error.

When I run this same code locally, gspread-pandas finds the referenced sheets on my Google Drive without a problem.

Hope this helps and happy to provide anything else that might be helpful.

[tfbecker]

I have the same issue. Is there a way to get your library to work without the .json authentication?

[hamletbatista]

@aiguofer Here is an example Google Colab that does away with the .json authentication. It is too much work to follow all those steps.

This code uses Gspread directly. Can you please make the necessary changes to gspread-pandas to support this? https://colab.research.google.com/drive/1a3djGnFKW-auF6M8QMqRAtXt6ssz585p#scrollTo=hAkVGqP5Yrx4

Relevant code:

# (This is always confusing, but it works)
from google.colab import auth
auth.authenticate_user()
import gspread
from oauth2client.client import GoogleCredentials
gc = gspread.authorize(GoogleCredentials.get_application_default())

# get all data from the spreadsheet
worksheet = gc.open(spreadsheetName).sheet1
worksheetRows = worksheet.get_all_values() ```

Thanks in advance

[hamletbatista]

@HolabeFelixBecker @aiguofer I think we can support Colab with a very simple change. I created a pull request for your review https://github.com/aiguofer/gspread-pandas/pull/47

[aiguofer]

Hey, sorry for the delayed response on this. I merged the above PR, but wanted to point out that oauth2client is deprecated. I believe what you'd want is something like this (I haven't tested, nor used Google Colab):

import google.auth
from google.colab import auth
from gspread_pandas import Spread

auth.authenticate_user()
credentials, project_id = google.auth.default()

spread = Spread('Example Sheet', creds=credentials)

see: https://google-auth.readthedocs.io/en/latest/reference/google.auth.html#google.auth.default

[MSKDom]

Hey, sorry for the delayed response on this. I merged the above PR, but wanted to point out that oauth2client is deprecated. I believe what you'd want is something like this (I haven't tested, nor used Google Colab):

import google.auth
from google.colab import auth
from gspread_pandas import Spread

auth.authenticate_user()
credentials, project_id = google.auth.default()

spread = Spread('Example Sheet', creds=credentials)

see: https://google-auth.readthedocs.io/en/latest/reference/google.auth.html#google.auth.default

This should be added to the README section as an option since a lot of notebook users will try this