CodexやClaude Codeから非公開Googleスプレッドシートを直接操作する方法

my-sheets-bot@your-project-id.iam.gserviceaccount.com

my-project/
  scripts/
    sheets_read.py
    sheets_append.py
    sheets_update.py
  .env.example
  requirements.txt
  .gitignore

~/.config/google-service-accounts/sheets-bot.json

google-api-python-client==2.*
google-auth==2.*
python-dotenv==1.*

GOOGLE_APPLICATION_CREDENTIALS=/Users/yourname/.config/google-service-accounts/sheets-bot.json
SPREADSHEET_ID=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
SHEET_NAME=Sheet1

.env
*.credentials.json
service-account*.json
secrets/
.claude/settings.local.json

# scripts/sheets_client.py
import os
from google.oauth2 import service_account
from googleapiclient.discovery import build

SCOPES = ["https://www.googleapis.com/auth/spreadsheets"]

def build_sheets_service():
    credentials_path = os.environ["GOOGLE_APPLICATION_CREDENTIALS"]
    credentials = service_account.Credentials.from_service_account_file(
        credentials_path,
        scopes=SCOPES,
    )
    return build("sheets", "v4", credentials=credentials)

# scripts/sheets_read.py
import os
from dotenv import load_dotenv
from sheets_client import build_sheets_service

load_dotenv()

spreadsheet_id = os.environ["SPREADSHEET_ID"]
sheet_name = os.environ.get("SHEET_NAME", "Sheet1")
range_name = f"{sheet_name}!A1:Z100"

service = build_sheets_service()
result = service.spreadsheets().values().get(
    spreadsheetId=spreadsheet_id,
    range=range_name,
).execute()

for row in result.get("values", []):
    print(row)

# scripts/sheets_append.py
import os
from datetime import datetime
from dotenv import load_dotenv
from sheets_client import build_sheets_service

load_dotenv()

spreadsheet_id = os.environ["SPREADSHEET_ID"]
sheet_name = os.environ.get("SHEET_NAME", "Sheet1")

values = [[
    datetime.now().isoformat(timespec="seconds"),
    "Codex/Claude Codeから追記",
    "done",
]]

service = build_sheets_service()
service.spreadsheets().values().append(
    spreadsheetId=spreadsheet_id,
    range=f"{sheet_name}!A:C",
    valueInputOption="USER_ENTERED",
    insertDataOption="INSERT_ROWS",
    body={"values": values},
).execute()

print("appended")

# scripts/sheets_update.py
import os
from dotenv import load_dotenv
from sheets_client import build_sheets_service

load_dotenv()

spreadsheet_id = os.environ["SPREADSHEET_ID"]
sheet_name = os.environ.get("SHEET_NAME", "Sheet1")

service = build_sheets_service()
service.spreadsheets().values().update(
    spreadsheetId=spreadsheet_id,
    range=f"{sheet_name}!D2",
    valueInputOption="USER_ENTERED",
    body={"values": [["更新済み"]]},
).execute()

print("updated")

python -m venv .venv
source .venv/bin/activate
pip install -r requirements.txt
cp .env.example .env
python scripts/sheets_read.py
python scripts/sheets_append.py
python scripts/sheets_update.py

# scripts/sheets_client_dwd.py
import os
from google.oauth2 import service_account
from googleapiclient.discovery import build

SCOPES = ["https://www.googleapis.com/auth/spreadsheets"]

def build_sheets_service():
    credentials = service_account.Credentials.from_service_account_file(
        os.environ["GOOGLE_APPLICATION_CREDENTIALS"],
        scopes=SCOPES,
    )
    delegated_credentials = credentials.with_subject(
        os.environ["GOOGLE_IMPERSONATE_USER"]
    )
    return build("sheets", "v4", credentials=delegated_credentials)

GOOGLE_APPLICATION_CREDENTIALS=/Users/yourname/.config/google-service-accounts/sheets-bot.json
GOOGLE_IMPERSONATE_USER=sheets-bot@example.com
SPREADSHEET_ID=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
SHEET_NAME=Sheet1

my-project/
  scripts/
    sheets_oauth_client.py
    sheets_oauth_read.py
    sheets_oauth_append.py
  credentials.json
  token.json
  .gitignore

credentials.json
token.json
.env
secrets/
.claude/settings.local.json

# scripts/sheets_oauth_client.py
import os
from google.auth.transport.requests import Request
from google.oauth2.credentials import Credentials
from google_auth_oauthlib.flow import InstalledAppFlow
from googleapiclient.discovery import build

SCOPES = ["https://www.googleapis.com/auth/spreadsheets"]

def build_sheets_service():
    credentials = None
    token_path = os.environ.get("GOOGLE_OAUTH_TOKEN", "token.json")
    client_secret_path = os.environ.get("GOOGLE_OAUTH_CLIENT_SECRET", "credentials.json")

    if os.path.exists(token_path):
        credentials = Credentials.from_authorized_user_file(token_path, SCOPES)

    if not credentials or not credentials.valid:
        if credentials and credentials.expired and credentials.refresh_token:
            credentials.refresh(Request())
        else:
            flow = InstalledAppFlow.from_client_secrets_file(
                client_secret_path,
                SCOPES,
            )
            credentials = flow.run_local_server(port=0)

        with open(token_path, "w", encoding="utf-8") as token_file:
            token_file.write(credentials.to_json())

    return build("sheets", "v4", credentials=credentials)

google-api-python-client==2.*
google-auth==2.*
google-auth-oauthlib==1.*
python-dotenv==1.*

# scripts/sheets_oauth_read.py
import os
from dotenv import load_dotenv
from sheets_oauth_client import build_sheets_service

load_dotenv()

spreadsheet_id = os.environ["SPREADSHEET_ID"]
sheet_name = os.environ.get("SHEET_NAME", "Sheet1")

service = build_sheets_service()
result = service.spreadsheets().values().get(
    spreadsheetId=spreadsheet_id,
    range=f"{sheet_name}!A1:Z100",
).execute()

for row in result.get("values", []):
    print(row)

# scripts/sheets_oauth_append.py
import os
from datetime import datetime
from dotenv import load_dotenv
from sheets_oauth_client import build_sheets_service

load_dotenv()

spreadsheet_id = os.environ["SPREADSHEET_ID"]
sheet_name = os.environ.get("SHEET_NAME", "Sheet1")

service = build_sheets_service()
service.spreadsheets().values().append(
    spreadsheetId=spreadsheet_id,
    range=f"{sheet_name}!A:C",
    valueInputOption="USER_ENTERED",
    insertDataOption="INSERT_ROWS",
    body={
        "values": [[
            datetime.now().isoformat(timespec="seconds"),
            "OAuthユーザーとして追記",
            "done",
        ]]
    },
).execute()

print("appended by OAuth user")

python scripts/sheets_oauth_read.py

{
  "permissions": {
    "deny": [
      "Read(./credentials.json)",
      "Read(./token.json)",
      "Read(./secrets/**)"
    ],
    "ask": [
      "Bash(python scripts/sheets_oauth_append.py:*)"
    ]
  }
}

{
  "env": {
    "GOOGLE_APPLICATION_CREDENTIALS": "/Users/yourname/.config/google-service-accounts/sheets-bot.json",
    "SPREADSHEET_ID": "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
    "SHEET_NAME": "Sheet1"
  },
  "permissions": {
    "deny": [
      "Read(./.env)",
      "Read(./secrets/**)",
      "Read(/Users/yourname/.config/google-service-accounts/**)"
    ],
    "ask": [
      "Bash(python scripts/sheets_update.py:*)",
      "Bash(python scripts/sheets_append.py:*)"
    ]
  }
}

このリポジトリでは scripts/sheets_*.py だけを使ってGoogle Sheetsを操作してください。
認証情報JSONの中身は読まないでください。
読み取りは scripts/sheets_read.py、追記は scripts/sheets_append.py、更新は scripts/sheets_update.py を使います。
本番シートへ書き込む前に、必ず対象範囲と変更内容を表示してください。