Weekly YouTube competitor tracker in Google Sheets

PromptCreate

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.

What does this prompt do?

Runs automatically every Monday at 6am so a fresh competitor snapshot is waiting when you start the week.
Reads your list of competitor YouTube channels from a Channels tab in your spreadsheet, so you control who is tracked in one place.
Pulls subscribers, total uploads, and engagement for each channel, plus any breakout videos from the past week.
Appends one tidy row per channel to a Weekly Tracking tab, so you build a clean week-over-week history with no manual exports.

What do I need to use this?

A Google account with edit access to the spreadsheet you want to track competitors in.
A vidIQ account with API access for YouTube channel stats and breakout video data.
A short list of competitor YouTube channels you want to follow each week.

How can I customize it?

Change the schedule to run on a different day or time, or switch to a daily or biweekly cadence.
Edit the Channels tab in your spreadsheet to add or remove competitors. No code changes needed.
Adjust which columns get written into the Weekly Tracking tab, for example add average view count or drop the engagement column.

FAQs

Do I need to maintain a list of channels somewhere special?

No. Your competitor list lives in a Channels tab inside the same Google Sheet. Add or remove rows there and the next run picks up the changes automatically.

Will this overwrite my existing tracking history?

No. Each run appends new rows to the Weekly Tracking tab with that run's date, so you build a clean week-over-week history that you can chart or pivot on.

What counts as a breakout video?

vidIQ flags videos that significantly outperformed the channel's recent average views, so you only see what is actually trending up rather than every new upload.

Can I track more than 10 competitors?

Yes. The workflow loops over every channel listed in your Channels tab, so you can track as many as you want. Heavier lists take longer to run, which is fine on a weekly schedule.

Does it require any manual download or CSV step?

No. Everything moves directly from vidIQ into your spreadsheet, so there are no exports, downloads, or copy-paste steps each Monday.

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.