Weekly YouTube competitor tracker in Google Sheets

Build a code workflow that refreshes my YouTube competitor tracker in Google Sheets every Monday at 6am local time. This is a fully deterministic pipeline: no LLM reasoning, no summarization, just structured data fanned out from my sheet through vidIQ and back into my sheet.

Trigger: cron, every Monday at 6:00 AM.

Step 1. Read competitor channels. Use Google Sheets Get Values to read the Channels tab of my spreadsheet. The tab has a header row and at least two columns: channel_id (a YouTube channel ID like UCxxxx) and channel_name (the display name I use for that competitor). Read the full data range, skip the header row, and treat each remaining row as one channel to process. The spreadsheet ID and the Channels range (for example Channels!A2:B) should be exposed as configurable inputs.

Step 2. For each channel, fan out two vidIQ calls in parallel where possible:

a) vidIQ Channel Stats for that channel_id. Capture subscribers, total uploads, and engagement (engagement rate).

b) vidIQ Outlier Videos for that channel_id, scoped to the last 7 days. Count how many outlier videos were returned, and pick the single top outlier by view count. Capture its title and view count.

If a channel returns an error from either vidIQ call (unknown channel, no data, rate limit), do not abort the whole run. Log the error, leave that channel's numeric fields blank or zero, and continue to the next channel.

Step 3. Build one row per channel with these columns, in this order: run_date (ISO date of the run, e.g. 2026-06-01), channel_name, subscribers, total_uploads, engagement_rate, new_outlier_count, top_outlier_title, top_outlier_views. If there were no outliers in the last 7 days, write 0 for new_outlier_count and leave top_outlier_title and top_outlier_views blank.

Step 4. Append all rows to the Weekly Tracking tab of the same spreadsheet using Google Sheets Append Values with USER_ENTERED value input so the date renders as a real date and the numbers render as numbers. Append in one batched call where possible. Do not clear or overwrite existing rows, only append, so the sheet builds a week-over-week history.

Inputs the workflow should expose: the Google Sheets spreadsheet ID, the Channels read range (default Channels!A2:B), the Weekly Tracking append range (default Weekly Tracking!A:H), and the outlier lookback window (default 7 days).

Keep the workflow strictly deterministic and read/write only. No model calls, no commentary text, no Slack or email side-effects. Just the loop: read channels, hit vidIQ, append rows.