Bulk-Update WordPress Custom Fields From a Google Sheet

function te_b64url( $s ) { return rtrim( strtr( base64_encode( $s ), '+/', '-_' ), '=' ); }

/**
 * Mint a short-lived access token from the service-account key (JWT bearer grant).
 * The scope defaults to read-only; pass the read-write Sheets scope when you need
 * to write back into the sheet.
 */
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 row 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();
}

<?php
/**
 * Plugin Name: TE Sheet Pull
 * Plugin URI:  https://techearl.com/wordpress-bulk-update-custom-fields-google-sheet
 * Description: WP-CLI command that reads a Google Sheet via a service account and applies rows to WordPress.
 * Author:      Ishan Karunaratne
 * Author URI:  https://techearl.com
 */

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

class TE_Sheet_Pull_Command {

	/**
	 * Pull a sheet and apply a column to a post-meta key.
	 *
	 * ## OPTIONS
	 * --sheet=<id>   : Spreadsheet ID.
	 * --key=<path>   : Path to the service-account JSON key.
	 * --field=<key>  : The post-meta key to write.
	 * [--range=<a1>] : Sheet range. Default: Sheet1!A:B
	 * [--live]       : Actually write. Without it, dry run.
	 */
	public function pull( $args, $assoc ) {
		$key   = json_decode( file_get_contents( $assoc['key'] ), true );
		$range = $assoc['range'] ?? 'Sheet1!A:B';
		$field = $assoc['field'];
		$live  = isset( $assoc['live'] );

		$token = te_sheet_token( $key );
		if ( ! $token ) { WP_CLI::error( 'Could not get an access token.' ); }

		$rows = te_sheet_read( $assoc['sheet'], $range, $token );
		array_shift( $rows ); // drop the header row

		$changes = array();
		foreach ( $rows as $row ) {
			$post_id = (int) ( $row[0] ?? 0 );
			$value   = (string) ( $row[1] ?? '' );
			if ( ! $post_id || '' === $value ) { continue; }
			$current = (string) get_post_meta( $post_id, $field, true );
			if ( $current === $value ) { continue; } // change-only

			$changes[] = array( 'post_id' => $post_id, 'old' => $current, 'new' => $value );
			if ( $live ) { update_post_meta( $post_id, $field, $value ); }
		}

		if ( empty( $changes ) ) { WP_CLI::success( 'Sheet and WordPress already match.' ); return; }
		WP_CLI\Utils\format_items( 'table', $changes, array( 'post_id', 'old', 'new' ) );
		$mode = $live ? 'updated' : 'WOULD update (dry run)';
		WP_CLI::success( sprintf( '%d posts %s from the sheet.', count( $changes ), $mode ) );
	}
}
WP_CLI::add_command( 'te-sheet', 'TE_Sheet_Pull_Command' );