gspread - Google Spreadsheets Python API

import gspread
import os

# Ensure your service account key file path is set as an environment variable
# or replace with the actual path.
# For example: export GSPREAD_SERVICE_ACCOUNT_KEYFILE="./path/to/your/service_account.json"

SERVICE_ACCOUNT_KEYFILE = os.environ.get(
    'GSPREAD_SERVICE_ACCOUNT_KEYFILE',
    './path/to/your/service_account.json' # Placeholder, replace or use env var
)

try:
    # Authenticate using a service account
    # Make sure to share your Google Sheet with the service account email address.
    gc = gspread.service_account(filename=SERVICE_ACCOUNT_KEYFILE)

    # Open a spreadsheet by its title
    spreadsheet_title = "My Test Spreadsheet"
    sh = gc.open(spreadsheet_title)

    # Select the first worksheet
    wks = sh.sheet1

    print(f"Successfully opened spreadsheet: {sh.title}")
    print(f"First worksheet title: {wks.title}")

    # Read a single cell value
    cell_a1 = wks.acell('A1').value
    print(f"Value in A1: {cell_a1}")

    # Update a single cell
    wks.update_acell('B1', 'Hello gspread!')
    print("Updated cell B1.")

    # Update a range of cells (using v6 syntax with 2D array and named args)
    data_to_write = [['Name', 'Age'], ['Alice', 30], ['Bob', 24]]
    wks.update(values=data_to_write, range_name='A3')
    print("Updated range A3:B5.")

    # Get all values from the worksheet as a list of lists
    all_values = wks.get_all_values()
    print("\nAll values in the worksheet:")
    for row in all_values:
        print(row)

except FileNotFoundError:
    print(f"Error: Service account key file not found at {SERVICE_ACCOUNT_KEYFILE}. ")
    print("Please ensure the file exists and the path is correct.")
except gspread.exceptions.SpreadsheetNotFound:
    print(f"Error: Spreadsheet '{spreadsheet_title}' not found or not shared with the service account.")
    print("Ensure the spreadsheet name is correct and shared with the client_email from your service account JSON.")
except gspread.exceptions.APIError as e:
    print(f"Google Sheets API Error: {e}")
    print("This might be a rate limit issue or incorrect API permissions.")
except Exception as e:
    print(f"An unexpected error occurred: {e}")