A Step-by-Step Guide to Google Spreadsheet Authentication and Automation with Python

Overview

Let’s be honest: handling authentication configuration is a chore. It’s a task we do so rarely that we often forget the process almost as soon as it’s completed. By the time we need to revisit it, the whole procedure seems unfamiliar once again.

This week, I found myself in this situation while setting up spreadsheet automation for the venture fund I work for, Chapter One. Our primary databases are SQL databases hosted on Google Cloud Platform. However, this particular task required me to have write access to a spreadsheet, a departure from my past use of the Google Sheet API, which I’d set up with read-only permissions.

So, I decided to turn my experience into a helpful resource. I’m writing this blog post as a straightforward guide, with each action broken up into separate scripts, hoping it might save someone else time and trouble in the future.

Connecting, reading, and writing to a google spreadsheet with Python

Setup steps

1. Add https://www.googleapis.com/auth/spreadsheets to OAuth consent screen. Go to https://console.cloud.google.com/apis/credentials/consent/edit?project={project_id}, click “Edit App”. Save and continue under OAuth consent screen, click “Add or remove scopes”, and add “https://www.googleapis.com/auth/spreadsheets to the scope.
2. Create OAuth 2.0 Client ID credential for Desktop: https://console.cloud.google.com/apis/credentials?project={project_id}
3. Download the OAuth 2.0 Client JSON file

Create a Google API service with token authentication

import os
import json
from googleapiclient.discovery import build
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request


def create_google_service(client_secret_file, api_service_name, api_version, output_path, scopes):
    """Creates a Google API service.

    :param client_secret_file: Path to the client secret JSON file from Google Cloud Console.
    :param api_service_name: The name of the Google API service.
    :param api_version: The version of the Google API service.
    :param output_path: Path to the output JSON file storing the credentials.
    :param scopes: A list of scopes for the API service.
    :return: A service that is connected to the specified Google API.
    """
    cred = None

    # Check if the output JSON file with credentials exists
    if os.path.exists(output_path):
        with open(output_path, 'rb') as token:
            cred = json.load(token)

    # If there are no (valid) credentials available, let the user log in
    if not cred or not cred.valid:
        if cred and cred.expired and cred.refresh_token:
            cred.refresh(Request())
        else:
            flow = InstalledAppFlow.from_client_secrets_file(
                client_secret_file, scopes)
            cred = flow.run_local_server(port=0)

            # Save the credentials for the next run
            with open(output_path, 'w') as token:
                token.write(cred.to_json())

    try:
        # Build the service
        service = build(api_service_name, api_version, credentials=cred)
        print(f"{api_service_name} service created successfully")
        return service
    except Exception as e:
        print(f"Failed to create service: {e}")
        return None


create_google_service(
    client_secret_file='google-client-secret-desktop.json', # your input filename
    api_service_name='sheets',
    api_version='v4',
    output_path='google-sheet-api-token.json', # your output filename
    scopes=['https://www.googleapis.com/auth/spreadsheets']
)

Write to spreadsheet

# Import required modules
from googleapiclient.discovery import build
from google.oauth2.credentials import Credentials
import logging

# Configuring the logging module to output debug information
logging.basicConfig(level=logging.INFO)

# Setting up constants for Google Sheets API access
# Auth scope for Sheets API
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
# ID of the target spreadsheet
# Find the spreadsheet ID in the URL: https://docs.google.com/spreadsheets/d/{SPREADSHEET_ID}
SPREADSHEET_ID = 'SPREADSHEET_ID'
# Path to token JSON
TOKEN_PATH = 'google-sheet-api-token.json'
# The range of cells to update in sheet notation
SAMPLE_RANGE_NAME = 'Sheet1!A:E'


def authenticate() -> None:
    """
    Authenticates the user and sets up the Sheets API service.
    """
    try:
        # Load credentials from the service account file and create a Sheets API service
        credentials = Credentials.from_authorized_user_file(
            TOKEN_PATH, SCOPES)
        service = build('sheets', 'v4', credentials=credentials)
        return service
    except Exception as e:
        logging.error(f"Failed to authenticate: {e}")
        return None  # Ensure the function returns a value even in case of error


def export_data_to_sheets(service):
    """
    Updates specific cells in the spreadsheet with new data.

    :param service: A Sheets API service instance.
    """
    # Update the range of cells with the given values
    response = service.spreadsheets().values().update(
        spreadsheetId=SPREADSHEET_ID,  # The ID of the spreadsheet to update
        valueInputOption='RAW',  # The values will be parsed exactly as they are input
        range=SAMPLE_RANGE_NAME,  # The range of cells to update
        body=dict(
            majorDimension='ROWS',  # The first dimension of the values array corresponds to rows
            values=[['This', 'is', 'a', 'test...'], [
                'Congrats!', 'It', 'works!']]  # The data to input
        )
    ).execute()
    logging.info(response)
    logging.info('Sheet successfully Updated!')


# Authenticate to the service and update the sheet
service = authenticate()
if service:  # Only attempt to update the sheet if authentication was successful
    export_data_to_sheets(service)

Read from spreadsheet

# Import required modules
from googleapiclient.discovery import build
from google.oauth2.credentials import Credentials
import logging

# Set the logging level to INFO to get detailed logs
logging.basicConfig(level=logging.INFO)

# Setting up constants for Google Sheets API access
# Auth scope for Sheets API
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
# ID of the target spreadsheet
# Find the spreadsheet ID in the URL: https://docs.google.com/spreadsheets/d/{SPREADSHEET_ID}
SPREADSHEET_ID = 'SPREADSHEET_ID'
# Path to token JSON
TOKEN_PATH = 'google-sheet-api-token.json'
# The range of cells to update in sheet notation
SAMPLE_RANGE_NAME = 'Sheet1!A:E'


def authenticate() -> None:
    """
    Authenticates the user and sets up the Sheets API service.
    Returns the authenticated service or None if an error occurs.
    """
    try:
        # Load credentials from the token file and create a Sheets API service
        credentials = Credentials.from_authorized_user_file(TOKEN_PATH, SCOPES)
        service = build('sheets', 'v4', credentials=credentials)
        return service
    except Exception as e:
        # Log an error message if authentication fails
        logging.error(f"Failed to authenticate: {e}")
        return None


def read_data_from_spreadsheet(service):
    """
    Reads data from specific cells in the spreadsheet.

    :param service: An authenticated Sheets API service instance.
    """
    # Create a Sheets API instance
    sheet = service.spreadsheets()

    # Request to get values from the specified range in the Google Sheet
    result = sheet.values().get(spreadsheetId=SPREADSHEET_ID,
                                range=SAMPLE_RANGE_NAME).execute()

    # Extract the values from the response
    values = result.get('values', [])

    # If the spreadsheet is empty, log an info message and return None
    if not values:
        logging.info('No data found.')
        return None

    # Log the values and return them
    logging.info(values)
    return values


# Authenticate to the Google Sheets API and read data from the spreadsheet
service = authenticate()
if service is not None:  # Only attempt to read data if authentication was successful
    read_data_from_spreadsheet(service)