Sync new Squarespace orders into Google Sheets

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.