Update a WordPress Business Directory From a Google Sheet

wp te-directory pull --sheet=YOUR_SHEET_ID \
  --key=/srv/secrets/directory-sa.json

Match a Google Sheet's stable listing_id to an existing WordPress listing, validate the whole input range, then write only changes. The downloadable directory-sync plugin previews by default. A live run requires a checked private database export, records its plan and attempts, and verifies each listing's title, metadata and open/closed terms.

Correction: the earlier example linked to a backup pattern without implementing it, silently skipped invalid hours, and claimed unattended safety and constant memory. Version 1.1 removes those promises. It handles a bounded range of at most 1,000 rows and fails before writes if input validation or backup creation fails.

The data model

The complete download registers a public listing post type and a dedicated listing_status taxonomy. It owns four native text-meta keys: phone, address, hours (a JSON string) and permanently_closed. It also updates the post title from the name column. The separate listing_id meta value is the immutable join key.

Install this example on a staging site with that data model. If an existing directory plugin already owns the post type, taxonomy or fields, adapt its API instead of registering conflicting definitions. ACF Repeaters, image fields, business-directory plugin internals and WooCommerce products need their own write/restore logic.

The plugin requires PHP 8.1+, WP-CLI, PHP OpenSSL and a working wp db export command. The full source is te-directory-sync.php; put it in wp-content/plugins/te-directory-sync.php and activate it:

wp plugin activate te-directory-sync
wp rewrite flush

Flush rewrite rules once after registration, not on every import. Create/list the directory's posts and assign a unique listing_id to each before running the sync. This updater does not create missing listings or delete rows omitted from the sheet.

The sheet

Keep this exact header order. These rows are fictional input examples, not claims about real businesses:

name is the desired title, not the lookup key. A renamed business still resolves through listing_id. Duplicate IDs in the sheet, multiple matching WordPress posts, duplicate ID meta rows and unknown IDs stop the entire range before writes.

The default range is Listings!A1:F1001: one header plus at most 1,000 listings. For another range, include the header as its first returned row. Larger sheets need deliberate partitions; rows outside the requested range are untouched. A bounded query is not proof of constant memory, and plugins can add their own processing costs.

Blank phone/address cells explicitly clear those fields. Blank hours preserve the old schedule; closed explicitly sets every day to closed. permanently_closed accepts yes, no or blank (blank means open), and rejects other spellings. Review these meanings with the person maintaining the sheet before the first run.

Authenticating and reading the sheet

Create a service account, enable the Sheets API and share this sheet with its email as a viewer. Save the JSON key outside every served directory, restrict it to the WP-CLI operating-system user, and never commit it. The plugin rejects a key inside WordPress or with group/world-readable permissions.

It signs an RS256 JWT for Google's token endpoint with the read-only Sheets scope, checks signing and HTTP/JSON failures, then requests the selected range with spreadsheets.values.get. Tokens and keys are not printed. Service-account access is limited by both the OAuth scope and the sheet's sharing permissions.

A failed request is an error, not an empty source-of-truth sheet. A published CSV is a different privacy and authentication choice, covered in Google Sheets CSV versus the API.

Parsing structured hours

The parser supports one same-day opening interval per day, forward day ranges such as Mon-Fri, and hours from 00:00 through 23:59. It normalizes 7:00 to 07:00, rejects overlapping clauses and validates every clause before any listing changes.

$hours = te_directory_parse_hours( 'Mon-Fri 7:00-15:00; Sat-Sun 8:00-14:00' );
// $hours['mon'] is array( 'open' => '07:00', 'close' => '15:00' ).

$preserve = te_directory_parse_hours( '' ); // null: leave existing hours alone.
$closed = te_directory_parse_hours( 'closed' ); // Every day explicitly null.

// Each throws; none silently becomes an empty schedule:
// te_directory_parse_hours( 'Mon 25:00-26:00' );
// te_directory_parse_hours( 'Fri-Mon 9:00-17:00' );
// te_directory_parse_hours( 'Mon-Fri 9:00-17:00; Fri 10:00-18:00' );

Overnight openings, split shifts, holiday exceptions and 24:00 need a richer model and are deliberately rejected here. These local wall-clock strings do not implement an "open now" feature by themselves; that needs the listing's timezone and exception rules. Validate any emitted LocalBusiness data against what the page actually displays.

Closed, not deleted

When permanently_closed is yes, the command writes the flag and assigns the closed term. An open row gets the open term and an empty closed flag. It reconciles the term even if the meta flag already matches, so a prior partial write can be detected and repaired.

The template still has to display the closure accurately and exclude closed places from open-business filters. The plugin does not supply a theme. Keeping a useful historical listing at its existing URL can help visitors; it does not guarantee rankings. Do not publish a misleading active-business page merely to retain traffic.

The command

The command builds and validates the complete range before changing any listing. For each stable ID it reads at most two matching posts, enough to detect an ambiguous match. This is bounded per-ID lookup, not a claim of one database query for the whole directory.

Metadata keys must use the exact documented spelling. The command checks the database's own comparison rules and rejects equivalent spellings such as Phone alongside phone before writes. This prevents a case-insensitive database update from changing a field that an exact-key cached read missed. It repeats the check when rechecking each listing before mutation; other writers still need to be paused.

A live run follows this sequence:

1. Acquire a file lock in the private backup directory. Other writers still need to be paused.
2. Read and validate the sheet, headers, IDs, values and existing field shapes.
3. Export the database with WP-CLI and require a successful exit code and a nonempty private file.
4. Write and synchronize the complete change plan to a private JSON-lines journal.
5. Recheck each listing's prior state, journal the attempt, apply changed fields, verify the final state and journal success.

The exporter uses --single-transaction --skip-lock-tables, appropriate for a database whose consistency needs are satisfied by a transactional dump. Check table engines and your backup procedure first; mixed/nontransactional tables need a different backup strategy. The full database export is broader than this one post type. Test restoring it on an isolated copy before relying on it.

wp_update_post(..., true), metadata return values and taxonomy errors are checked. A failure stops the command with a nonzero exit status. Previously changed listings may remain changed: the command is not a database transaction and does not roll back external plugin side effects.

Run it

Pause competing imports and editors for the maintenance window. Preview the complete range, then create a private backup directory owned by the WP-CLI user:

wp te-directory pull --sheet=YOUR_SHEET_ID \
  --key=/srv/secrets/directory-sa.json

install -d -m 700 "$HOME/te-directory-backups"
wp te-directory pull --sheet=YOUR_SHEET_ID \
  --key=/srv/secrets/directory-sa.json \
  --backup-dir="$HOME/te-directory-backups" --live

The code rejects WordPress, content and upload directories, including configured external paths, but cannot discover every other virtual host or filesystem alias. Confirm it is outside all served paths. The SQL backup and journal may contain private data; do not store them under uploads, an exposed home directory, or a shared log location.

The preview shows full before/after states, so treat its output as potentially private too. The live run prints the actual backup and journal paths and only reports success after verification.

Recovery and repeat runs

A second successful run against the same range should report no changes. That property does not make unattended scheduling automatically safe. Add alerting, credential management, retry policy, source-change review and operational ownership before scheduling it. The built-in lock only serializes runs using the same backup directory on the same filesystem.

If a run fails, stop other writers and compare the original plan, attempt records and current data. A missing success record can mean the process died after the database write. Use the recorded before-state for reviewed, targeted recovery; do not guess from a truncated terminal table.

For a whole-database rollback, rehearse this on an isolated copy first:

wp db import /srv/private/ACTUAL_BACKUP_FILENAME.sql

That command replaces database state with the saved dump. It can erase unrelated changes since the backup, so it belongs in a controlled rollback with a current backup and paused writers. The sample does not pretend a whole-site restore is a safe unattended undo button.

The updater also leaves geocoding to a separate job. An address change can make stored coordinates stale; the journal exposes the before/after address so that follow-up can be selected explicitly. See the native-meta safety guide for a narrower snapshot/restore example and the multi-site sheet workflow for per-site coordination.