Partnerships CRM Google Sheets Sync

Google Sheets sync keeps CRM data and spreadsheet workflows aligned.

Sync Modes

Mode	Direction	Use when
`push`	CRM -> Sheet	CRM is source of truth
`pull`	Sheet -> CRM	team bulk-edited sheet and wants those edits in CRM
`merge`	pull then push	both sides changed and you want normalized reconciliation

Required Env Variables

Variable	Required	Notes
`PARTNERSHIPS_GSHEETS_ENABLED`	recommended	set `false` to hard-disable
`PARTNERSHIPS_GSHEETS_AUTO_SYNC`	optional	if `true`, successful writes trigger best-effort push
`PARTNERSHIPS_GSHEETS_SPREADSHEET_ID`	yes	target spreadsheet id
`PARTNERSHIPS_GSHEETS_SHEET_NAME`	optional	defaults to `PartnershipsCRM`
`PARTNERSHIPS_GSHEETS_SERVICE_ACCOUNT_JSON`	preferred	full JSON credentials
`PARTNERSHIPS_GSHEETS_SERVICE_ACCOUNT_EMAIL`	fallback	used with private key
`PARTNERSHIPS_GSHEETS_PRIVATE_KEY`	fallback	used with account email

Credentials stay backend-only

Do not expose service account credentials in frontend env vars.

One-Time Google Setup

Step 1: Create service account

Open Google Cloud Console.
Create/select project.
Enable Google Sheets API.
Create service account.
Generate key JSON.

Open target spreadsheet.
Click Share.
Add service account email as Editor.
Save.

Step 3: Configure backend env and redeploy

Set env vars in the runtime where Partnerships backend runs, then deploy/restart service.

Local Testing Against A Real Sheet

Export env vars in shell running serverless offline.
Start partnerships service locally.
Open CRM and check Sheets status card.
Trigger manual sync (push) from Overview tab.
Confirm spreadsheet updates.
Edit one row in sheet.
Trigger pull and verify CRM data updates.

Status Card Meanings

Not configured: missing/invalid env setup, or hard-disabled
Configured: credentials + sheet access validated
Configured but not accessible: credentials exist, but sheet cannot be opened

Status payload also returns:

diagnostics for env presence checks
lastSyncAt, lastSyncMode, lastSyncStatus
lastSyncSummary + lastSyncError

Auto-Sync Behavior

When PARTNERSHIPS_GSHEETS_AUTO_SYNC=true:

partner/event/link/document/comm mutations can trigger a best-effort push
write operation still succeeds even if auto-sync push fails
sync failure is logged and shown in sync status metadata

Pull Guardrails

During pull:

row with empty company/contact context is skipped
company is required
date fields must be YYYY-MM-DD
amount fields must parse as non-negative numbers
status/tier values are normalized to CRM formats

Warnings are returned in sync result summary for skipped/partial rows.

Formatting Applied To Sheet

After push, backend applies presentation rules:

frozen header row
grouped header color bands
status chips with color formatting
boolean highlights (alumni/archive)
adjusted column widths

Formatting logic is in services/partnerships/googleSheets.js.

Common Errors

Google Sheets sync is not configured.

missing spreadsheet id and/or credentials

...configured but the sheet could not be accessed.

wrong spreadsheet id
sheet not shared with service account
invalid private key (newline formatting issue)

Dynamo ResourceNotFoundException when checking status

local tables were not migrated (including meta table)

column behavior: Google Sheets Mapping
env setup: Partnerships CRM Overview
route details: Backend API