Harvest every video you've ever posted on TikTok into a clickable, filterable Excel spreadsheet with full engagement metrics — plays, likes, comments, shares, saves, post date, music, and a clickable link to each video.
A Claude Code recipe that uses the Playwright MCP plugin to log into TikTok, walk every video on your profile, and enrich each one with public engagement metrics. The output is an Excel workbook with a clickable HYPERLINK on every caption, autofilter on every column, count formatting on the numbers, and a TOTALS row at the bottom that sums plays / likes / comments / shares / saves.
Nothing private. Every metric in the spreadsheet is the same number anyone can see on your video pages. The login step exists only so TikTok serves real pages instead of bot-protection placeholders.
| Column | What It Holds |
|---|---|
| A — Row # | 1-indexed, sorted newest-first |
| B — Date | Post date in YYYY-MM-DD format |
| C — Caption | Truncated to ~80 chars, as a clickable HYPERLINK to the video |
| D — Plays | Total play count |
| E — Likes | Heart count |
| F — Comments | Comment count |
| G — Shares | Share count |
| H — Saves | Save / collect count |
| I — Duration | Seconds |
| J — Music title | Track name |
| K — Music author | Track creator |
| L — Full video URL | Plain text URL (not a hyperlink) |
Plus a TOTALS row two rows below the last video, with SUM formulas on plays / likes / comments / shares / saves.
uniqueId matches and grabs the expected video count.[data-e2e="user-post-item"] count is stable for 4 rounds (cap at 40 iterations).videos_raw.csv.ThreadPoolExecutor(max_workers=4) and a 5-second pause every 100 fetches. Each video page's embedded JSON yields caption, post time, duration, music, plays, likes, comments, shares, saves.openpyxl — sorted newest-first, frozen header row, autofilter on, number formatting, TOTALS row. Saves as MY_VIDEOS.xlsx and opens.openpyxl (pip install openpyxl)MY_VIDEOS.xlsx to land.logged in.Filter by date. Click the dropdown on column B to narrow to a specific month or year.
Find your evergreen content. Add a column M with =H2/E2 (saves ÷ plays). Videos where that ratio is high are the ones people kept for later — usually your most useful or evergreen pieces.
Saves vs Shares are different signals. Sort by Saves to find your most "save-worthy" content. Sort by Shares to find your most "share-worthy." They're rarely the same videos.
Track growth over time. Re-run the prompt periodically and save dated copies of the xlsx. Diff them in a separate sheet to see which videos are still picking up plays months after posting.
One README, one prompt block inside it. Copy either panel below — the left is the full README for reference, the right is just the prompt you paste into Claude Code.
# TikTok Videos → Spreadsheet
Harvest every video you've ever posted on TikTok into a clickable, filterable Excel spreadsheet with full engagement metrics — plays, likes, comments, shares, saves, post date, music, and a clickable link to each video.
**Time:** Under 2 minutes for a profile with a few hundred videos.
**Output columns:** Row # · Date · Caption (clickable link) · Plays · Likes · Comments · Shares · Saves · Duration · Music title · Music author · Full video URL. With a TOTALS row at the bottom and autofilter enabled.
## Prerequisites
- Claude Code with the **Playwright MCP** plugin enabled
- Python 3 with `openpyxl` installed (`pip install openpyxl`)
- Your TikTok account (the scraper runs on your own profile — no API key, no authentication trickery)
## How to run
Paste the prompt below into a fresh Claude Code session. When Claude opens a browser to TikTok, log in. Then say "logged in" in chat and let it work.
---
## The prompt
```
Harvest every video on my TikTok profile into an Excel spreadsheet with full engagement metrics. Steps:
1. Open https://www.tiktok.com/login via Playwright MCP. Wait until I say "logged in" before continuing. (If Playwright needs the browser installed: npx @playwright/mcp install-browser chrome-for-testing)
2. After I confirm, evaluate this in the page to detect my username from the current URL:
location.href.match(/@([\w.\-_]+)/)[1]
If that returns null, ask me for my TikTok handle.
3. Navigate to https://www.tiktok.com/@<my_username>. Confirm the page loaded my profile by checking that the embedded JSON contains my uniqueId:
JSON.parse(document.querySelector('#__UNIVERSAL_DATA_FOR_REHYDRATION__').textContent)['__DEFAULT_SCOPE__']['webapp.user-detail'].userInfo.user.uniqueId
Also read videoCount from the same path's stats — that's how many videos to expect.
4. Scroll the page to load every video grid item. In a loop: window.scrollTo(0, document.body.scrollHeight), wait 900ms, count [data-e2e="user-post-item"]. Stop when the count is stable for 4 consecutive iterations. Cap at 40 iterations as a safety net.
5. Extract video URLs from the DOM. For each [data-e2e="user-post-item"]: the inner `a[href*="/video/"]` gives the video URL, image alt gives a preview caption, and [data-e2e="video-views"] gives the grid view count. Save to videos_raw.csv with columns: idx, video_id, video_url, grid_views, grid_caption.
6. Write a Python script (videos_raw.csv → videos_enriched.csv) that for each video URL fetches the page with a real browser User-Agent header (e.g. "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0 Safari/537.36"). urllib's default UA gets 403'd by TikTok. For each response:
- Regex-extract <script id="__UNIVERSAL_DATA_FOR_REHYDRATION__">…</script>
- JSON.parse it
- Read data["__DEFAULT_SCOPE__"]["webapp.video-detail"]["itemInfo"]["itemStruct"] (fallback to "webapp.user-post" if missing)
- From that itemStruct, pull: desc (caption), createTime (unix timestamp), video.duration, music.title, music.authorName, and stats: playCount, diggCount (likes), commentCount, shareCount, collectCount (saves)
- Convert createTime to a YYYY-MM-DD date string
Use ThreadPoolExecutor(max_workers=4) and time.sleep(5) every 100 completions — this is the throttling pattern that stays under TikTok's edge rate limit. If you hit >10% errors, stop immediately — you're being rate-limited and should not retry within the same run.
7. Build the spreadsheet with openpyxl. Sort by date descending (newest first). Columns:
A: Row # (1-indexed)
B: Date posted (YYYY-MM-DD)
C: Caption as clickable hyperlink → `=HYPERLINK("<video_url>","<short_caption>")` (truncate caption to ~80 chars + ellipsis)
D: Plays
E: Likes
F: Comments
G: Shares
H: Saves
I: Duration (seconds)
J: Music title
K: Music author
L: Full video URL
Freeze the header row, enable autofilter on the full range, format count columns with #,##0. Add a TOTALS row two rows below the last video with SUM formulas on plays/likes/comments/shares/saves.
Save as MY_VIDEOS.xlsx and open it.
NOTES
- TikTok randomizes CSS class names. Always use partial matchers like [class*="..."] or data-e2e attributes — never the css-xxx-xxx-DivWhatever hashed class names, which will have changed by the time the next person runs this.
- TikTok's profile shows a slightly higher videoCount in the user-detail JSON than the actual grid (~2-3 video gap is normal — TikTok hides a few from public listings).
- A small number of older videos may not have all stats fields populated. Don't crash on missing fields — write empty cells.
- Per-video new-follower counts are NOT available in any public TikTok data. Those are only in the Creator Analytics dashboard (Pro accounts) and require manual export. Five engagement metrics (plays, likes, comments, shares, saves) is everything the public profile exposes.
```
---
## Tips
- **Filter by date:** Click the dropdown on column B to filter to a specific month or year.
- **Find your saves-to-likes ratio:** Add a column M with `=H2/E2`. Videos where this is high signal "people kept it for later" — usually your most useful or evergreen content.
- **Find top growers over time:** Re-run this prompt periodically and save the dated xlsx files. Diff them in a separate spreadsheet to see which videos are still picking up plays months later.
- **Filtering by engagement type:** Sort by Saves column to find your most "save-worthy" content. Sort by Shares to find your most "share-worthy." They're usually different videos.
## What this scraper does NOT capture
- **Per-video new-followers gained** — only available in TikTok's Creator Analytics dashboard
- **Watch time / completion rate** — same restriction
- **Audience demographics** — same restriction
- **Comments themselves** (only the count) — can be added if needed, but comments are loaded via separate API call
## Privacy note
This tool only reads what's already public on your TikTok profile. Every metric in the spreadsheet is visible to anyone who visits your video pages. The login step is just to ensure TikTok treats you as a real user rather than serving bot-protection placeholder pages.
Harvest every video on my TikTok profile into an Excel spreadsheet with full engagement metrics. Steps:
1. Open https://www.tiktok.com/login via Playwright MCP. Wait until I say "logged in" before continuing. (If Playwright needs the browser installed: npx @playwright/mcp install-browser chrome-for-testing)
2. After I confirm, evaluate this in the page to detect my username from the current URL:
location.href.match(/@([\w.\-_]+)/)[1]
If that returns null, ask me for my TikTok handle.
3. Navigate to https://www.tiktok.com/@<my_username>. Confirm the page loaded my profile by checking that the embedded JSON contains my uniqueId:
JSON.parse(document.querySelector('#__UNIVERSAL_DATA_FOR_REHYDRATION__').textContent)['__DEFAULT_SCOPE__']['webapp.user-detail'].userInfo.user.uniqueId
Also read videoCount from the same path's stats — that's how many videos to expect.
4. Scroll the page to load every video grid item. In a loop: window.scrollTo(0, document.body.scrollHeight), wait 900ms, count [data-e2e="user-post-item"]. Stop when the count is stable for 4 consecutive iterations. Cap at 40 iterations as a safety net.
5. Extract video URLs from the DOM. For each [data-e2e="user-post-item"]: the inner `a[href*="/video/"]` gives the video URL, image alt gives a preview caption, and [data-e2e="video-views"] gives the grid view count. Save to videos_raw.csv with columns: idx, video_id, video_url, grid_views, grid_caption.
6. Write a Python script (videos_raw.csv → videos_enriched.csv) that for each video URL fetches the page with a real browser User-Agent header (e.g. "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0 Safari/537.36"). urllib's default UA gets 403'd by TikTok. For each response:
- Regex-extract <script id="__UNIVERSAL_DATA_FOR_REHYDRATION__">…</script>
- JSON.parse it
- Read data["__DEFAULT_SCOPE__"]["webapp.video-detail"]["itemInfo"]["itemStruct"] (fallback to "webapp.user-post" if missing)
- From that itemStruct, pull: desc (caption), createTime (unix timestamp), video.duration, music.title, music.authorName, and stats: playCount, diggCount (likes), commentCount, shareCount, collectCount (saves)
- Convert createTime to a YYYY-MM-DD date string
Use ThreadPoolExecutor(max_workers=4) and time.sleep(5) every 100 completions — this is the throttling pattern that stays under TikTok's edge rate limit. If you hit >10% errors, stop immediately — you're being rate-limited and should not retry within the same run.
7. Build the spreadsheet with openpyxl. Sort by date descending (newest first). Columns:
A: Row # (1-indexed)
B: Date posted (YYYY-MM-DD)
C: Caption as clickable hyperlink → `=HYPERLINK("<video_url>","<short_caption>")` (truncate caption to ~80 chars + ellipsis)
D: Plays
E: Likes
F: Comments
G: Shares
H: Saves
I: Duration (seconds)
J: Music title
K: Music author
L: Full video URL
Freeze the header row, enable autofilter on the full range, format count columns with #,##0. Add a TOTALS row two rows below the last video with SUM formulas on plays/likes/comments/shares/saves.
Save as MY_VIDEOS.xlsx and open it.
NOTES
- TikTok randomizes CSS class names. Always use partial matchers like [class*="..."] or data-e2e attributes — never the css-xxx-xxx-DivWhatever hashed class names, which will have changed by the time the next person runs this.
- TikTok's profile shows a slightly higher videoCount in the user-detail JSON than the actual grid (~2-3 video gap is normal — TikTok hides a few from public listings).
- A small number of older videos may not have all stats fields populated. Don't crash on missing fields — write empty cells.
- Per-video new-follower counts are NOT available in any public TikTok data. Those are only in the Creator Analytics dashboard (Pro accounts) and require manual export. Five engagement metrics (plays, likes, comments, shares, saves) is everything the public profile exposes.