Two-Way Sync Between WordPress and a Google Sheet

A two-way sync between WordPress and a Google Sheet means two things happening on a schedule: WordPress writes its current state back into the sheet so the sheet is never stale, and edits made in the sheet flow back into WordPress. The plumbing for both directions is the same Sheets API call you have already seen, run twice. The hard part, the part that quietly loses data if you ignore it, is deciding which side wins when the same row was edited in both places. This article builds the loop, and then spends most of its time on that decision, because a two-way sync that hasn't answered it is a data-loss bug with a cron schedule.

It assumes you have the two one-direction halves already: pushing edits from the sheet into WordPress with a REST endpoint, and pulling the sheet into WordPress with a service account. If you have read those, you have seen every API call here except one: the write back into the sheet.

The shape of it

            ┌──────────────── WordPress is newer ───────────────┐
            ▼                                                    │
      Google Sheet                                          WordPress
            │                                                    ▲
            └──────────────── the sheet is newer ────────────────┘

Two arrows, one decision per row: which direction does this row move, or does it move at all? Everything that follows is about making that decision deliberately. The naive version, "copy the sheet onto WordPress, then copy WordPress onto the sheet," runs both arrows blindly every cycle and is guaranteed to clobber somebody's edit.

The half you have not built: writing WordPress back into the sheet

Pulling a sheet reads it with spreadsheets.values.get. Writing WordPress back into the sheet is the mirror call, spreadsheets.values.batchUpdate, which sets the values of one or more ranges in a single request. Two things change from the read-only pull:

• The service account needs write access. Share the sheet with its email as Editor, not Viewer, and request the read-write scope https://www.googleapis.com/auth/spreadsheets (not ...spreadsheets.readonly).
• You POST a body of data entries, each a range plus a 2D values array, with a valueInputOption.

That last field matters more than it looks. RAW stores exactly what you send. USER_ENTERED parses each value as if a person had typed it into the cell, so "42" becomes the number 42, a date string becomes a date, and a leading = becomes a formula. For syncing data you almost always want RAW, so a SKU like =SUM or a price that should stay a string lands literally instead of turning into a formula or a coerced number.

Using the te_sheet_token() JWT minter (the same service-account helper used across the sheet cluster, included with the full command below, called here with the scope widened to read-write), the write is one function:

/**
 * Write rows back into the sheet in one batch. $updates is a list of
 * [ 'range' => 'Sheet1!C2', 'value' => 'published' ] entries.
 */
function te_sheet_write( $sheet_id, array $updates, array $key ): bool {
	$token = te_sheet_token( $key, 'https://www.googleapis.com/auth/spreadsheets' );
	if ( ! $token ) {
		return false;
	}

	$data = array();
	foreach ( $updates as $u ) {
		$data[] = array(
			'range'  => $u['range'],
			'values' => array( array( $u['value'] ) ),
		);
	}

	$url = sprintf(
		'https://sheets.googleapis.com/v4/spreadsheets/%s/values:batchUpdate',
		rawurlencode( $sheet_id )
	);

	$res = wp_remote_post( $url, array(
		'headers' => array(
			'Authorization' => "Bearer {$token}",
			'Content-Type'  => 'application/json',
		),
		'body'    => wp_json_encode( array(
			'valueInputOption' => 'RAW',
			'data'             => $data,
		) ),
	) );

	return 200 === (int) wp_remote_retrieve_response_code( $res );
}

One request updates every changed cell, however many rows moved. That batching is not a nicety: the Sheets API meters writes per minute (300 per project, 60 per user, per minute, returning 429 over the limit), so a thousand single-cell writes is a rate-limit incident waiting to happen, while one values:batchUpdate of a thousand ranges is a single billable call.

The actual problem: who wins?

Now the loop is buildable, and now it is dangerous. Picture one cycle: at 09:00 someone fixes a price in WordPress; at 09:15 someone fixes the title of the same product in the sheet; the sync runs at 09:20. Copy the sheet onto WordPress and you keep the new title but resurrect the old price. Then copy WordPress onto the sheet and you overwrite the new title with the old one. Both people made a correct edit. The blind sync destroyed one of them, and nobody got an error.

This is last-write-wins, and on its own it is not a strategy, it is the absence of one. The fix is to decide, before any write, which side is allowed to win, and the three ways to decide it scale with how much you are willing to build:

The honest answer for almost every WordPress-and-a-sheet setup is the first one. Price and stock are owned by WordPress (or WooCommerce); an editorial "notes" or "campaign" column is owned by the sheet. Each field flows in exactly one direction, the two arrows never touch the same cell, and there is no conflict to resolve because you designed it out. Reach for timestamps only on the handful of fields both sides really do edit.

A direction-aware sync

So the sync command does not "push" or "pull." It reads the sheet, reads WordPress, and for each field consults an ownership map that says which way that field is allowed to move. Owned-by-WordPress fields collect into a batch written back to the sheet; owned-by-sheet fields collect into post-meta writes; nothing is bidirectional at the field level.

<?php
/**
 * Plugin Name: TE Sheet Two-Way Sync
 * Plugin URI:  https://techearl.com/wordpress-google-sheet-two-way-sync
 * Description: Reconciles a Google Sheet and WordPress in both directions, one owner per field.
 * Author:      Ishan Karunaratne
 * Author URI:  https://techearl.com
 */

defined( 'ABSPATH' ) || exit;
if ( ! defined( 'WP_CLI' ) || ! WP_CLI ) { return; }

/** Base64url-encode (JWT segments are unpadded and use -_ instead of +/). */
function te_b64url( $s ) { return rtrim( strtr( base64_encode( $s ), '+/', '-_' ), '=' ); }

/**
 * Mint a short-lived service-account access token (JWT bearer grant).
 * Scope defaults to read-only; pass the read-write Sheets scope to write back.
 */
function te_sheet_token( array $key, string $scope = 'https://www.googleapis.com/auth/spreadsheets.readonly' ) {
	$now    = time();
	$header = te_b64url( wp_json_encode( array( 'alg' => 'RS256', 'typ' => 'JWT' ) ) );
	$claims = te_b64url( wp_json_encode( array(
		'iss'   => $key['client_email'],
		'scope' => $scope,
		'aud'   => 'https://oauth2.googleapis.com/token',
		'iat'   => $now,
		'exp'   => $now + 3600,
	) ) );
	$sig = '';
	openssl_sign( "{$header}.{$claims}", $sig, $key['private_key'], 'sha256WithRSAEncryption' );
	$jwt = "{$header}.{$claims}." . te_b64url( $sig );
	$res = wp_remote_post( 'https://oauth2.googleapis.com/token', array(
		'body' => array(
			'grant_type' => 'urn:ietf:params:oauth:grant-type:jwt-bearer',
			'assertion'  => $jwt,
		),
	) );
	return json_decode( wp_remote_retrieve_body( $res ), true )['access_token'] ?? '';
}

/** Read a sheet range over the Sheets API. Returns the raw rows, header included. */
function te_sheet_read( string $sheet_id, string $range, string $token ): array {
	$url = sprintf( 'https://sheets.googleapis.com/v4/spreadsheets/%s/values/%s', rawurlencode( $sheet_id ), rawurlencode( $range ) );
	$res = wp_remote_get( $url, array( 'headers' => array( 'Authorization' => "Bearer {$token}" ) ) );
	return json_decode( wp_remote_retrieve_body( $res ), true )['values'] ?? array();
}

class TE_Sheet_Sync_Command {

	/** Which side owns each column. Owned-by-WP flows out to the sheet; owned-by-sheet flows in. */
	private const OWNER = array(
		'price'  => 'wp',     // WordPress is the source of truth for price
		'stock'  => 'wp',     // and for stock
		'notes'  => 'sheet',  // the team owns the editorial notes column
	);

	/**
	 * Reconcile the sheet and WordPress. Dry run unless --live.
	 *
	 * ## OPTIONS
	 * --sheet=<id>  : Spreadsheet ID.
	 * --key=<path>  : Path to the service-account JSON key.
	 * [--range=<a1>]: Sheet range. Default: Sheet1!A:D
	 * [--live]      : Actually write. Without it, dry run.
	 */
	public function sync( $args, $assoc ): void {
		$key   = json_decode( file_get_contents( $assoc['key'] ), true );
		$range = $assoc['range'] ?? 'Sheet1!A:D';
		$live  = isset( $assoc['live'] );

		$token = te_sheet_token( $key, 'https://www.googleapis.com/auth/spreadsheets' );
		if ( ! $token ) { WP_CLI::error( 'Could not get an access token.' ); }

		$rows = te_sheet_read( $assoc['sheet'], $range, $token );
		$head = array_shift( $rows );          // e.g. [ post_id, price, stock, notes ]
		$cols = array_flip( $head );

		$to_wp    = array();   // sheet -> WordPress (owned-by-sheet fields)
		$to_sheet = array();   // WordPress -> sheet (owned-by-wp fields)

		foreach ( $rows as $i => $row ) {
			$post_id = (int) ( $row[ $cols['post_id'] ] ?? 0 );
			if ( ! $post_id ) { continue; }
			$rownum = $i + 2;  // 1-based, plus the header row

			foreach ( self::OWNER as $field => $owner ) {
				if ( ! isset( $cols[ $field ] ) ) { continue; }
				$sheet_val = (string) ( $row[ $cols[ $field ] ] ?? '' );
				$wp_val    = (string) get_post_meta( $post_id, $field, true );
				if ( $sheet_val === $wp_val ) { continue; }   // already in step

				if ( 'sheet' === $owner ) {
					$to_wp[] = array( 'id' => $post_id, 'field' => $field, 'new' => $sheet_val, 'dir' => 'to WP' );
				} else {
					$colA1     = chr( 65 + $cols[ $field ] );  // A, B, C ...
					$to_sheet[] = array( 'id' => $post_id, 'field' => $field, 'new' => $wp_val,
						'dir' => 'to sheet', 'range' => "Sheet1!{$colA1}{$rownum}", 'value' => $wp_val );
				}
			}
		}

		$plan = array_merge( $to_wp, $to_sheet );
		if ( empty( $plan ) ) { WP_CLI::success( 'Sheet and WordPress already in step.' ); return; }

		WP_CLI\Utils\format_items( 'table', $plan, array( 'id', 'field', 'dir', 'new' ) );

		if ( $live ) {
			foreach ( $to_wp as $u ) { update_post_meta( $u['id'], $u['field'], $u['new'] ); }
			if ( $to_sheet ) { te_sheet_write( $assoc['sheet'], $to_sheet, $key ); }
		}

		$mode = $live ? 'reconciled' : 'WOULD reconcile (dry run)';
		WP_CLI::success( sprintf(
			'%d %s: %d to WordPress, %d to the sheet.',
			count( $plan ), $mode, count( $to_wp ), count( $to_sheet )
		) );
	}
}
WP_CLI::add_command( 'te-sheet', 'TE_Sheet_Sync_Command' );

The command is self-contained: te_sheet_token() and te_sheet_read() (the same service-account helpers used across the sheet cluster) are included at the top, and te_sheet_write() is the values:batchUpdate function above. The whole command is just routing: owned-by-sheet differences go one way, owned-by-WordPress differences go the other, and a field that matches on both sides is left alone. There is no row where both arrows fire, so there is no conflict to lose.

Run it dry first, the same discipline as every write in this cluster. The dir column is the whole point: you can see, before anything is written, exactly which way each change is about to move.

If a field you expected to move the other way shows up going the wrong direction, the OWNER map is wrong, not the data, and you have caught it before a single cell changed.

Running it on a schedule

A two-way sync earns its keep by running unattended. There are two clocks you can drive it from, and for a server-side reconcile the WordPress one is the right choice.

On the WordPress side, do not lean on WP-Cron for this: it only fires when someone loads a page, so a low-traffic site can skip a scheduled sync for hours. Disable it and drive WP-CLI from real system cron instead:

// wp-config.php
define( 'DISABLE_WP_CRON', true );

# crontab: reconcile every 15 minutes, server-side, no browser involved
*/15 * * * * cd /var/www/site && wp te-sheet sync --sheet=SHEET_ID --key=/etc/te/sa-key.json --live >/dev/null 2>&1

The Apps Script side has its own scheduler, an installable time-driven trigger, if you would rather the sheet drive the cadence (for example to push the sheet's owned columns the moment after a nightly import):

// Run once to install; fires roughly every hour thereafter.
ScriptApp.newTrigger('teReconcile').timeBased().everyHours(1).create();

One caveat worth knowing: time-driven triggers fire within a window, not on the dot. An "every hour" trigger runs sometime inside the hour, so do not build anything that assumes it ran at exactly :00. For a reconcile that runs every 15 minutes anyway, the jitter is irrelevant; the system-cron line above is the one I reach for.

Keeping it current

This pattern has held since well before WordPress 5.x, and the only moving parts are on the Google side. Two things are worth knowing now that were not true when I first wired this up:

• Apps Script runs on V8. Google began migrating compatible scripts to the V8 runtime on February 18, 2020, so modern JavaScript (let/const, arrow functions, classes, template literals) works in the bound script. Older examples written for the legacy Rhino engine still run, but new code can use V8 syntax freely.
• Sheets API quotas, and a billing change on the horizon. Reads and writes are each capped at 300 per minute per project (60 per user), and exceeding that returns 429. Staying within those limits has been free; Google has signalled that over-quota usage is planned to start incurring Cloud billing charges later in 2026. Batching writes with values:batchUpdate, which this command already does, is what keeps you comfortably under the ceiling.

Neither changes the architecture. The ownership map is still what makes a two-way sync safe, and a service account writing batched values is still the whole mechanism.

Where this sits in the cluster

This is the keystone of the sheet-and-WordPress set: it reuses the push endpoint for the inbound direction when you want a human to trigger it, the service-account pull for the outbound read, and the same dry-run-then-live discipline as every other write in the cluster. The difference is only that it runs both directions and decides between them. To run that same sync across a fleet of separate installs rather than one, a fleet controller groups one sheet by a website column and fans each batch out to its own site. If the endpoint is doing anything you would hate to see replayed or forged, gate it the way the secure write-endpoint article lays out before you point a cron at it.