How to read google spreadsheet using google colab

Question

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.

Retail

Provide minimal reproducible example. Specifically, Describe the problem. "It doesn't work" isn't descriptive enough to help people understand your problem. Instead, tell other readers what the expected behavior should be. Tell other readers what the exact wording of the error message is, and which line of code is producing it. Use a brief but descriptive summary of your problem as the title of your question. – Barina

Answer 1

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)

Memorialize

Answer 2

Update to the answer by Murilo Cunha, as it gives errors for authentication

from google.colab import auth
auth.authenticate_user()

import gspread
from google.auth import default
creds, _ = default()

gc = gspread.authorize(creds)

import pandas as pd
# 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])

Difference

Answer 3

It's helpful if you could say what is the exact error you get when you run this script. However, I notice there are redundant quotes in following script,

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

replace extra above by following,

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

Another option is to try gc.open_by_url() instead of gc.open()

Apps