TechEarl

Populate a WordPress Events Calendar From a Google Sheet

Keep events (title, dates, venue, sold-out flag, ticket URL) in a Google Sheet and have WordPress pull them into an event CPT on a cron. Dates stored as sortable Y-m-d H:i:s, change-only, dry-run by default, so the calendar maintains itself.

Ishan Karunaratne⏱️ 10 min readUpdated
Share thisCopied
Maintain events in a Google Sheet; WordPress pulls them into an event CPT on a cron with sortable Y-m-d H:i:s date meta, a sold-out flag, change-only and dry-run.

Populating a WordPress events calendar from a Google Sheet comes down to four decisions: keep one row per event keyed by a stable event_id, have WordPress pull the sheet on a cron with a service account, store each date as a sortable Y-m-d H:i:s string in the site's own timezone, and apply only the rows that actually changed. The sheet is where a non-developer adds an event, flips a sold_out flag, or updates a ticket link; WordPress reconciles to it on a schedule, so the calendar stays current without anyone touching wp-admin. Because events are time-sensitive, the cron is what makes the whole thing self-maintaining: new rows appear as posts, the sold-out badge flips when the flag flips, and past events can be auto-archived.

This is the same service-account pull model used for custom fields, pointed at an events vertical. The two new problems an events calendar adds are storing dates so they sort correctly and querying for "upcoming" reliably. Both come down to not storing an ambiguous human date string.

The sheet

One row per event. The join key is event_id, a stable identifier you assign once and never reuse, not the title (titles get edited, and matching on a moving target creates duplicates).

event_idtitlestartendvenuesold_outtickets_url
EV-1001Spring Synth Night2022-05-14 20:002022-05-14 23:30The Glasshousenohttps://tix.example/ev-1001
EV-1002Saturday Matinee2022-05-21 14:002022-05-21 16:00The Glasshouseyeshttps://tix.example/ev-1002
EV-1003Late Jazz Session2022-06-04 21:002022-06-05 00:30Cellar Roomnohttps://tix.example/ev-1003

The start and end columns hold a human-readable local datetime. That is fine in the sheet; the whole job of the import is to turn those into something WordPress can sort and filter on. Note EV-1003 ends after midnight: the end date is the next calendar day, which is exactly why you store a real datetime and not just a date.

The event post type

The store is a plain event custom post type. A CPT plus meta keeps you out of plugin lock-in; if you already run The Events Calendar, skip to the variation below and reuse its meta keys instead.

php
<?php
add_action( 'init', 'te_register_event_cpt' );
function te_register_event_cpt() {
	register_post_type( 'event', array(
		'label'       => 'Events',
		'public'      => true,
		'has_archive' => true,
		'menu_icon'   => 'dashicons-calendar-alt',
		'supports'    => array( 'title', 'editor', 'thumbnail' ),
		'rewrite'     => array( 'slug' => 'events' ),
	) );
}

Each event carries a small set of meta keys: the start and end datetimes, a Unix timestamp of the start (the thing you actually sort and range-query on), the venue, the sold-out boolean, and the ticket URL. The event_id from the sheet is stored as meta too, so the importer can find an existing event by it.

Storing dates so they sort right

This is the part people get wrong. A date is only sortable and queryable if it is stored in a fixed, unambiguous format. Store two things per date:

  1. A normalized Y-m-d H:i:s string (2022-05-14 20:00:00), which sorts lexicographically in the same order it sorts chronologically and reads cleanly in the database.
  2. A Unix timestamp integer of the start, for range queries ("everything from now forward") that compare as plain numbers.

The trap is timezone. The sheet holds a wall-clock time a human typed (2022-05-14 20:00), and "8pm" means 8pm in the venue's timezone, which is the site's timezone, not UTC and not the server's. Never store the local string as if it were UTC, and never let PHP's default timezone decide. Parse the sheet's value against wp_timezone(), the site's configured zone:

php
/** Parse a human "Y-m-d H:i" from the sheet into the site timezone. Returns a DateTimeImmutable or null. */
function te_parse_event_datetime( $raw ) {
	$raw = trim( (string) $raw );
	if ( '' === $raw ) { return null; }
	$tz = wp_timezone();
	$dt = date_create_immutable( $raw, $tz );
	return $dt ?: null;
}

Building the DateTimeImmutable with wp_timezone() as the second argument anchors "8pm" to the site's zone. From there the normalized string is $dt->format('Y-m-d H:i:s') and the timestamp is $dt->getTimestamp() (correctly UTC-based, since a timestamp is an absolute instant). Storing both means your templates can render the friendly string and your queries can use the integer without re-parsing anything.

If you want the timestamp stored as true UTC for portability, it already is: getTimestamp() returns the absolute instant regardless of the zone you parsed in. The Y-m-d H:i:s string stays local on purpose so the calendar displays the time the event actually starts in its own city.

The sold-out flag

sold_out in the sheet is a human "yes"/"no". Store it as a clean boolean meta (1 or 0, or absent) so the template can badge it without string-guessing:

php
function te_truthy( $raw ) {
	return in_array( strtolower( trim( (string) $raw ) ), array( 'yes', 'y', 'true', '1', 'sold out', 'sold' ), true );
}

In the single-event template, a one-line check renders the badge:

php
if ( get_post_meta( get_the_ID(), 'event_sold_out', true ) ) {
	echo '<span class="event-badge event-badge--sold-out">Sold out</span>';
}

Because the flag rides in on the same pull, flipping sold_out to yes in the sheet and waiting for the next cron run (or running the command by hand) is all it takes to badge an event. If you want it to flip the instant the cell changes rather than on the next cron tick, an onEdit trigger pushing to WordPress is the lower-latency option, and pushing the sold-out state the other way (WordPress back into the sheet) is covered in the two-way sync.

The import command

This registers wp te-events pull. 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 of the file. It reads the sheet with a service account, finds each event by its event_id meta or creates it, and applies the change-only and dry-run discipline: read what WordPress currently has, skip rows already at target, log every old to new, and write nothing unless you pass --live.

php
<?php
/**
 * Plugin Name: TE Events Sheet Pull
 * Plugin URI:  https://techearl.com/wordpress-events-calendar-google-sheet
 * Description: WP-CLI command that reads an events Google Sheet via a service account and reconciles an event CPT, change-only.
 * Version:     1.0.0
 * Author:      Ishan Karunaratne
 * Author URI:  https://techearl.com
 * License:     GPL-2.0-or-later
 * Text Domain: te-events-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_Events_Sheet_Command {

	/**
	 * Pull an events sheet and reconcile the event CPT.
	 *
	 * ## OPTIONS
	 * --sheet=<id>    : Spreadsheet ID.
	 * --key=<path>    : Path to the service-account JSON key.
	 * [--range=<a1>]  : Sheet range. Default: Events!A:G
	 * [--archive]     : Move events whose end is in the past to status 'archived'.
	 * [--live]        : Actually write. Without it, dry run.
	 */
	public function pull( $args, $assoc ) {
		$key   = json_decode( file_get_contents( $assoc['key'] ), true );
		$range = $assoc['range'] ?? 'Events!A:G';
		$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 ) {
			$event_id = trim( (string) ( $row[0] ?? '' ) );
			$title    = trim( (string) ( $row[1] ?? '' ) );
			$start    = te_parse_event_datetime( $row[2] ?? '' );
			$end      = te_parse_event_datetime( $row[3] ?? '' );
			if ( '' === $event_id || ! $start ) { continue; }

			$fields = array(
				'event_start'    => $start->format( 'Y-m-d H:i:s' ),
				'event_end'      => $end ? $end->format( 'Y-m-d H:i:s' ) : '',
				'event_start_ts' => (string) $start->getTimestamp(),
				'event_venue'    => sanitize_text_field( (string) ( $row[4] ?? '' ) ),
				'event_sold_out' => te_truthy( $row[5] ?? '' ) ? '1' : '0',
				'event_tickets'  => esc_url_raw( trim( (string) ( $row[6] ?? '' ) ) ),
			);

			$post_id = te_find_event_by_id( $event_id );
			$diff    = te_event_diff( $post_id, $title, $fields );
			if ( empty( $diff ) ) { continue; } // change-only

			$changes[] = array(
				'event_id' => $event_id,
				'event'    => $post_id ? "#{$post_id}" : 'NEW',
				'changed'  => implode( ', ', array_keys( $diff ) ),
			);

			if ( $live ) {
				te_apply_event( $post_id, $event_id, $title, $fields );
			}
		}

		if ( $live && isset( $assoc['archive'] ) ) {
			te_archive_past_events();
		}

		if ( empty( $changes ) ) { WP_CLI::success( 'Calendar already matches the sheet.' ); return; }
		WP_CLI\Utils\format_items( 'table', $changes, array( 'event_id', 'event', 'changed' ) );
		$mode = $live ? 'reconciled' : 'WOULD reconcile (dry run)';
		WP_CLI::success( sprintf( '%d events %s from the sheet.', count( $changes ), $mode ) );
	}
}
WP_CLI::add_command( 'te-events', 'TE_Events_Sheet_Command' );

The helpers keep the command readable. te_find_event_by_id() resolves the stable key; te_event_diff() is what makes the run change-only by returning just the fields that differ from what is stored; te_apply_event() does the write:

php
function te_find_event_by_id( $event_id ) {
	$found = get_posts( array(
		'post_type'   => 'event',
		'post_status' => 'any',
		'numberposts' => 1,
		'fields'      => 'ids',
		'meta_key'    => 'event_id',
		'meta_value'  => $event_id,
	) );
	return $found ? (int) $found[0] : 0;
}

function te_event_diff( $post_id, $title, array $fields ) {
	$diff = array();
	if ( ! $post_id ) { return array( 'title' => $title ) + $fields; } // new event: everything changes
	if ( get_the_title( $post_id ) !== $title ) { $diff['title'] = $title; }
	foreach ( $fields as $k => $v ) {
		if ( (string) get_post_meta( $post_id, $k, true ) !== (string) $v ) { $diff[ $k ] = $v; }
	}
	return $diff;
}

function te_apply_event( $post_id, $event_id, $title, array $fields ) {
	$post_id = wp_insert_post( array(
		'ID'          => $post_id ?: 0,
		'post_type'   => 'event',
		'post_status' => 'publish',
		'post_title'  => $title,
	) );
	update_post_meta( $post_id, 'event_id', $event_id );
	foreach ( $fields as $k => $v ) {
		update_post_meta( $post_id, $k, $v );
	}
}

Storing the fields as native post meta is the simple default; if your events use ACF field groups instead, swap update_post_meta for update_field (the ACF vs native meta trade-off explains when it matters and when it does not).

Run it

Dry run first, every time. It reads the sheet, resolves each event_id, diffs against what WordPress holds, and prints only the events that would change without writing a thing:

A terminal running wp te-events pull as a dry run, showing a change-only table of events sourced from the Google Sheet: EV-1001 marked NEW with title and dates changing, EV-1002 with event_sold_out flipping, EV-1003 with its end datetime and venue changing, then a success line reporting the events that would be reconciled from the sheet.
The events pull as a dry run: only the rows that differ from WordPress appear, one for a brand-new event, one whose sold-out flag flipped, one whose venue and end time changed.

The changed column names exactly which fields differ, so a re-run right after a live pass shows nothing: the sheet and the calendar match. Add --live to write, and --archive to also retire past events in the same pass.

Querying upcoming events

With the start stored as a sortable Y-m-d H:i:s string and a timestamp integer, "upcoming events, soonest first" is a clean WP_Query. Query against the datetime meta with meta_type => 'DATETIME' so the comparison is chronological, not lexical-but-accidental, and order by the meta value:

php
$now = current_time( 'mysql' ); // 'Y-m-d H:i:s' in the site timezone

$upcoming = new WP_Query( array(
	'post_type'      => 'event',
	'posts_per_page' => 10,
	'meta_key'       => 'event_start',
	'orderby'        => 'meta_value',
	'meta_type'      => 'DATETIME',
	'order'          => 'ASC',
	'meta_query'     => array(
		array(
			'key'     => 'event_start',
			'value'   => $now,
			'compare' => '>=',
			'type'    => 'DATETIME',
		),
	),
) );

current_time('mysql') gives "now" in the site's timezone as a Y-m-d H:i:s string, which is exactly the format and zone the meta was stored in, so the >= comparison lines up. Setting meta_type and the meta_query type to DATETIME tells MySQL to cast the meta to a real datetime for the comparison and the sort; without it, an empty or odd value can sort wrong. This is precisely why the import stored a normalized string and not whatever the human typed.

Variation: The Events Calendar plugin

If you run The Events Calendar, do not invent your own CPT. Its events are the tribe_events post type, and it stores start and end in local time as Y-m-d H:i:s under _EventStartDate and _EventEndDate (with _EventStartDateUTC / _EventEndDateUTC companions for the UTC values). Point the importer at those keys instead of event_start / event_end, write the local-time string the same way (parsed against wp_timezone()), and the plugin's own calendar views pick the events up. The stable-key, change-only, dry-run discipline is identical; only the post type and meta key names change. Keep using your own event_id meta as the join key so the importer still matches reliably across title edits.

Putting it on a cron

The reason to build the pull rather than a one-off import is that events go stale on their own. Schedule the command and the calendar maintains itself:

bash
# crontab: reconcile the calendar from the sheet every night at 02:15,
# archiving anything that has already ended.
15 2 * * * cd /var/www/site && wp te-events pull --sheet=SHEET_ID --key=/etc/te/sa-key.json --live --archive >> /var/log/te-events.log 2>&1

A real system crontab calling WP-CLI beats wp-cron for anything scheduled, because wp-cron only fires on a page visit and a low-traffic events site might go hours between hits. For a fleet of event sites sharing one master sheet, the multi-site fan-out wraps this same command in a loop over many installs. New events get added as a row, the sold-out flag is a single cell edit, and the nightly run does the rest.

Sources

Authoritative references this article was fact-checked against.

TagsWordPressGoogle SheetsWP-CLIEvents CalendarCustom Post TypeService Account

Found this useful? Pass it on.

Copied

Ishan Karunaratne

Software Systems Architect · Senior Software Engineer · Engineering Leadership

Software systems architect and senior software engineer with more than two decades designing, building, and running production software, Linux systems, and DevOps infrastructure, and lately working AI into the stack. Now a CTO, though what I write here is drawn from the full arc of that work, across architecture, engineering, and operations, not any single job.

More by Ishan →LinkedIn
Keep reading

Related posts

WordPress

Update a WordPress Business Directory From a Google Sheet

Keep a directory of thousands of listings in sync with a Google Sheet a non-dev maintains: a WP-CLI pull command that reconciles each listing change-only, parses structured hours, and flips permanently-closed places to a closed status instead of deleting them.