Google Sheets — the integration layer

Audience: developers & AI agents · Scope: the shared Google Sheets client, the BcGoogleSheet picker, caching and failure modes; a map to the consumer and settings references · Last reviewed: 2026-07-20

TL;DR — Google Sheets is shared plumbing, not a feature: 30+ consumers read from or write to sheets — the products-page filters, the rep and branch imports, performance data, the price list, the quote follow-up sheets, the SQL exports, and every Infor schedule export. They all authenticate as one service account, and a sheet is invisible to the system until it is shared with that account. Most writes are a full replace.

This is a Integration layer — systems link to it; it is not owned by any one of them.

The parts

Doc	What it covers
This hub	the client, auth, the picker property, the caches, and the failure patterns everything inherits
consumers.md	every read/write site — feature → sheet setting → direction → trigger
settings.md	every setting holding a sheet reference, and the credentials group

How it fits together

   Custom Settings → Google Cloud Integration
        reps_file  (service-account JSON, one per environment)
              │
              ▼
   ConfigboxModelGooglesheets            ← the shared client
     getAccessibleGoogleSheets()  ──────► Drive: files.list (sharedWithMe)
     getSheetValues() / writeSheetValues() / appendSheetValues() / clearSheet()
              │                                   │
              │ cached per env (memcached)        │
              ▼                                   ▼
   BcGoogleSheet picker                    30+ consumers
     "<spreadsheetId>#<gid>"               (see consumers.md)
     + "Reload list"                        page load · admin button · cron/CLI

File	Role
data/customization/models/googlesheets.php	ConfigboxModelGooglesheets — the whole client: auth, listing, read, write, append, clear
data/customization/properties/bcgooglesheet.php (+ tmpl/)	the sheet-picker property type
data/customization/properties/bcgooglejsonfile.php (+ tmpl/)	the credentials upload; surfaces the service-account address
data/customization/controllers/adminbcgooglesheets.php	the "Reload list" AJAX endpoint
data/customization/assets/javascript/custom.js:12-113	googleSheetPropertyModule — picker behaviour
docroot/cli/cb_google_sheets_refresh_cache.php	cron that rebuilds the accessible-sheets cache for every environment

Authentication

One service-account JSON key file, uploaded per environment as the reps_file setting (Custom Settings → Google Cloud Integration) and stored in the private data store under private/custom_media/sheets_json/<settingsRecordId>.json. getService() (models/googlesheets.php:25) points GOOGLE_APPLICATION_CREDENTIALS at it and uses application-default credentials.

• The name reps_file is historical. It is the single Google credential for everything — Sheets, Drive, BigQuery and Vertex AI — not just the rep sync.
• The account's address (client_email) is surfaced twice: under the credentials upload, and under every sheet picker as "Used Service Account:".
• A sheet the account cannot see does not exist to the system. Sharing is the setup step people forget; writing consumers need edit access.
• getService() always uses the current environment's credentials, even when you are editing another environment's settings record.

The sheet picker (BcGoogleSheet)

Sheet references are stored as <spreadsheetId>#<gid> — the numeric tab id, not the tab title.

The picker lists what the service account can see: Drive files.list with sharedWithMe=true, then one entry per tab, labelled "<File name>: <Tab title>", with " (is read-only)" appended when the account lacks edit rights. Reload list re-runs that listing and rebuilds the dropdown.

Behaviours worth knowing:

• A saved value that is no longer listed renders as a synthetic "Inaccessible sheet" option plus a red banner — deliberately, so saving the form does not silently wipe the reference.
• requireWriteAccess is a UI-only guard. It disables read-only options in the dropdown; nothing re-validates it on save or at write time.
• The "Reload list" endpoint always uses the current environment. Editing the live settings record from dev and pressing Reload repopulates the dropdown with dev's visible sheets. The server-rendered list is per-record; the reloaded one is not.

When the listing fails

Because "we could not load the list" and "this sheet is not shared with us" look identical from an empty list, the picker keeps them apart deliberately:

• The red "is not accessible to this service account" banner is shown only when a list was successfully loaded and the saved value genuinely isn't in it.
• When the listing failed, an amber "The list of sheets could not be loaded from Google" callout appears instead, carrying the API's own reason, and the saved value is offered as a "Current sheet (…)" option so submitting the form cannot silently clear the setting.
• The list-screen renderer does the same: Could not load the sheet list - <id> rather than No access to spreadsheet <id>.
• Reload list reports the real reason on failure and leaves the previous cache entry alone.
• refreshAccessibleSheetsCache() handles each environment separately — one unreachable environment neither aborts the others nor overwrites their good cache entries; it collects the failures and throws at the end, so the cron exits non-zero and names the environments that failed.

Caching

Cache	Backend	Key	TTL	Refreshed by
Accessible-sheets list	memcached (Scrapbook PSR-16)	cb.accessibleSheets.<env>	none	"Reload list" button; cli/cb_google_sheets_refresh_cache.php (all envs)
Products-page filters	APCu	products.filters	none	Refresh Product Filters button (Custom Settings → Product overview page settings)
Spreadsheet metadata	in-process	per file id	request	n/a

The client self-throttles at 40 spreadsheet reads per minute and sleep(60)s on hitting the limit — which is why a full cache refresh is documented in the UI as taking about a minute.

Failure modes everything inherits

• getSheetValues() returns null, not [], for an empty range. Callers that index straight into the result fatal instead of degrading.
• A failed listing is never cached. getAccessibleGoogleSheets() throws on failure — an empty list is a legitimate result ("nothing is shared with the account") and the two must stay distinguishable. getCachedAccessibleGoogleSheets($envName, &$error) catches, sets $error, returns [] and does not write to the cache, so the next render retries and the system self-heals once Google is reachable. Only successful listings are cached. Callers must check $error before concluding a saved reference is inaccessible — see the picker's failure UX.
• Most writes are a full replace. writeSheetValues() resizes the tab to exactly the row count and clears every value. A consumer whose source query returns nothing will happily blank its destination tab. The one documented exception is the SQL exports, which return early and leave the sheet untouched — see consumers.md.
• Two code paths exist. Most consumers use ConfigboxModelGooglesheets; the price-list import, commission tiers import, CSI/order-scheduling and job-BOM calculation build a raw Google_Client inline instead. Those get no picker, no cached listing and no access pre-flight — they hold a spreadsheet id plus a tab title in plain string settings.
• Some consumers pre-flight with getAccessErrorStatus() and throw a clear error; many do not. The products-page filters, order tracking, cold quotes, code normalization, the costing sheet, the production schedule and the performance-data imports all skip it.

Deployment runbook (manual steps)

Per environment:

1. Create/obtain the Google service account and download its JSON key.
2. Custom Settings → Google Cloud Integration → upload it as the Google API Service Account Key file (JSON). Confirm the Service Account for used file address renders.
3. Share every destination sheet with that address — edit access for any sheet the system writes to.
4. Press Reload list on a picker (or run cli/cb_google_sheets_refresh_cache.php) and wait ~1 minute.
5. Set each sheet setting — see settings.md.
6. Add the cron entries for the CLI-triggered consumers — see consumers.md.
7. Smoke test the cheapest consumer end to end (the rep import is a good one: it validates headers and reports clearly), then press Refresh Product Filters and load the public products page.

Turning it off: most consumers treat a blank sheet setting as "feature off" and return early — the quote follow-up nudge and stage-sync sheets do so explicitly. The products-page filters do not: see the gotcha in consumers.md.

Gotchas & caveats

• One credential, blast radius everywhere. Rotating or breaking reps_file takes down Sheets, BigQuery and Vertex AI at once.
• spreadsheetId#gid is by tab id, not title. Renaming a tab is safe; deleting and recreating it is not.
• Two settings share the label "Rep Master Sheet" (reps_sheet and branches_sheet), distinguished only by their settings group. They are separate imports of the same source workbook.
• Two settings are hardcoded in code, not configurable — the production-schedule export and the converted LUM-code import carry literal spreadsheet ids.
• The settings row outgrew MySQL's row-size limit, so sheet settings are spread across extension tables (_reps, _branches, _exports, _quote_follow_up, _pipedrive) joined transparently by Kenedo's storeExternally. Adding a new sheet setting usually means adding it to an extension table, not the base one.

Possible follow-ups

• Guard getProductFilters() — today a malformed sheet takes the public products page down (see consumers.md).
• No automated coverage for the listing-failure UX; it was verified by blocking Google's hostnames inside the DDEV web container and reloading a picker. A spec would need the same kind of network fault injection.
• Migrate the raw-Google_Client consumers onto the model so they gain the picker and the pre-flight.

Related docs

• consumers.md · settings.md
• Pipedrive — the other integration layer
• Quote Follow-Up — a system that depends on this layer
• Admin Guide: SQL to Google Sheet exports (admin-guide/pricing-bom-erp/sql-to-sheet-exports.md) · Branch import (admin-guide/quotes-sales/manage-branches.md) · Browse filters (admin-guide/products-configurator/manage-browse-filters.md)