gsheettest.py

import sys
import os
import gspread
from google.oauth2 import service_account


def get_gspread_client() -> gspread.Client:
    scope = ["https://www.googleapis.com/auth/drive", "https://www.googleapis.com/auth/spreadsheets"]
    # see https://docs.gspread.org/en/latest/oauth2.html
    credentials_path = os.environ.get('GOOGLE_SERVICE_ACCOUNT_CREDENTIALS', None)
    if credentials_path:
        # Authenticate using the service account credentials file
        credentials = service_account.Credentials.from_service_account_file(credentials_path, scopes=scope)
        return gspread.authorize(credentials)
    # default app credentials stored in `~/.config/gspread/credentials.json`
    authorized_user_file = gspread.auth.DEFAULT_AUTHORIZED_USER_FILENAME
    exit_after_authorization = not authorized_user_file.exists()
    gc = gspread.oauth(scopes=scope)
    if exit_after_authorization:
        print("Successfully authorized the client app via OAuth2!")
        sys.exit(0)
    return gc


gc = get_gspread_client()
sheet_url = "https://docs.google.com/spreadsheets/d/1_ridW28-bBFYdoEfcJU79eXf3qn5NxX_XveW19pI4C8"
spreadsheet = gc.open_by_url(sheet_url)
worksheet = spreadsheet.worksheets()[0]
header_row, *data_rows = worksheet.get_all_values()
print(f"header_row: {header_row}")

# read column titled 'my column'
for i, row in enumerate(data_rows):
    print(f"row {i+2} is: {row}")

# update first column
try:
    n = len(data_rows)
    column = [[f"row {i}"] for i in range(n)]
    worksheet.update(values=column, range_name=f"A2:A{n+1}")
except Exception as e:
    print(e)

Example usage with OAuth:

❯ pip install gspread
❯  mkdir ~/.config/gspread
❯ cp client_app_oauth.json ~/.config/gspread/credentials.json
❯ python gsheettest.py
Please visit this URL to authorize this application: https://accounts.google.com/o/oauth2/auth?response_type=code&client_id=374937665983-trtvr2qc54c4s9d0rps1tre29u9m1p5n.apps.googleusercontent.com&redirect_uri=http%3A%2F%2Flocalhost%3A58856%2F&scope=https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fdrive+https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fspreadsheets&state=OLtVhvPXTdHRbUm8sUyBO3VVi77xsV&access_type=offline
Successfully authorized the client app via OAuth2!
❯ python gsheettest.py
header_row: ['a column', 'my column', 'something else']
row 2 is: ['row 0', 'one', '']
row 3 is: ['row 1', 'two', '']
row 4 is: ['row 2', 'three', 'is company']
row 5 is: ['row 3', 'four', '']
row 6 is: ['row 4', 'five', '']
row 7 is: ['row 5', 'six', '']