Squarespace to Google Sheets order log (auto-sync every 30 min)

PromptCreate

Build a code workflow that runs on a cron trigger every 30 minutes and syncs new Squarespace orders into a Google Sheets order log for backup, bookkeeping, and ad-hoc reporting.

Inputs (configurable when the workflow is set up): the target Google Sheets spreadsheet ID, the tab name to append to (default 'Orders'), and an optional initial start date for the first run. Store the last successful run timestamp in workflow state so subsequent runs only pull new orders.

Each run should:

1. Read the last-synced timestamp from workflow state. If there is none, use the configured initial start date, or default to the current time minus 1 hour.

2. Call Squarespace Forms 'Retrieve All Orders' with a modifiedAfter (or createdAfter) date filter starting at that timestamp. Paginate through every page using the returned nextPageCursor until hasNextPage is false. Cursor and date range cannot be combined in the same request, so use the cursor on subsequent pages once the first page has been fetched. Always send a User-Agent header.

3. Build idempotency by first calling Google Sheets 'Get Values' on the order ID column of the configured tab (for example, range 'Orders!A:A' assuming column A holds the order ID). Collect the set of order IDs already present so we can skip them. If the sheet is empty or missing the header row, write the header row first using Google Sheets 'Append Values': Order Number, Created Date, Customer Name, Customer Email, Line Items, Subtotal, Shipping, Tax, Total, Fulfillment Status, Order ID.

4. For each fetched order whose ID is not already in the sheet, build a row with: order number, created date (ISO formatted), customer first and last name joined, customer email, a short line item summary (each item formatted like 'Qty x Product Name' joined by '; '), subtotal, shipping total, tax total, grand total, current fulfillment status, and the order ID. Use the order's currency code in the totals so multi-currency stores stay accurate.

5. Call Google Sheets 'Append Values' once with all the new rows batched together, using valueInputOption USER_ENTERED so dates and numbers render cleanly. Insert into the configured tab (for example, 'Orders!A:K').

6. After a successful append, update the last-synced timestamp in workflow state to the most recent order's createdOn (or the run start time if no new orders were found). On any error, do not advance the timestamp so the next run retries the same window.

Notes for the agent: Squarespace Forms uses an API key with the Orders permission. Pagination is cursor-based at 50 items per page, and the orders list envelope uses the 'result' key. Donations are not orders, so they will not appear here. Be defensive about null line items or addresses. Rate limit is 300 requests/minute. Google Sheets uses OAuth; the workflow needs spreadsheets write scope. Keep the order ID as a plain string so leading zeros are not eaten by Sheets auto-formatting (prefix with an apostrophe or use RAW for that column if needed).

Idempotency is critical: cursor pagination plus retries can otherwise double-write. The order ID lookup against the sheet is the source of truth for what has already been logged.

What does this prompt do?

Pulls every new Squarespace order placed since the last run and adds it as a row in your Google Sheet.
Captures the order number, date, customer name and email, line items, subtotal, shipping, tax, total, fulfillment status, and order ID.
Skips orders that are already in the sheet, so retries and overlapping runs never create duplicate rows.
Runs on its own every 30 minutes, so your order log is always close to live without anyone touching it.

What do I need to use this?

A Squarespace store on a plan that supports orders (Commerce Basic, Commerce Advanced, Business, or higher).
A Google account with edit access to the spreadsheet you want to use as your order log.
A spreadsheet with a tab and header row ready to receive orders (or let the workflow create one on first run).

How can I customize it?

Change the schedule. Run it every 15 minutes for busier stores, or once an hour if you don't need near-live updates.
Pick which columns you want. Drop fields you don't care about, or add product SKU, discount codes, or shipping address if your accounting needs them.
Point it at a different sheet or tab per month, per store, or per brand if you sell on multiple Squarespace sites.

FAQs

Will I get duplicate rows if the workflow runs twice or retries?

No. Before adding a new row, the workflow checks the order ID column in your sheet and skips any order that is already logged, so the log stays clean even if a run overlaps a previous one.

Does this work with my Squarespace plan?

You need a plan that includes the Commerce features and the Orders API, which covers Commerce Basic, Commerce Advanced, Business, and the newer Core and Plus tiers. Personal-only sites without Commerce won't return orders.

Can I send orders to more than one sheet, like one per store?

Yes. Duplicate the workflow, change the Squarespace connection and the spreadsheet it points at, and you can run a separate log per store or per brand.

What happens to orders placed before I set this up?

By default, the workflow only logs orders placed after it starts running. If you want history, you can do a one-time backfill by pointing it at an older start date the first time.

Will it capture refunds and cancellations later on?

It records each order's current fulfillment status when it is first synced. If you want refund and cancellation updates over time, you can extend the workflow to re-check recent orders and update existing rows.

Related templates

Call overdue Xero customers with an AI collections agent

Every weekday at 10am, an AI voice agent phones your most overdue Xero accounts, logs what each customer promised, and reports back to finance in Slack.