Google Sheets settings reference

Audience: developers & AI agents · Scope: every setting that holds a sheet reference or the Google credentials · Last reviewed: 2026-07-20

TL;DR — Sheet references come in two shapes: the BcGoogleSheet picker (stores <spreadsheetId>#<gid>, validated against what the service account can see) and plain strings (a spreadsheet id plus a tab title, unvalidated). All of them are per-environment rows on the settings record; many live in extension tables rather than the base one.

All settings are defined in data/customization/models/adminbcsettings.php unless noted.

Credentials — the Google Cloud Integration group

Setting	Label	Type	Notes
reps_file	Google API Service Account Key file (JSON)	BcGoogleJsonFile	Required. Max 50 KB, application/json. Stored as <settingsRecordId>.json under the private data store private/custom_media/sheets_json. The derived reps_file_path is what every consumer reads.
bigquery_project_id	BigQuery Project ID	string	Optional; BigQuery, not Sheets

reps_file is the single Google credential for the whole site — Sheets, Drive, BigQuery and Vertex AI. The reps_ prefix is historical.

Picker settings (BcGoogleSheet)

Stored as <spreadsheetId>#<gid>. requireWriteAccess disables read-only options in the dropdown — a UI-only guard, not re-validated on save or at write time.

Setting	Label	Settings group	Write access	Stored in
product_filters_sheet	Google sheet with product filter data	Product overview page settings	no	base
reps_sheet	Rep Master Sheet	Reps Synchronization	no	_settings_reps
branches_sheet	Rep Master Sheet	Branches Synchronization	no	_settings_branches
order_tracking_sheet	Sheet for reading shipment data	Shipment tracking	no	base
order_tracking_sheet_add_shipping_address	Sheet for adding the ship to address	Shipment tracking	yes	base
order_tracking_sheet_add_po_number	Sheet for adding the PO number	Shipment tracking	yes	base
infor_code_normalization_report_sheet	Sheet for code normalization reports	Infor REST API Integration	yes	base
quote_nudge_sheet	Follow-up nudge sheet	Quote Follow-ups	yes	_settings_quote_follow_up
quote_stage_sync_sheet	Quote stage-change log sheet	Quote Follow-ups	yes	_settings_quote_follow_up
cold_quotes_report_sheet	Cold Quotes Report Sheet	Cold Quotes	yes	base
performance_data_import_sheet	Performance Data Import Sheet	Performance Data	no	base (required)
performance_data_multiplier_import_sheet	Performance Data Multiplier Import Sheet	Performance Data	no	base (required)

One more lives on a different model: ConfigboxModelSqlexports::sheet (models/sqlexports.php:87-96), label Google Sheet, requireWriteAccess true — one per export record rather than one per site.

reps_sheet and branches_sheet share the label "Rep Master Sheet" and are told apart only by their settings group. They are two separate imports from the same source workbook.

Plain-string settings (spreadsheet id + tab title)

No picker, no validation, no access pre-flight. The tab is addressed by title, so renaming a tab breaks these (unlike the picker settings, which use the tab id).

Group	Settings
Google Sheet Exports	exports_fabrication_schedule_spreadsheet / _sheet, exports_fabrication_schedule_priority_spreadsheet / _sheet, exports_engineering_schedule_spreadsheet / _sheet, exports_assembly_schedule_spreadsheet / _sheet, exports_warehouse_kitting_spreadsheet / _sheet, kitting_bom_spreadsheet_id / kitting_bom_sheet_title
Google Sheet Exports (legacy)	fabrication_schedule_spreadsheet_id / _sheet_title, fabrication_schedule_priority_spreadsheet_id / _sheet_title, engineering_schedule_spreadsheet_id / _sheet_title, warehouse_kitting_data_spreadsheet_id / _sheet_title — read only by the …Old methods, which have no live callers
Price list imports	pricelist_key, pricelist_matrices_tab, pricelist_feedback_tab
Order Scheduling	order_scheduling_sheet_id, order_scheduling_orders_tab_name, order_scheduling_items_tab_name
Scheduled Job BOM Calculation	job_bom_calc_source_spreadsheet / _tab, job_bom_calc_target_spreadsheet / _tab
Margin Analysis Tool	costing_sheet_google_spreadsheet_id, costing_sheet_google_sheet_title

Not configurable

Two consumers carry literal spreadsheet ids in code — changing their destination needs a code change:

• Production schedule export — models/bcproductionschedule.php:18-32, tabs Jobs and Raw BOM Data.
• Converted LUM codes import — models/convertedcodes.php:4-11.

Header-keyword settings

The rep and branch imports don't hardcode column headers; they match the sheet's header row against per-column settings, and abort with a specific message when one is missing. Each import has its own *_header_keyword_* set — for branches: branch code, name, organisation id, sync flag, RSM email, RSM name, agent discount groups, agent win rate (adminbcsettings.php:481-580).

Storage — why settings live in extension tables

The base settings row outgrew MySQL's row-size limit, so groups of columns were carved into settings_id-keyed extension tables with cascade-delete foreign keys, joined back transparently by Kenedo's storeExternally / foreignTableName / foreignTableAlias / foreignTableKey:

Table	Created by
#__configbox_external_settings_reps	updates/0.5.59.php
#__configbox_external_settings_branches	updates/0.5.60.php
#__configbox_external_settings_exports	updates/0.5.58.php, extended by 0.5.62.php
#__configbox_external_settings_quote_follow_up	updates/0.5.69.php
#__configbox_external_settings_pipedrive	updates/0.5.56.php

When adding a new sheet setting, add it to the right extension table, not the base one — see migrations.

Schema history worth knowing

Sheet references were originally stored as two columns (spreadsheet_id + sheet_reference). They were consolidated into the single <id>#<gid> form: updates/0.4.84.php (backfilling #__configbox_external_sql_exports.sheet), updates/0.4.90.php (the export log), and the old column pairs dropped in updates/0.4.91.php. A stale docblock in models/googlesheets.php:64 still describes the separator as a colon.

Related docs

• Overview · consumers.md
• Migrations — how to add a settings column
• Admin Guide: Custom Settings (admin-guide/settings-ops/custom-settings.md)