This guide sets up daily-ready sync from local SQLite into a Google Sheet
using a service account. The app command is:
python -m opportunity_ingest sync-sheetsIt fully replaces one worksheet tab (default name: Ingest).
Use a separate tab (e.g. Review) for manual notes so they are not wiped.
Cross-links (LLM + product rules):
- AGENTS.md — project entry for agents
- DATA_UPDATE_DIRECTIVES.md §3 — Sheets normative rules
- AS_BUILT.md — architecture
- daily_sync.ps1 — Windows scheduled ingest + sync
- Google account
- This project venv working (
run --writealready creates rows in SQLite) - Python package extras for Sheets:
cd <repository-root>
.\.venv\Scripts\Activate.ps1
pip install -e ".[sheets]"- Open Google Cloud Console.
- Top bar → project picker → New Project (or pick an existing one).
- Name example:
contract-bidding-ingest→ Create.
- Menu → APIs & Services → Library.
- Search Google Sheets API → open it → Enable.
- (Recommended) Also enable Google Drive API the same way
(needed for somegspreadopen-by-key flows).
- Menu → IAM & Admin → Service Accounts.
- + Create Service Account.
- Name:
opportunity-sheets-sync→ Create and Continue. - Role: you can skip project roles for Sheets-only access (sharing the sheet is enough)
→ Continue → Done.
- Click the new service account email.
- Tab Keys → Add key → Create new key → JSON → Create.
- A
.jsonfile downloads. Keep it secret (never commit to git).
Suggested local path:
secrets/google-service-account.json
Create the folder and move the file there:
New-Item -ItemType Directory -Force -Path secrets | Out-Null
# Move the downloaded JSON into secrets\google-service-account.jsonEnsure .gitignore ignores secrets (add secrets/ if needed).
On the service account details page, copy the email, like:
opportunity-sheets-sync@YOUR_PROJECT_ID.iam.gserviceaccount.com
You will share the Sheet with this address.
- Open Google Sheets → Blank spreadsheet.
- Rename the file, e.g. Contract Opportunities.
- Rename the first tab to
Ingest(or leave default and setGOOGLE_SHEET_TABlater). - Optional: add a
Rankedtab for Grok interpret-rank output (created automatically on first rank sync if missing). Do not put rankings onIngest. - Optional: add a
Reviewtab for human notes (pipeline will not touch it if you only syncIngest/Ranked).
- Click Share.
- Paste the service account email.
- Role: Editor.
- Uncheck “Notify people” if offered → Share / Send.
If you skip this step, sync fails with permission errors.
From the browser URL:
https://docs.google.com/spreadsheets/d/ 1abc...xyz /edit#gid=0
^^^^^^^^^^
SHEET_ID
Copy only the long 1abc... segment → that is GOOGLE_SHEET_ID.
pip install -e ".[sheets]"Copy from example if needed, then edit .env in the repo root:
STORAGE_BACKEND=sqlite
DATA_DIR=data
GOOGLE_SHEET_ID=1abc...your_id...xyz
GOOGLE_SHEET_TAB=Ingest
GOOGLE_SHEET_RANK_TAB=Ranked
GOOGLE_SERVICE_ACCOUNT_FILE=secrets/google-service-account.json
# Optional Grok (separate extra): XAI_API_KEY=...Do not commit .env or the JSON key.
python -m opportunity_ingest run --write --csv data/sample-openTenderNotice.csv --max-create 10
python -m opportunity_ingest check-storepython -m opportunity_ingest sync-sheetsOr override flags:
python -m opportunity_ingest sync-sheets --sheet-id "1abc..." --tab IngestExpected success line (Ingest):
Synced 10 rows to sheet 1abc... tab 'Ingest'
Open the Google Sheet in the browser → Ingest tab should show headers + rows
(Title, OpportunityID, Link, RelevanceScore, Status, …).
Same spreadsheet and service account. Rankings never write to Ingest.
pip install -e ".[ai]"
# XAI_API_KEY in root .env
python -m opportunity_ingest interpret-rank --status New --limit 20
# or re-push last local report without calling Grok:
python -m opportunity_ingest sync-rank-sheets- Creates/replaces tab
Ranked(orGOOGLE_SHEET_RANK_TAB). - Local copies:
data/rankings/interpret-*.md. - Skip sheet push:
--no-sync-sheets. - Full human checklist (all features):
docs/PLUG_AND_PLAY.md. - Architecture:
docs/AS_BUILT.md. Unbuilt ideas:docs/BACKLOG.md.
-
Use the checked-in script
scripts\daily_sync.ps1(it changes to the repository root from its own location). -
Task Scheduler → Create Basic Task → Daily after ~9:30 AM Eastern
(CanadaBuys refreshes earlier; 10:00 AM local is a safe start). -
Action: start program
powershell.exe
Arguments:
-NoProfile -ExecutionPolicy Bypass -File "<repository-root>\scripts\daily_sync.ps1"
PC must be on at that time.
- Repo secrets:
GOOGLE_SHEET_IDGOOGLE_SERVICE_ACCOUNT_JSON= full contents of the JSON key fileTEAMS_WEBHOOK_URLoptional
- After the existing
run --writestep, add:
- name: Sync Google Sheets
env:
GOOGLE_SHEET_ID: ${{ secrets.GOOGLE_SHEET_ID }}
GOOGLE_SERVICE_ACCOUNT_JSON: ${{ secrets.GOOGLE_SERVICE_ACCOUNT_JSON }}
GOOGLE_SHEET_TAB: Ingest
STORAGE_BACKEND: sqlite
DATA_DIR: data
run: |
pip install -e ".[sheets]"
python -m opportunity_ingest sync-sheetsUse inline JSON (GOOGLE_SERVICE_ACCOUNT_JSON) in CI; use file path locally.
| Topic | Behaviour |
|---|---|
| Sync mode | Full replace of the target tab (clear + write all SQLite rows) |
Manual edits on Ingest |
Overwritten next sync |
| Manual work | Keep on a Review tab (not synced) |
| Empty DB | Writes header only (0 data rows) |
| Status field | Comes from SQLite; edit in DB/export workflow for now, not two-way |
| Symptom | Fix |
|---|---|
Sheets deps missing |
pip install -e ".[sheets]" |
set --sheet-id or GOOGLE_SHEET_ID |
Put .env at repo root (not scripts/.env); restart shell |
Service account file not found |
Check path; enable File Explorer “File name extensions”; avoid *.json.json |
| Permission / 403 when opening sheet | Share sheet with service account email as Editor |
GOOGLE_SHEET_ID is required |
Set env or --sheet-id (no quotes needed in .env) |
| API not enabled | Enable Google Sheets API (+ Drive API) on the GCP project |
| Wrong project key | JSON key must belong to the project where APIs are enabled |
| No rows | Run run --write first; check-store / export-csv to verify SQLite |
-
secrets/and.envare gitignored - JSON key never pasted into chat/commits
- Sheet shared only with your user + service account
- CI uses GitHub Secrets, not plain env in logs
pip install -e ".[sheets]"
# fill DB
python -m opportunity_ingest run --write --csv data/sample-openTenderNotice.csv --max-create 10
# push to Google Sheets
python -m opportunity_ingest sync-sheets
# optional local CSV too
python -m opportunity_ingest export-csv