Skip to content

Google Sheets Connector Tutorial

Jump to bottom
Christopher Greening edited this page Jul 22, 2021 · 1 revision

The purpose of this tutorial is to show example code that downloads and uploads spreadsheets to Google Sheets using the connectors provided by route1.io.

Table of Contents

Prerequisites

  • route1io_connectors installed
  • Credentials
    • Refresh token
    • Client ID via Google Cloud Platform
    • Client secret via Google Cloud Platform

Importing the connector

All Google Sheets connectors can be found in the route1io_connectors.gsheets module

from route1io_connectors import gsheets

Connecting to Google Sheets with credentials

Before we can interact with Google Sheets via Python we have to use our refresh token to get a valid access token. Normally we could do this via a POST request to the Google OAuth 2 endpoint https://accounts.google.com/o/oauth2/token but we've written a convenient function that handles that for us.

REFRESH_TOKEN = "YOUR_GOOGLE_REFRESH_TOKEN"
CID = "YOUR_CID"
CSC = "YOUR_CSC"
gsheets_credentials = gsheets.get_gsheets.credentials(
    refresh_token=REFRESH_TOKEN,
    cid=CID,
    csc=CSC
)

Now that we have a Credentials object, we can create our connection to Google Sheets.

gsheets_connection = gsheets.connect_to_gsheets(credentials=gsheets_credentials)

Uploading data from a local spreadsheet to Google Sheets

With our gsheets_connection, we're now able to easily upload a local CSV to Google Sheets.

Parameters:

  • gsheets_conn: Connection to Google Sheets that we instantiated with gsheets.connect_to_gsheets
  • filename: Filepath of the CSV we want to upload from
  • spreadsheet_id: ID of the spreadsheet we have permission to upload to (see below)
  • spreadsheet_name Name of the specific sheet we want to upload to

Finding the ID of the spreadsheet is easy, just look at the URL of the spreadsheet and you'll see it listed as a long string of characters like so: https://docs.google.com/spreadsheets/d//edit#gid=0

gsheets.upload_gsheets_spreadsheet(
    gsheets_conn=gsheets_connection,
    filename="your_data.csv",
    spreadsheet_id="THIS-IS-THE-SPREADSHEETS-ID",
    spreadsheet_name="your_data_sheet"
)

Downloading data from Google Sheets to a local CSV

With our gsheets_connection, we're also able to easily download a remote file from a Google Sheet to our local machine.

Parameters:

  • gsheets_conn: Connection to Google Sheets that we instantiated with gsheets.connect_to_gsheets
  • filename: Filepath of the CSV we want to download to
  • spreadsheet_id: ID of the spreadsheet we have permission to download from
  • spreadsheet_name Name of the specific sheet we want to download from
gsheets.download_gsheets_spreadsheet(
    gsheets_conn=gsheets_connection,
    filename="your_data.csv",
    spreadsheet_id="THIS-IS-THE-SPREADSHEETS-ID",
    spreadsheet_name="your_data_sheet"
)

Full example

Below is a full sample usage for uploading a CSV to Google Sheets that can easily be copied and pasted into your own source code as needed.

from route1io_connectors import gsheets

# Credentials
# NOTE: Storing credentials in source isn't a secure idea in practice. It's recommended you store them in a more secure place.
REFRESH_TOKEN = "YOUR_GOOGLE_REFRESH_TOKEN"
CID = "YOUR_CID"
CSC = "YOUR_CSC"
gsheets_credentials = gsheets.get_gsheets.credentials(
    refresh_token=REFRESH_TOKEN,
    cid=CID,
    csc=CSC
)

# Connect to Google Sheets
gsheets_connection = gsheets.connect_to_gsheets(credentials=gsheets_credentials)

# Download remote spreadsheet from Google Sheets to local machine
gsheets.download_gsheets_spreadsheet(
    gsheets_conn=gsheets_connection,
    filename="your_data.csv",
    spreadsheet_id="THIS-IS-THE-SPREADSHEETS-ID",
    spreadsheet_name="your_data_sheet"
)

Clone this wiki locally