To update WooCommerce products from a Google Sheet, resolve rows by SKU, validate the entire input, preview the changes, then write through WC_Product setters and save(). Direct changes to _price or _stock bypass product data-store behavior and can leave lookup data and caches inconsistent.
I separate the sheet download from the write. That gives the operator an immutable JSON input to review, archive and rerun, instead of reading a changing sheet halfway through a live operation. The workflow below handles regular prices, owned stock quantities, status-only inventory and scheduled sales for simple products and variations.
Sheet schema and SKU identity
Use these exact headers:
sku,regular_price,quantity,stock_status,sale_price,sale_from,sale_to| sku | regular_price | quantity | stock_status | sale_price | sale_from | sale_to |
|---|---|---|---|---|---|---|
| MUG-BLU | 24.99 | 7 | 19.99 | 2026-10-01 | 2026-10-07 | |
| TS-RED-M | 0 | |||||
| SERVICE-ONE | instock | |||||
| CAP-BLK | CLEAR |
Blank means leave unchanged. 0 is a real price or quantity. CLEAR is accepted only in sale_price and removes the sale price and both dates. A nonblank numeric sale price replaces its schedule: blank dates mean an open-ended boundary for that sale. Dates without a sale price are rejected. Each SKU must occur once and resolve to exactly one product or variation.
A quantity row requires stock already managed directly by that product/variation. A status-only row requires unmanaged stock. The updater deliberately rejects a variation inheriting its parent's stock: silently moving it into an independent stock pool would change fulfillment behavior. Change stock ownership separately, after checking the store configuration.
Read the sheet with a service account
Enable the Sheets API, create a service account and share the spreadsheet with its email as a viewer. Keep its JSON key outside the web root and source control. The official Python authentication/client libraries avoid hand-maintaining a JWT implementation:
python3 -m venv .venv
.venv/bin/pip install google-api-python-client google-authSave this as read-product-sheet.py. Format SKU and date columns as plain text in the sheet; the reader uses unformatted values so currency display formatting does not become a price string.
#!/usr/bin/env python3
"""Export a reviewed WooCommerce sheet to local JSON.
Author: Ishan Karunaratne - https://techearl.com/woocommerce-bulk-update-products-google-sheet
"""
import argparse
import json
import os
from decimal import Decimal
from google.oauth2 import service_account
from googleapiclient.discovery import build
parser = argparse.ArgumentParser()
parser.add_argument("--key", required=True)
parser.add_argument("--sheet", required=True)
parser.add_argument("--range", default="Products!A:G")
parser.add_argument("--output", required=True)
args = parser.parse_args()
credentials = service_account.Credentials.from_service_account_file(
args.key, scopes=["https://www.googleapis.com/auth/spreadsheets.readonly"]
)
service = build("sheets", "v4", credentials=credentials, cache_discovery=False)
values = service.spreadsheets().values().get(
spreadsheetId=args.sheet, range=args.range,
valueRenderOption="UNFORMATTED_VALUE",
).execute().get("values", [])
headers = ["sku", "regular_price", "quantity", "stock_status", "sale_price", "sale_from", "sale_to"]
if not values or values[0] != headers:
raise ValueError("Sheet headers do not match the required schema")
rows = []
for number, cells in enumerate(values[1:], 2):
if not any(str(cell).strip() for cell in cells):
continue
if len(cells) > len(headers):
raise ValueError(f"Extra columns in row {number}")
cells = cells + [""] * (len(headers) - len(cells))
for index in (0, 5, 6):
if not isinstance(cells[index], str):
raise ValueError(f"SKU and dates must be plain text, row {number}")
row = {}
for key, cell in zip(headers, cells):
if isinstance(cell, bool):
raise ValueError(f"Boolean value in row {number}")
row[key] = format(Decimal(str(cell)), "f") if isinstance(cell, (int, float)) else str(cell).strip()
rows.append(row)
if not rows:
raise ValueError("No product rows")
# Exclusive creation prevents an unnoticed overwrite of a reviewed input file.
fd = os.open(args.output, os.O_WRONLY | os.O_CREAT | os.O_EXCL, 0o600)
with os.fdopen(fd, "w", encoding="utf-8") as output:
json.dump(rows, output, ensure_ascii=False, indent=2)
output.write("\n")
print(f"Wrote {len(rows)} rows to {args.output}").venv/bin/python read-product-sheet.py \
--key=/srv/private/sheets-reader.json \
--sheet=YOUR_SPREADSHEET_ID \
--output=/srv/private/products-review.jsonDo not use a publicly published CSV for private supplier data. A failed API request raises an error here instead of pretending the sheet was empty and reporting success.
The footgun: do not write _price directly
For ordinary product pricing, _regular_price and _sale_price are inputs; the active price and lookup records also depend on sale timing and product type. Use WooCommerce's public object API:
$product = wc_get_product( $product_id );
if ( ! $product ) {
throw new RuntimeException( 'Product not found.' );
}
$product->set_regular_price( '24.99' );
$product->save();Normalize values before comparing them. A sheet value of 19.9 and a stored value of 19.90 should not create an endless stream of “changes.” Store prices using the configured currency precision, not an assumed two decimal places for every store.
A dry-run updater with a checked backup
This example requires PHP 8.1 or later for checked file synchronization. Save it as a private apply-products.php outside the web root. It runs through wp eval-file, so WordPress and WooCommerce are already loaded. The first argument is the reviewed JSON file; optional live and a new private backup filename enable writes.
This example keeps the input and plan in memory. Split very large catalogs into reviewed files of a suitable size. It is not a transaction with checkout: schedule quantity reconciliation for a controlled window, or use an inventory integration designed for concurrent orders and reservations.
<?php
/**
* Apply a reviewed WooCommerce product sheet.
* Author: Ishan Karunaratne - https://techearl.com/woocommerce-bulk-update-products-google-sheet
*/
function te_product_state( $p ) {
return array(
'regular_price' => $p->get_regular_price( 'edit' ),
'sale_price' => $p->get_sale_price( 'edit' ),
'sale_from' => $p->get_date_on_sale_from( 'edit' ) ? $p->get_date_on_sale_from( 'edit' )->getTimestamp() : null,
'sale_to' => $p->get_date_on_sale_to( 'edit' ) ? $p->get_date_on_sale_to( 'edit' )->getTimestamp() : null,
'manage_stock' => $p->get_manage_stock( 'edit' ),
'quantity' => $p->get_stock_quantity( 'edit' ),
'stock_status' => $p->get_stock_status( 'edit' ),
'backorders' => $p->get_backorders( 'edit' ),
);
}
function te_sheet_price( $value ) {
if ( ! preg_match( '/^(?:0|[1-9][0-9]*)(?:\.[0-9]+)?$/D', $value ) ) {
throw new RuntimeException( 'Use a nonnegative decimal price without currency symbols.' );
}
return wc_format_decimal( $value, wc_get_price_decimals() );
}
function te_sheet_date( $value, $end ) {
if ( '' === $value ) { return null; }
$date = DateTimeImmutable::createFromFormat( '!Y-m-d', $value, wp_timezone() );
if ( ! $date || $date->format( 'Y-m-d' ) !== $value ) {
throw new RuntimeException( 'Use an exact YYYY-MM-DD date.' );
}
return ( $end ? $date->setTime( 23, 59, 59 ) : $date )->getTimestamp();
}
function te_write_all( $handle, $text ) {
$length = strlen( $text );
$offset = 0;
while ( $offset < $length ) {
$written = fwrite( $handle, substr( $text, $offset ) );
if ( false === $written || 0 === $written ) {
throw new RuntimeException( 'Private file write failed.' );
}
$offset += $written;
}
if ( ! fflush( $handle ) || ! fsync( $handle ) ) {
throw new RuntimeException( 'Private file flush failed.' );
}
}
function te_private_file( $path ) {
$dir = realpath( dirname( $path ) );
if ( ! $dir || ! is_writable( $dir ) || ( fileperms( $dir ) & 0077 ) !== 0 ||
'/' !== substr( $path, 0, 1 ) ) {
throw new RuntimeException( 'Use an existing mode-0700 private directory and absolute filename.' );
}
$uploads = wp_get_upload_dir();
foreach ( array( ABSPATH, WP_CONTENT_DIR, $uploads['basedir'] ) as $public ) {
$public = realpath( $public );
if ( $public && ( $dir === $public || 0 === strpos( $dir . '/', $public . '/' ) ) ) {
throw new RuntimeException( 'Backup must be outside WordPress web, content and upload roots.' );
}
}
$old_mask = umask( 0077 );
$handle = fopen( $path, 'x' );
umask( $old_mask );
if ( ! $handle ) { throw new RuntimeException( 'Cannot create exclusive private file.' ); }
return $handle;
}
try {
if ( ! function_exists( 'wc_get_product' ) ) {
throw new RuntimeException( 'WooCommerce is not active.' );
}
$file = $args[0] ?? '';
$mode = $args[1] ?? 'dry-run';
if ( ! in_array( $mode, array( 'dry-run', 'live' ), true ) ) {
throw new RuntimeException( 'Mode must be dry-run or live.' );
}
$raw = file_get_contents( $file );
if ( false === $raw ) { throw new RuntimeException( 'Cannot read input.' ); }
$rows = json_decode( $raw, true, 512, JSON_THROW_ON_ERROR );
if ( ! is_array( $rows ) || ! $rows || array_keys( $rows ) !== range( 0, count( $rows ) - 1 ) ) {
throw new RuntimeException( 'Input must be a nonempty JSON list.' );
}
$headers = array( 'sku', 'regular_price', 'quantity', 'stock_status', 'sale_price', 'sale_from', 'sale_to' );
$seen = array();
$seen_ids = array();
$plan = array();
global $wpdb;
foreach ( $rows as $row ) {
if ( ! is_array( $row ) || array_keys( $row ) !== $headers ) {
throw new RuntimeException( 'Unexpected row schema.' );
}
foreach ( $row as $cell ) {
if ( ! is_string( $cell ) ) { throw new RuntimeException( 'All cells must be strings.' ); }
}
$sku = $row['sku'];
if ( '' === $sku || isset( $seen[ $sku ] ) ) {
throw new RuntimeException( 'Empty or duplicate sheet SKU.' );
}
$seen[ $sku ] = true;
$matches = $wpdb->get_col( $wpdb->prepare(
"SELECT DISTINCT p.ID FROM {$wpdb->posts} p
JOIN {$wpdb->postmeta} m ON m.post_id = p.ID
WHERE m.meta_key = '_sku' AND m.meta_value = %s
AND p.post_type IN ('product', 'product_variation')", $sku
) );
if ( 1 !== count( $matches ) ) {
throw new RuntimeException( 'SKU must resolve uniquely: ' . $sku );
}
$p = wc_get_product( (int) $matches[0] );
if ( ! $p || ! in_array( $p->get_type(), array( 'simple', 'variation' ), true ) ||
'trash' === $p->get_status( 'edit' ) ) {
throw new RuntimeException( 'Unsupported product type/status: ' . $sku );
}
$product_id = $p->get_id();
if ( 'variation' === $p->get_type() && $p->get_stock_managed_by_id() !== $product_id ) {
throw new RuntimeException( 'Inherited variation stock requires a separate ownership-aware updater: ' . $sku );
}
if ( isset( $seen_ids[ $product_id ] ) ) {
throw new RuntimeException( 'Multiple rows resolve to the same product: ' . $sku );
}
$seen_ids[ $product_id ] = true;
$before = te_product_state( $p );
if ( '' !== $row['regular_price'] ) {
$p->set_regular_price( te_sheet_price( $row['regular_price'] ) );
}
if ( '' !== $row['quantity'] ) {
if ( '' !== $row['stock_status'] || true !== $p->get_manage_stock( 'edit' ) ||
! preg_match( '/^(?:0|[1-9][0-9]*)$/D', $row['quantity'] ) ||
strlen( $row['quantity'] ) > 9 ) {
throw new RuntimeException( 'Quantity needs owned managed stock and a nonnegative integer: ' . $sku );
}
$p->set_stock_quantity( (int) $row['quantity'] );
}
if ( '' !== $row['stock_status'] ) {
if ( false !== $p->get_manage_stock( 'edit' ) ||
! in_array( $row['stock_status'], array( 'instock', 'outofstock', 'onbackorder' ), true ) ) {
throw new RuntimeException( 'Status-only updates require unmanaged stock: ' . $sku );
}
$p->set_stock_status( $row['stock_status'] );
}
if ( 'CLEAR' === $row['sale_price'] ) {
if ( '' !== $row['sale_from'] || '' !== $row['sale_to'] ) {
throw new RuntimeException( 'CLEAR must have blank dates.' );
}
$p->set_sale_price( '' );
$p->set_date_on_sale_from( null );
$p->set_date_on_sale_to( null );
} elseif ( '' !== $row['sale_price'] ) {
$sale = te_sheet_price( $row['sale_price'] );
if ( '' === $p->get_regular_price( 'edit' ) ||
(float) $sale >= (float) $p->get_regular_price( 'edit' ) ) {
throw new RuntimeException( 'Sale must be below a defined regular price: ' . $sku );
}
$from = te_sheet_date( $row['sale_from'], false );
$to = te_sheet_date( $row['sale_to'], true );
if ( null !== $from && null !== $to && $from > $to ) {
throw new RuntimeException( 'Sale starts after it ends: ' . $sku );
}
$p->set_sale_price( $sale );
$p->set_date_on_sale_from( $from );
$p->set_date_on_sale_to( $to );
} elseif ( '' !== $row['sale_from'] || '' !== $row['sale_to'] ) {
throw new RuntimeException( 'Dates require a sale price: ' . $sku );
}
$p->validate_props();
$after = te_product_state( $p );
if ( $before !== $after ) {
$plan[] = array( 'id' => $p->get_id(), 'sku' => $sku, 'before' => $before, 'after' => $after );
}
}
foreach ( $plan as $item ) {
WP_CLI::log( wp_json_encode( $item ) );
}
if ( 'live' !== $mode || ! $plan ) {
WP_CLI::success( count( $plan ) . ' planned changes; no writes.' );
return;
}
$backup_path = $args[2] ?? '';
if ( '' === $backup_path ) { throw new RuntimeException( 'Live mode requires a new private backup path.' ); }
$backup = te_private_file( $backup_path );
te_write_all( $backup, json_encode( array(
'site' => home_url(), 'input_sha256' => hash( 'sha256', $raw ), 'plan' => $plan,
), JSON_THROW_ON_ERROR | JSON_PRETTY_PRINT ) . "\n" );
fclose( $backup );
$journal = te_private_file( $backup_path . '.results.jsonl' );
foreach ( $plan as $item ) {
$p = wc_get_product( $item['id'] );
if ( ! $p || te_product_state( $p ) !== $item['before'] ) {
throw new RuntimeException( 'Product changed since planning; stopped: ' . $item['sku'] );
}
$want = $item['after'];
$p->set_regular_price( $want['regular_price'] );
$p->set_sale_price( $want['sale_price'] );
$p->set_date_on_sale_from( $want['sale_from'] );
$p->set_date_on_sale_to( $want['sale_to'] );
$p->set_stock_quantity( $want['quantity'] );
$p->set_stock_status( $want['stock_status'] );
$saved = $p->save();
$readback = $saved ? wc_get_product( $p->get_id() ) : false;
$ok = $readback && te_product_state( $readback ) === $want;
te_write_all( $journal, json_encode( array(
'id' => $item['id'], 'sku' => $item['sku'], 'verified' => $ok,
), JSON_THROW_ON_ERROR ) . "\n" );
if ( ! $ok ) { throw new RuntimeException( 'Write verification failed; stopped: ' . $item['sku'] ); }
}
fclose( $journal );
WP_CLI::success( count( $plan ) . ' verified product writes.' );
} catch ( Throwable $error ) {
WP_CLI::error( $error->getMessage() . ' A live run can be partially applied; inspect the backup and result journal before retrying.' );
}The SKU query is a read-only uniqueness check for WooCommerce's current WordPress product storage. Actual mutations use CRUD. If an extension replaces product storage or SKU resolution, adapt that lookup to its supported API and retest rather than assuming this SQL is portable.
Run a preview, then the live operation
wp eval-file /srv/private/apply-products.php /srv/private/products-review.json
wp eval-file /srv/private/apply-products.php /srv/private/products-review.json \
live /srv/private/product-backups/run-2026-10-01.jsonUse an operator-only directory with mode 0700 that is not exposed by any web-server alias. The script rejects paths beneath the WordPress/content roots, but it cannot discover every server alias. Keep a database backup as well as the per-product snapshot. Practice restoring the affected fields through CRUD on staging before the first live run. Do not blindly restore old stock after new orders have arrived.
The snapshot records all planned before/after values before the first mutation. The result journal records verified outcomes; a failed write stops the batch. This is recoverable evidence, not an automatic rollback or a guarantee of atomicity across products.
Supplier stock and managed inventory
Managed quantity and independent stock status are different models. When stock is managed, WooCommerce validates status using quantity, backorders and its configured out-of-stock threshold. Setting instock manually does not necessarily override those rules on save.
With status-only stock, quantity is not the authority. The updater rejects mixing quantity and status in a row. It preserves the existing backorder policy rather than silently changing whether customers may buy unavailable items.
A variable parent can manage a shared stock pool, or variations can own their stock. The combined updater accepts simple products and variations with unambiguous ownership; it rejects variable parents and inherited stock. For a shared parent-stock integration, design a stock-only update against that parent and test how every child inherits it. A variation SKU is not permission to split the shared pool.
Scheduled sale prices and date behavior
A date-only value passed straight to the generic sale-date setter is not an inclusive end-of-day instruction. This updater parses sale_from at local midnight and sale_to at 23:59:59 in wp_timezone(), then supplies timestamps. Use a named store timezone when daylight-saving rules matter.
WooCommerce's sale scheduling and product data store handle price activation. The site's scheduled-task system must actually run; the stored date is not a promise of a callback executing at the exact second on an idle or broken scheduler. Verify the active storefront price around both boundaries on staging, including a daylight-saving transition if relevant.
For percentage discounts, validate a percentage between 0 and 100 and convert against the regular price using the store's currency precision. Do not assume every currency uses two decimal places or accept a localized string such as $1,299.00 as a machine price.
Invalid rows, partial failures and verification
Before use, test duplicate and unknown SKUs, a variation inheriting stock, blank versus zero values, invalid dates such as February 30, a sale ending before it starts, a decimal using commas, a read-only backup directory and an existing backup filename. None should begin product writes.
Then test valid simple products and variations, a zero-stock transition with and without backorders, CLEAR, a scheduled sale and a repeat run. Compare product admin values, storefront prices, stock behavior and lookup-driven sorting. If a plugin modifies values on save, the read-back mismatch should stop the operation; investigate rather than suppressing it.
For scheduling, download a new uniquely named input, run the same validation, retain the snapshot/journal and prevent overlapping jobs with an OS lock. Do not repeatedly import an old sale schedule after WooCommerce has expired and cleared it. A large or continuously changing inventory needs pagination, reservations-aware reconciliation and explicit source ownership beyond this example. The safe batch-update guide and two-way sheet sync rules cover the surrounding operational decisions.
Sources
Authoritative references this article was fact-checked against.
- Sheets values and render optionsdevelopers.google.com
- Google service-account credentialsgoogle-auth.readthedocs.io
- WooCommerce CRUD objectsdeveloper.woocommerce.com
- Product validation and stock semanticswoocommerce.github.io
- WooCommerce date setterswoocommerce.github.io
- Parent and variation inventorywoocommerce.com
- Exclusive file creationphp.net
- Checked file writesphp.net
