Track campaign hashtags across social in Google Sheets

Build a deterministic, code-based workflow that logs every new social post on a configured list of campaign hashtags into Google Sheets. No reasoning step, fixed schema in, rows out. The marketing team analyzes reach in their existing dashboards that read from this sheet.

Trigger: cron, every 4 hours.

Workflow inputs (configured once at setup):

- hashtags: a list of campaign hashtags to track, without the # symbol (e.g. ["summersale25", "ourbrand", "launchday"]).

- spreadsheetId: the destination Google Sheets spreadsheet ID.

- sheetName: the tab name to write into. Default: "Campaign Mentions".

Steps:

1. Load the last-run high-water mark per platform from workflow state. This is a UTC ISO 8601 timestamp for each of twitter, instagram, tiktok. If state is empty (first run), default to 4 hours ago.

2. For each hashtag in the configured list, query all three platforms in parallel using XPOZ:

- Search Twitter Posts by Keywords with query "#<hashtag>".

- Search Instagram Posts by Keywords with query "#<hashtag>".

- Search TikTok Posts by Keywords with query "#<hashtag>".

3. For each platform, filter results to posts whose posted_at is strictly greater than that platform's stored high-water mark. Page through XPOZ results with the cursor until no more results remain newer than the high-water mark.

4. Deduplicate by post URL within the batch. Then read the existing URL column from the destination sheet once per run using Google Sheets Get Values, build a Set, and drop any URL already present so re-runs never insert the same row twice.

5. Map each remaining post into this fixed row schema, in this column order:

- platform ("twitter", "instagram", or "tiktok")

- hashtag (the campaign hashtag that matched, without #)

- author_handle

- post_url

- posted_at (UTC ISO 8601)

- like_count

- comment_count

- share_count (retweets for Twitter, shares for TikTok, 0 for Instagram)

- caption_snippet (first 200 characters of the caption or tweet text, single-line, no newlines)

6. If the destination sheet's first row is empty, write the header row in that exact column order on the first run only.

7. Call Google Sheets Append Values once per run with all new rows batched into a single request to stay under rate limits. Use USER_ENTERED so timestamps and numbers parse as native sheet types.

8. On success, advance each platform's stored high-water mark to the latest posted_at seen for that platform in this batch. Only advance on success.

Resilience: if any one platform errors or rate-limits, continue with the others, log the failure, and do not advance that platform's high-water mark so the next run picks up what was missed. Do not let one platform's failure block the whole run.

Logging: per run, log per-platform counts (posts found, posts deduped against the sheet, posts appended) plus any errors.

Output is purely deterministic. No AI summarization or scoring. The sheet is the single source of truth that downstream dashboards read from.