google-sheetsgoogle-sheets-apigoogle-colaboratoryflysystem-google-drive

How to read google spreadsheet using google colab

We can list out spreadsheet present in google drive using below command

 from google.colab import drive
 drive.mount('/content/drive')
 !ls -l /content/drive/'Shared drives'

but unable to read spreadsheet using below command 

import gspread
from oauth2client.client import GoogleCredentials

gc = gspread.authorize(GoogleCredentials.get_application_default())

gc.open('/content/drive/'Shared drives/data.gsheet').data available

and also one more problem we have space in sheetname(data available) and we don't have access to change sheetname

I have refer link:: https://colab.research.google.com/notebooks/io.ipynb
Kindly help on it.

Solution

  • You can use gc.open_by_url('gsheets_url') to open the document (no need to mount the drive). For the sheet name, you can use gsheets.worksheet('sheet name').

    So on your case it'd go something like:

    from google.colab import auth
auth.authenticate_user()
import gspread
from oauth2client.client import GoogleCredentials

# setup
gc = gspread.authorize(GoogleCredentials.get_application_default())

# read data and put it in a dataframe
gsheets = gc.open_by_url('your-link')
sheets = gsheets.worksheet('data available').get_all_values()
df = pd.DataFrame(sheets[1:], columns=sheets[0])

    (credits to this post)