Bulk Schedule WooCommerce Sale Prices From a Google Sheet

Running a scheduled WooCommerce sale from a Google Sheet comes down to four steps: read a sheet of sku, sale_price, sale_from, sale_to; resolve each SKU to a product; set the sale price and the two on-sale dates through the CRUD; and call save(). WooCommerce does the rest, it flips _price to the sale price when the window opens and back to the regular price when it closes, on its own scheduled event. You do not cron the flip yourself. This is the price-and-sale vertical of the same pull model I use for bulk product edits from a sheet: a WP-CLI command, a service account, change-only writes, dry-run by default.

The reason a sheet is the right control surface here is that a sale is a list, not a workflow. Marketing hands you a tab of "these 4,000 SKUs, 20% off, May 1 to May 7." That is exactly the shape of a spreadsheet, and the job is to translate each row into the right pair of WooCommerce setters without anyone opening the product editor.

The sheet

One row per product. The join key is sku; the three other columns are the sale price and the window it runs in.

Dates are plain YYYY-MM-DD. Leave sale_price blank in a row to clear an existing sale on that product, which is how you end a sale early or take a single SKU out of one. More on that below.

Why scheduled dates, not a cron that flips the price

The instinct is to write a job that sets the sale price on May 1 and another that removes it on May 8. Do not. WooCommerce already has this built in. When you set date_on_sale_from and date_on_sale_to on a product, WooCommerce stores both and schedules its own events: at the start date the active price (_price) becomes the sale price, and at the end date it reverts to the regular price. The flip happens through WooCommerce's wc_scheduled_sales cron hook, not anything you write.

That matters for correctness, not just convenience. _price is a derived value, a synced copy of either _regular_price or _sale_price depending on whether a sale is active right now. If you write _price directly with a cron of your own, you fight WooCommerce's own scheduler and you desync the lookup tables. As of WooCommerce 3.6 (April 2019) there is a dedicated wc_product_meta_lookup table that the catalog, sorting, and filtering read from, and it is populated from the CRUD save, not from raw meta writes. The lesson is the same one that holds for every WooCommerce field: go through wc_get_product() and the setters, never poke wp_postmeta by hand. Set the dates, call save(), and let WooCommerce own the timing.

A note on time zones, because it bites people: those dates are interpreted in your site time zone (Settings, General), not UTC and not the viewer's locale. A sale_to of 2019-05-08 means end of that day in site time, so the sale runs through the whole of May 8. If your store time zone is set to a UTC offset rather than a named city, daylight-saving shifts can move the effective cutoff by an hour; a named zone (for example America/New_York) handles DST correctly. Set the store time zone once and the sheet dates line up with what shoppers see.

The setters

The whole apply step for one row is four method calls. Setting a sale:

$p = wc_get_product( $product_id );
$p->set_sale_price( '14.99' );
$p->set_date_on_sale_from( '2019-05-01' );
$p->set_date_on_sale_to( '2019-05-08' );
$p->save();

Clearing a sale is the same shape with an empty sale price (and the dates wiped so no stale window lingers):

$p = wc_get_product( $product_id );
$p->set_sale_price( '' );
$p->set_date_on_sale_from( '' );
$p->set_date_on_sale_to( '' );
$p->save();

These setters and wc_get_product() have existed since the WooCommerce 3.0 CRUD landed (April 2017), so they are safe ground for any current store. To resolve a SKU to an ID, use wc_get_product_id_by_sku( $sku ), which returns 0 if the SKU is unknown so you can report misses instead of silently skipping them.

If the sheet holds a percentage rather than an absolute price (a column that says 20 for "20% off"), compute the sale price in the script from the product's own regular price:

$regular    = (float) $p->get_regular_price();
$sale_price = round( $regular * ( 1 - $percent / 100 ), 2 );

Doing the math against each product's get_regular_price() means one "20% off" column in the sheet correctly produces a different sale price per SKU, and round( ..., 2 ) keeps it to clean currency cents.

The command

This registers wp te-sale apply. 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. It applies the change-only and dry-run discipline from the safe batch updater, and writes nothing without --live.

<?php
/**
 * Plugin Name: TE WooCommerce Sale Scheduler
 * Plugin URI:  https://techearl.com/woocommerce-bulk-update-prices-and-sales
 * Description: WP-CLI command that reads a sheet of SKU + sale price + dates and schedules WooCommerce sales through the CRUD.
 * 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_Sale_Sync_Command {

	/**
	 * Apply a sheet of scheduled sales to WooCommerce.
	 *
	 * ## OPTIONS
	 * --sheet=<id>   : Spreadsheet ID.
	 * --key=<path>   : Path to the service-account JSON key.
	 * [--range=<a1>] : Sheet range. Default: Sheet1!A:D (sku, sale_price, sale_from, sale_to)
	 * [--live]       : Actually write. Without it, dry run.
	 */
	public function apply( $args, $assoc ) {
		$key   = json_decode( file_get_contents( $assoc['key'] ), true );
		$range = $assoc['range'] ?? 'Sheet1!A:D';
		$live  = isset( $assoc['live'] );

		$token = te_sheet_token( $key );
		if ( ! $token ) { WP_CLI::error( 'Could not get a Google 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] ?? '' ) );
			$from  = trim( (string) ( $row[2] ?? '' ) );
			$to    = trim( (string) ( $row[3] ?? '' ) );
			if ( '' === $sku ) { continue; }

			$id = wc_get_product_id_by_sku( $sku );
			if ( ! $id ) { WP_CLI::warning( "Unknown SKU: {$sku}" ); continue; }
			$p = wc_get_product( $id );

			// Current state, normalised to strings for comparison.
			$cur_price = (string) $p->get_sale_price();
			$cur_from  = $p->get_date_on_sale_from() ? $p->get_date_on_sale_from()->date( 'Y-m-d' ) : '';
			$cur_to    = $p->get_date_on_sale_to()   ? $p->get_date_on_sale_to()->date( 'Y-m-d' )   : '';

			if ( $cur_price === $price && $cur_from === $from && $cur_to === $to ) {
				continue; // change-only: already at target
			}

			$changes[] = array(
				'sku'   => $sku,
				'price' => ( '' === $price ? '(clear)' : $price ),
				'from'  => $from,
				'to'    => $to,
				'was'   => ( '' === $cur_price ? '(none)' : $cur_price ),
			);

			if ( $live ) {
				$p->set_sale_price( $price );           // '' clears the sale
				$p->set_date_on_sale_from( $from );      // '' wipes the window
				$p->set_date_on_sale_to( $to );
				$p->save();                              // WooCommerce schedules the flip/unflip
			}
		}

		if ( empty( $changes ) ) { WP_CLI::success( 'Every product already matches the sheet.' ); return; }
		WP_CLI\Utils\format_items( 'table', $changes, array( 'sku', 'price', 'from', 'to', 'was' ) );
		$mode = $live ? 'scheduled' : 'WOULD schedule (dry run)';
		WP_CLI::success( sprintf( '%d sales %s from the sheet.', count( $changes ), $mode ) );
	}
}
WP_CLI::add_command( 'te-sale', 'TE_Sale_Sync_Command' );

The change-only check compares all three fields (price plus both dates) against what the product already holds, normalising WooCommerce's WC_DateTime objects back to Y-m-d strings so the comparison is apples to apples. A row that already matches is skipped, which is what makes the command idempotent: run it twice and the second run reports nothing to do. That same property is what lets you run it on a cron without it churning the catalog or re-saving thousands of unchanged products on every pass.

Run it

Dry run first, every time. It reads the sheet, resolves each SKU, compares against the live product, and prints the rows it would touch without writing a thing:

The was column is the sale price the product had before; the price, from, and to columns are what the sheet wants. A row showing (clear) in price is one where the sheet's sale_price cell was empty, so the command will remove the sale and wipe its dates. Rows already at target never appear, so re-running right after a live pass changes nothing.

Once the dry run reads correctly, add --live:

wp te-sale apply --sheet=1AbC...xyz --key=/srv/secrets/sa.json --live

WooCommerce stores the dates and registers its scheduled-sales events. On May 1 the price flips to the sale price across all 4,000 products without you doing anything; on the night of May 8 it reverts to regular. To pull a product out of the sale early, blank its sale_price cell in the sheet and re-run, or just edit sale_to to today and let WooCommerce expire it on schedule.

Putting it on a schedule

For a fixed promotion you run the command once and walk away, WooCommerce owns the start and end. But the pull model earns its keep when the sheet is a living source of truth: marketing edits the tab, and a nightly cron reconciles WooCommerce to it. Wire it the same way you would any WP-CLI job at scale, a system crontab calling wp te-sale apply --live, and because the command is change-only it does real work only on the nights the sheet actually changed.

If you run more than one store, the same command fans out across a fleet: keep one sheet tab per site and loop the CLI call over each install, which is the manage-many-sites-from-a-sheet pattern applied to pricing. And if you would rather the sheet push the moment someone hits "go" instead of waiting for the cron, the REST push endpoint is the on-demand half of the same toolkit; the trade-offs between pulling on a schedule and pushing on a click are the same ones the custom-fields pull twin lays out.

What this does not change

This vertical is sale pricing only: sale_price and the two on-sale dates. It deliberately leaves regular_price alone, because a markdown campaign should not touch the list price. To change regular prices, restock, or edit titles and attributes from a sheet, that is the general product updater; to drive stock quantities and stock status from a sheet, that is the inventory sync. All three share the same skeleton (service-account read, SKU resolve, CRUD save, change-only, dry-run) and differ only in which setters they call, so once you have one running the others are a column swap.