Sync WooCommerce Stock and Inventory From a Supplier Google Sheet

Syncing WooCommerce stock from a supplier's Google Sheet comes down to four steps: read the sheet with a service account, resolve each row's SKU to a product, set the quantity and status through the WooCommerce CRUD, and write only the rows whose stock actually moved. The supplier maintains the sheet (one row per SKU, a quantity, optionally a status); WordPress pulls it on a nightly cron and reconciles every product to match. No one logs into wp-admin, and the thousands of SKUs that did not change are skipped.

This is the WooCommerce inventory case of the service-account pull model: WordPress is the API client, reads the sheet itself, and applies each row. The difference here is what gets written. Stock is not a single meta value you can poke. It is managed state on the product, and the correct way to set it is the CRUD: load the product, call the stock setters, save. Do that and WooCommerce keeps its own lookup tables and caches consistent for you. Write the meta directly and you desync them.

The supplier sheet

One row per SKU. The join key is sku; qty is the on-hand quantity the supplier reports, and status is an optional explicit stock status when you do not want WooCommerce to infer it from the quantity.

status is one of instock, outofstock, or onbackorder. Leaving it blank is a valid choice: if you only fill in qty, let the quantity plus the product's backorders policy decide the status (more on that below). Variations are products too, each with its own SKU and its own stock, so a variation's row joins by the variation SKU exactly like a simple product does.

Resolve the SKU, not the post ID

A supplier knows its own part numbers, not your WordPress post IDs. So the join column is the SKU, and the first thing each row does is turn that SKU into a product ID:

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

wc_get_product_id_by_sku() resolves both simple products and variations, because variation SKUs are indexed the same way. A SKU with no match is a data problem worth surfacing (a typo in the feed, a discontinued line), so log it and move on rather than failing the whole run.

Set stock through the CRUD, not the meta

Here is the rule that matters: write stock through WC_Product, never with update_post_meta. WooCommerce stores stock as _stock and _stock_status meta, but those are not the whole story. Since WooCommerce 3.6 (April 2019) it also maintains a denormalized wc_product_meta_lookup table that powers fast catalog queries and the stock-status filters, and the CRUD is what keeps that table and the object cache in step with the meta. Poke _stock directly and the lookup table goes stale; the product shows one stock level in the database and another on the shop page.

The CRUD call is small. Load the product, turn managing stock on, set the quantity, set the status, save:

$p = wc_get_product( $product_id );

$p->set_manage_stock( true );      // quantities only matter if this is on
$p->set_stock_quantity( 42 );
$p->set_stock_status( 'instock' ); // instock | outofstock | onbackorder
$p->save();

set_manage_stock( true ) is the line people forget. If stock management is off, WooCommerce ignores the quantity entirely and the product is sold purely on its status. So if the supplier feed carries real counts, managing stock has to be on for those counts to mean anything. Turn it on as part of the sync.

set_stock_status() takes one of the three string values. You can set it explicitly from the sheet's status column, or you can leave the status alone and let WooCommerce derive it. When stock is managed and the quantity drops to zero, WooCommerce sets the status to outofstock on save (unless backorders are allowed, in which case it can become onbackorder). That behavior is governed by the product's backorders policy:

$p->set_backorders( 'no' );     // 'no' | 'notify' | 'yes'

no means a zero quantity goes straight to out of stock. notify allows backorders and shows a "available on backorder" notice. yes allows them silently. So you have two clean strategies, and you should pick one per sync:

• Status from the sheet. The supplier feed carries an authoritative status, you call set_stock_status() with it, and the quantity is informational. Use this when the supplier knows things WooCommerce cannot infer (a line is on backorder upstream even though your count is positive).
• Status from the quantity. You set only the quantity and the backorders policy, leave set_stock_status() out, and let WooCommerce compute the status on save. Simpler, and correct for the common case where zero on hand means out of stock.

Mixing them per row is fine as long as each row is internally consistent. The command below sets the quantity always, and applies an explicit status only when the sheet provides one.

The command

This registers wp te-stock sync. The command is self-contained: te_sheet_token() and te_sheet_read() (the same service-account helpers used across the sheet cluster, where the JWT-for-access-token dance is covered in full) are included at the top. It mints a token, reads the supplier range, and for each row resolves the SKU, compares the product's current quantity and status to the sheet, and writes only the rows that differ. Nothing is written without --live, exactly the dry-run, change-only discipline every write job in this cluster follows.

<?php
/**
 * Plugin Name: TE Stock Sync
 * Plugin URI:  https://techearl.com/woocommerce-bulk-update-stock-inventory
 * Description: WP-CLI command that pulls a supplier stock feed from a Google Sheet and applies it to WooCommerce by SKU.
 * 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_Stock_Sync_Command {

	/**
	 * Pull a supplier stock sheet and apply quantity + status to WooCommerce by SKU.
	 *
	 * ## OPTIONS
	 * --sheet=<id>   : Spreadsheet ID.
	 * --key=<path>   : Path to the service-account JSON key.
	 * [--range=<a1>] : Sheet range. Default: Stock!A:C (sku, qty, status)
	 * [--live]       : Actually write. Without it, dry run.
	 */
	public function sync( $args, $assoc ) {
		$key   = json_decode( file_get_contents( $assoc['key'] ), true );
		$range = $assoc['range'] ?? 'Stock!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] ?? '' ) );
			$qty    = (int) ( $row[1] ?? 0 );
			$status = trim( (string) ( $row[2] ?? '' ) ); // optional
			if ( '' === $sku ) { continue; }

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

			$p           = wc_get_product( $product_id );
			$old_qty     = (int) $p->get_stock_quantity();
			$old_status  = $p->get_stock_status();
			$want_status = '' !== $status ? $status : $old_status;

			// change-only: skip rows already at the target
			if ( $p->managing_stock() && $old_qty === $qty && $old_status === $want_status ) {
				continue;
			}

			$changes[] = array(
				'sku'    => $sku,
				'old'    => "{$old_qty} / {$old_status}",
				'new'    => "{$qty} / {$want_status}",
			);

			if ( $live ) {
				$p->set_manage_stock( true );
				$p->set_stock_quantity( $qty );
				if ( '' !== $status ) {
					$p->set_stock_status( $status );
				}
				$p->save();
			}
		}

		if ( empty( $changes ) ) { WP_CLI::success( 'Stock already matches the supplier sheet.' ); return; }
		WP_CLI\Utils\format_items( 'table', $changes, array( 'sku', 'old', 'new' ) );
		$mode = $live ? 'updated' : 'WOULD update (dry run)';
		WP_CLI::success( sprintf( '%d products %s from the supplier sheet.', count( $changes ), $mode ) );
	}
}
WP_CLI::add_command( 'te-stock', 'TE_Stock_Sync_Command' );

When an explicit status is in the sheet I set it; when it is blank I leave set_stock_status() out and let the save derive it from the quantity and the product's backorders policy. The old/new columns pack quantity and status together so a glance at the dry run tells you both what is moving and which direction.

Run it

Dry run first, always. It reads the supplier feed, resolves every SKU, and prints the products whose stock would change without touching the database:

Only SKUs whose quantity or status actually moved show up, which is the whole point. A supplier sheet might list ten thousand SKUs; on a normal night a few hundred move. The change-only check (managing_stock() is on and the quantity and status already match) means the command writes those few hundred and skips the rest, so the run is fast and the activity log is honest about what it touched.

Put it on a nightly cron

A supplier feed updates on the supplier's schedule, not when a human happens to look, so this command's natural home is a nightly cron. The cleanest way to run it is a real system cron entry that calls WP-CLI directly, rather than relying on WordPress's traffic-triggered pseudo-cron:

# /etc/cron.d/te-stock-sync  (02:30 nightly)
30 2 * * * www-data cd /var/www/shop && wp te-stock sync --sheet=THE_SHEET_ID --key=/etc/te/sa-key.json --live >> /var/log/te-stock.log 2>&1

Running through the system scheduler is the recommended way to drive WP-Cron reliably: it fires on time even with no site traffic, which is exactly what an overnight inventory job needs. Pipe the output to a log so you have a nightly record of what the sync moved. If you run this kind of scheduled WP-CLI job across more than one store, the harness for doing that safely at scale (lock files, per-site logging, timeouts) is its own topic, covered in running bulk scripts with WP-CLI and, for a whole fleet of stores off one control sheet, in managing multiple WordPress sites from a Google Sheet.

Common pitfalls

A few things bite people the first time they wire a supplier feed into WooCommerce stock:

• Manage stock left off. The single most common one. If set_manage_stock( true ) is not called, WooCommerce ignores the quantity and the product sells on its status alone, so your supplier counts do nothing. Turn it on in the sync.
• Writing _stock meta directly. It looks like it works, then the catalog filters and the wc_product_meta_lookup table show stale numbers because the meta and the lookup table diverged. Always go through the CRUD setters and save().
• Forgetting variations carry their own stock. A variable product's stock can be managed at the parent or per variation. If the feed gives variation-level SKUs, resolve and set each variation; setting the parent does not cascade to children.
• Expecting status to follow quantity when stock is not managed. The auto-derive (zero quantity becomes out of stock on save) only happens when managing stock is on. With it off, you must set the status explicitly.

Get those right and the loop is dependable: the supplier owns the sheet, the nightly cron owns the reconciliation, and WooCommerce stays in step without anyone touching wp-admin. The same pattern, pointed at a regular_price/sale_price column instead of stock, is how I keep WooCommerce prices and sales in sync from a sheet; the SKU-resolved CRUD write is identical, only the setters change.