SheetMagicSheetMagic

Pull 40 YouTube Transcripts in Minutes, in Google Sheets

Paste YouTube links in a column and run =YTTRANSCRIPT(A2:A41) to get every video's title, channel, date and transcript in its own row, ready to summarize or mine for content ideas, at 24 scraping credits per video instead of hours of copy-paste.

Phil Nguyen · 7 min read · Updated

To get YouTube transcripts into Google Sheets, paste your video links in a column and type =YTTRANSCRIPT(A2:A41) in an empty cell. SheetMagic writes one row per video with its title, channel, publish date and transcript into a new results tab, so 40 videos land in your spreadsheet within a few minutes for 24 scraping credits each, instead of an afternoon of copy-paste.

=YTTRANSCRIPT(A2:A41)

Picture a content marketer who wants to know what the top 40 videos in their niche actually say. By hand, that means opening each video, opening the transcript panel, copying the text and pasting it into a sheet. At about 4 minutes a video, 40 videos is roughly 2 hours 40 minutes before any real analysis starts, and the same chore comes back every time a competitor posts.

=YTTRANSCRIPT() is a SheetMagic custom function that extracts subtitles and transcripts from YouTube videos. It is built for the moment you have a list of videos and want the spoken text of each one in a row you can read, search, filter or hand to AI.

What does =YTTRANSCRIPT() return?

Each video becomes one row with these columns:

ColumnWhat it holds
TitleThe video title
Video URLThe watch link for the video
ChannelThe channel name
PublishedThe publish date
TranscriptThe spoken text of the video
Channel IDThe YouTube channel ID
Views, Likes, CommentsThe counts as YouTube shows them
KeywordsThe video tags in one cell
ThumbnailThe thumbnail image link
DescriptionThe video description

Views, Likes and Comments come through as YouTube's own text, such as "12,345 views" or "4.8K", not as plain numbers. If you want to sort by them, add a helper column that converts the text first.

How do I pull transcripts for a whole list of videos?

Put one video link per cell in a single column, then pass that range to the function:

=YTTRANSCRIPT(A2:A41)

The range is flattened into a list of links and empty cells are skipped, so a column with a few gaps still works. You can also pass a single link in quotes or a single cell, such as =YTTRANSCRIPT(A2).

While the job runs, the cell shows "⏳ Scraping...". The rows never land in or below the formula cell. They go to a new tab named "Youtube Transcripts Results", with a Source column first that shows which input each row came from. Keep Formula is on by default, so the formula cell keeps your formula as text; with it off, the cell changes to a short status such as "✅ 40 results → Youtube Transcripts Results".

Use full video links such as https://www.youtube.com/watch?v=dQw4w9WgXcQ. Short youtu.be links may also work.

Channel links and playlist links are not accepted. If you want every video from a channel, collect the individual video links into a column first, then run =YTTRANSCRIPT() on that column.

What are the limits of YouTube transcript extraction?

  • No timestamps. The transcript comes back as plain text, without time codes.
  • 5,000 characters per video. Long transcripts are cut at 5,000 characters so each cell stays a manageable size. The Description column has the same cap.
  • No language choice. The function has an optional language argument, but it is not used. The transcript comes in the video's available caption language: English when the video has English captions, otherwise the first available track.
  • Videos without captions are dropped. A video with no transcript does not get a row, so your output can have fewer rows than your input list.

How do I summarize YouTube transcripts with AI?

Once the transcripts are in the results tab, point =AITEXT(prompt, context) at the Transcript cell. The tab starts with the Source column, so Transcript is column F. Put this in an empty column on row 2 of that tab and fill it down:

=AITEXT("Summarize this video transcript in 3 bullet points", F2)

The cell shows Processing... first. With the default settings (Keep Formula on), the formula cell then keeps your formula as text and the summary lands in the cell to its right, so put the formula in a column with an empty column after it.

The context argument is added to your prompt, so the AI answers about that one transcript. A few prompts that work well on transcripts:

  • "List the main topics covered, one per line"
  • "Write a one-sentence summary for a newsletter"
  • "Pull out any product or tool names mentioned"
  • "Suggest a blog post title based on this transcript"

AI functions use AI tokens, which are a separate allowance from scraping credits. On the Solo plan, for example, the monthly allowance is 3,250,000 AI tokens alongside 10,000 scraping credits. See AI tokens vs scraping credits for how the two pools work, and Generate text with AI for more AITEXT prompts.

What can you do with the transcripts once they are in the sheet?

The rows are only worth the credits if they save you work or win you something. A few uses that do:

  • Turn videos into written content. Point =AITEXT() at each transcript to draft a blog outline or newsletter blurb. If an outline takes you 20 minutes to write from scratch, 10 videos is over 3 hours of drafting you start from a first version instead.
  • Find what competitors cover and you do not. Ask AI to list the topics in each transcript, then filter the column for topics missing from your own channel. That is a content gap list built from 40 videos you never had to watch end to end.
  • Pull every product and tool mention. Run "Pull out any product or tool names mentioned" down the column to see which products a niche keeps recommending, which tells you who to pitch for sponsorships or partnerships.
  • Search instead of rewatching. With every transcript in one column, Ctrl+F or a FILTER on a keyword finds the video and the quote in seconds, so you stop rewatching 15-minute videos to find one line.

How many credits does a YouTube transcript cost?

Each transcript costs 24 scraping credits per result, so 10 videos cost 240 credits and 40 videos cost 960. The Free plan comes with 100 scraping credits, enough for about 4 transcripts. The Solo plan includes 10,000 scraping credits a month, enough for about 416 transcripts.

You are billed per delivered row. A video that has no captions is dropped before it reaches the sheet, so it is never billed.

Where to go next

=YTTRANSCRIPT() is one of SheetMagic's scraping functions. For the full set, read the web scraping guide and web scraping in Google Sheets. To do more with the text once it is in your sheet, see AI in Google Sheets.

Questions about a transcript job? Email support@sheetmagic.ai and we will help.

Text and scraping formulas run on the free allowance. Install from the Google Workspace Marketplace, no card, no expiry.

Install free for Google Sheets

About the author

Phil NguyenPhil builds SheetMagic, the Google Sheets add-on that brings AI and web scraping into the spreadsheet, used by 6,000+ people. He writes these guides from the formulas he runs on his own sheets.