Bulk-Update WooCommerce Products (Prices, Stock) From a Google Sheet

Bulk-updating WooCommerce products from a Google Sheet comes down to three steps: read the supplier sheet, resolve each row to a product by its SKU, and apply the new price and stock through the WooCommerce CRUD. The one rule that decides whether this works or quietly corrupts your catalog: write through WC_Product setters and save(), never poke the _price post meta directly. This is the WooCommerce-shaped version of the service-account pull I use for plain custom fields, with one extra trap that the custom-field version does not have.

The sheet comes from whoever owns the numbers: a supplier price list, a merch spreadsheet, a stocktake. It has a sku column and the columns you want to push (here, price and stock). A WP-CLI command reads it with a service account, compares each row to what the store currently has, and writes only the rows that differ, dry-run by default.

The sheet

One row per product, keyed on sku because that is the identifier the supplier and the store already share. Internal post IDs mean nothing to a supplier; SKUs do.

The job is to make WooCommerce match those numbers, and only touch products whose price or stock actually changed.

The footgun: do not write _price directly

This is the mistake that makes a bulk WooCommerce update look like it worked and then misbehave for weeks. WooCommerce stores a product's price across three meta keys, not one:

• _regular_price is the price you set.
• _sale_price is the discounted price, if any.
• _price is a derived, synced copy: the value WooCommerce actually uses for display, sorting, and the price filter. It equals _sale_price when a sale is active, otherwise _regular_price.

So the obvious-looking shortcut is wrong:

// WRONG. Do not do this.
update_post_meta( $post_id, '_price', '19.99' );
update_post_meta( $post_id, '_stock', '42' );

That writes one of the three price keys and leaves the other two stale. Now _price says 19.99 while _regular_price still says the old number. The product can display one price and sort or filter by another, sale logic reads the wrong field, and you have skipped the product object's cache invalidation entirely, so transients and any object cache keep serving the old value until something else happens to bust them. The bug does not show up on the product you are looking at; it shows up three pages deep in a "sort by price low to high" listing that nobody notices is wrong.

The fix is to never set _price yourself. Set the inputs through the CRUD and let WooCommerce compute and sync _price for you, the same way an admin saving the product in wp-admin would:

// RIGHT. Let the CRUD compute and sync _price.
$id = wc_get_product_id_by_sku( $sku );
$p  = wc_get_product( $id );

$p->set_regular_price( '19.99' );   // a price is a string in WooCommerce
$p->set_stock_quantity( 42 );
$p->set_manage_stock( true );       // tell Woo to track this quantity
$p->save();                          // recalculates _price, busts caches

save() is the line that matters. It recomputes _price from _regular_price/_sale_price, writes all the meta in one consistent pass, and invalidates the product's caches. The CRUD data stores shipped in WooCommerce 3.0 (April 2017), so by 2018 this is the supported, boring way to mutate a product, no excuse to touch raw meta.

One more thing worth knowing: prices are strings, not floats. '19.99', not 19.99. WooCommerce stores and compares them as strings, and passing a float invites float-formatting surprises (19.9899999). Keep the value as text the whole way from the sheet cell to the setter.

Updated note: WooCommerce 3.6 (April 2019) later added the wc_product_meta_lookup table, a flat index of price, stock, SKU, and rating that powers product queries and sorting. After 3.6 the CRUD also keeps that lookup row in sync on save(), which makes poking _price by hand even more clearly wrong: now you desync three meta keys and a separate lookup table. The 2018 reason (the _price derived-copy desync plus caches) was already enough; 3.6 just raised the stakes.

Reading the sheet with a service account

The store needs to read the sheet on its own, on a schedule, with nobody logged in. That is the pull model, and it needs WordPress to authenticate to Google as a service account: create one in the Google Cloud console, enable the Google Sheets API, download its JSON key, and share the sheet with the service account's email (Viewer is enough). The key lives outside the web root and is referenced by path, never committed.

Minting the token is the same JWT-bearer dance used across the sheet cluster: the te_sheet_token() helper signs a JWT with openssl_sign and exchanges it for an access token, and te_sheet_read() then pulls the range over the Sheets API. Both are included at the top of the command below, so this file runs on its own; the same helpers (and the JWT derivation in full) are covered in bulk-updating custom fields from a sheet. If a public CSV would do instead of the API, the trade-off is weighed in reading a Google Sheet in PHP: CSV vs the API.

The command

This registers wp te-woo pull. It reads the sheet, resolves each SKU to a product, compares the sheet's price and stock to the live values, and applies only the differences. It writes nothing unless you pass --live. The change-only and dry-run discipline is the same one I apply to every batch write: read the current value, skip rows already at target, log old to new, and only touch the database with an explicit flag.

<?php
/**
 * Plugin Name: TE Woo Sheet Pull
 * Plugin URI:  https://techearl.com/woocommerce-bulk-update-products-google-sheet
 * Description: WP-CLI command that reads a Google Sheet of SKU/price/stock and updates WooCommerce products through the CRUD.
 * Version:     1.0.0
 * Author:      Ishan Karunaratne
 * Author URI:  https://techearl.com
 * License:     GPL-2.0-or-later
 * Text Domain: te-woo-sheet-pull
 */

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_Woo_Sheet_Pull_Command {

	/**
	 * Pull a SKU/price/stock sheet and update WooCommerce products.
	 *
	 * ## OPTIONS
	 * --sheet=<id>   : Spreadsheet ID.
	 * --key=<path>   : Path to the service-account JSON key.
	 * [--range=<a1>] : Sheet range. Default: Sheet1!A:C
	 * [--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:C';
		$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 ) {
			$sku   = trim( (string) ( $row[0] ?? '' ) );
			$price = trim( (string) ( $row[1] ?? '' ) ); // keep it a string
			$stock = (string) ( $row[2] ?? '' );
			if ( '' === $sku ) { continue; }

			$id = wc_get_product_id_by_sku( $sku );
			if ( ! $id ) {
				WP_CLI::warning( "No product for SKU {$sku}, skipping." );
				continue;
			}
			$p = wc_get_product( $id );
			if ( ! $p ) { continue; }

			$old_price = (string) $p->get_regular_price();
			$old_stock = (string) $p->get_stock_quantity();
			$diff      = array();

			if ( '' !== $price && $old_price !== $price ) {
				$diff['price'] = "{$old_price} -> {$price}";
				if ( $live ) { $p->set_regular_price( $price ); }
			}
			if ( '' !== $stock && $old_stock !== $stock ) {
				$diff['stock'] = "{$old_stock} -> {$stock}";
				if ( $live ) {
					$p->set_manage_stock( true );
					$p->set_stock_quantity( (int) $stock );
				}
			}

			if ( empty( $diff ) ) { continue; } // change-only

			if ( $live ) { $p->save(); } // recomputes _price, busts caches
			$changes[] = array(
				'sku'   => $sku,
				'price' => $diff['price'] ?? '(same)',
				'stock' => $diff['stock'] ?? '(same)',
			);
		}

		if ( empty( $changes ) ) { WP_CLI::success( 'Sheet and store already match.' ); return; }
		WP_CLI\Utils\format_items( 'table', $changes, array( 'sku', 'price', 'stock' ) );
		$mode = $live ? 'updated' : 'WOULD update (dry run)';
		WP_CLI::success( sprintf( '%d products %s from the sheet.', count( $changes ), $mode ) );
	}
}
WP_CLI::add_command( 'te-woo', 'TE_Woo_Sheet_Pull_Command' );

Two things in there earn their keep. wc_get_product_id_by_sku() resolves the supplier's SKU to a WooCommerce product ID; a SKU with no match is logged and skipped rather than silently ignored, which is how you catch a typo in the sheet or a product that was never created. And reading get_regular_price()/get_stock_quantity() before writing is what makes the run idempotent: run it twice in a row and the second run reports nothing to do, because every row already matches.

Run it

Dry run first, always. It reads the sheet, resolves SKUs, compares, and prints exactly what would change, without writing a thing:

wp te-woo pull --sheet=1AbC...XYZ --key=/srv/secrets/sa.json

Read the table, confirm the old-to-new moves are what you expect, then re-run with --live to apply them. Because the command is change-only, running --live a second time changes nothing: the store already matches the sheet. Put the live command on a cron (wp cron or a system crontab) and the supplier sheet becomes a source of truth the store reconciles to on a schedule, exactly the hands-off pull shape the custom-field version uses.

Variable products: set it on the variation

A variable product (a t-shirt in three sizes) has no price or stock of its own. Each variation is its own product, with its own SKU, its own _regular_price, and its own stock. So when the sheet's SKU belongs to a variation, the command resolves it to the variation and sets the price and stock there, which is exactly what wc_get_product_id_by_sku() does already: it returns the variation's ID, and wc_get_product() hands you a WC_Product_Variation whose setters and save() behave the same.

The only thing to get right is the SKU column. Give every variation its own SKU in WooCommerce (TS-BLK-M, TS-BLK-L), and put those variation SKUs in the sheet, not the parent's. The parent product is a container; the rows the supplier prices are the variations, so those are the rows in the sheet. No special-casing in the command, the same resolve-set-save runs for a simple product and a variation alike.

Scaling and scheduling

For one store this command is enough. For more than a few hundred products, lean on the patterns the cluster already covers rather than reinventing them:

• Run it under the WP-CLI harness so a long catalog sync gets batching, progress, and a clean exit code for cron and CI. The mechanics are in running bulk scripts with WP-CLI.
• Keep the writes safe: a backup before the first --live, the change-only skip on every run, and a log of every old-to-new move, all from the safe batch-update discipline.
• Fan it across a fleet: if the same supplier sheet feeds several stores, the multi-site fan-out pattern runs this command against each site in turn.

And if the data should flow the other way too (the store writing its live stock back into the sheet so the supplier sees what sold), that is a two-way sync, with one owner per column so the directions never fight over the same cell. While you are tightening the store, the WooCommerce performance pass is the companion piece to a catalog that changes on a schedule.