Skip to content
Google Sheets Connection
  • AI Chat
  • Code
  • Report

    • Google Sheets Connection

    This recipe can be used when you have data stored in a Google Sheet. There are two ways you can connect your datasource to this notebook: using an API key or using OAuth2.0. If you don't mind to put your spreadsheet public to everyone who has access to the link, using an API key is the easiest way to go. If you want to be more secure, you can choose to use OAuth2.0. This, however, requires a bit more work on your end.

    %%capture
# Install Google packages
!pip install --upgrade google-api-python-client google-auth-httplib2 google-auth-oauthlib
# Always reload utils.py
%load_ext autoreload
%autoreload 2
    # Import packages
import pandas as pd
import utils
import os

    Using an API key

    Follow these steps:

    1. Generate an API key using these instructions.
    2. Open the spreadsheet you want to access from this notebook, make it public to everyone with the link and copy the <spreadsheetId> from the URL that has following format: docs.google.com/spreadsheets/d/<spreadsheetId>/.
    3. Put both your API key and spreadsheetId as environment variables in the integrations tab with key GSS_API_KEY and SPREADSHEETID as key respectively. You can choose the integration name. Connect your integration to this workspace.
    # This is the sheet you want to access. You can be more specific by providing a certain range within that sheet.
RANGENAME = 'Sheet1'

try:
    # Access and read the specified spreadsheet using the API key
    df = utils.read_spreadsheet_api_key(os.environ['SPREADSHEETID'], os.environ['GSS_API_KEY'], RANGENAME, include_col_names=True)
    display(df.head())
except Exception as e:
    print('Please provide the right environment variables first.')

    Using OAuth2.0

    Follow these steps:

    1. Create a credentials.json file by following these instructions.
      ! Be sure to select Desktop App as the Application Type.
    2. Upload the credentials file to this notebook and change the name in the notebook appropriately.
    3. Open the spreadsheet you want to access from this notebook and copy the <spreadsheetId> from the URL that has following format: docs.google.com/spreadsheets/d/<spreadsheetId>/.
    4. Put your spreadsheetId as an environment variable with key SPREADSHEETID in the integrations tab. You can choose the integration name. Connect your integration to this workspace.
    # This is the sheet you want to access. You can be more specific by providing a certain range within that sheet.
RANGENAME = 'Sheet1'
try:
    # Access and read the specified spreadsheet using the API key
    df = utils.read_spreadsheet_oauth(os.environ['SPREADSHEETID'], RANGENAME, creds_file="credentials.json", include_col_names=True)
    display(df.head())
except Exception as e:
    print('Please provide the right environment variables first and allow access to your Google Sheet.')