Loading data from Google Sheets

Three methods for loading data from google sheets into a Colab Notebook

Method 1 (Public CSV)

If you’re willing to make your spreadsheet public, you can publish it as a CSV file on Google Sheets. Go to File > Publish to the Web, and select the CSV format. Then you can copy the published url, and load it in python using pandas.

import pandas as pd
df = pd.read_csv(url)

Method 2 (OAuth)

This method requires the user of the colab to authorize it every time the colab runs, but can work with non-public sheets

# Authentication
import google
google.colab.auth.authenticate_user()
google_sheets_credentials = GoogleCredentials.get_application_default()
gc = gspread.authorize(google_sheets_credentials)

# Load spreadsheet
wb = gc.open_by_url(url)
sheet = wb.worksheet(sheet)
values = sheet.get_all_values()

Method 3 (Service Account)

This method requires your to follow the instructions at https://gspread.readthedocs.io/en/latest/oauth2.html to create a google service account. You then need to share the google sheet with the service account email address.

# Need a newer version of gspread than included by default in Colab
!pip install --upgrade gspread

service_account_info = {} #JSON for google service account
import gspread
from google.oauth2.service_account import Credentials

scope = ['https://spreadsheets.google.com/feeds',
        'https://www.googleapis.com/auth/drive']

credentials = Credentials.from_service_account_info(service_account_info, scopes=scope)

gc = gspread.authorize(credentials)

# Load spreadsheet
wb = gc.open_by_url(url)
sheet = wb.worksheet(sheet)
values = sheet.get_all_values()