YouTube Transcript in Google Sheets With One Custom Function

Getting a YouTube transcript into Google Sheets takes one Apps Script function. Paste it once, and =YT_TRANSCRIPT(A2) works like any built-in formula: URL in one column, transcript in the next. Here's the complete script, plus two Sheets-specific gotchas that will bite you if nobody warns you.

The script

Open your spreadsheet, go to Extensions > Apps Script, and paste this:

const API_KEY = 'yt_YOUR_KEY';

/**
 * Fetches the transcript of a YouTube video.
 * @param {string} url Any YouTube URL or a bare 11-char video id.
 * @param {number} maxChars Optional length cap (default 5000).
 * @return {string} The transcript text.
 * @customfunction
 */
function YT_TRANSCRIPT(url, maxChars) {
  if (!url) return '';
  const endpoint = 'https://youtube2text.org/api/transcribe'
    + '?url=' + encodeURIComponent(url)
    + '&maxChars=' + (maxChars || 5000);
  const response = UrlFetchApp.fetch(endpoint, {
    headers: { 'x-api-key': API_KEY },
    muteHttpExceptions: true
  });
  const body = JSON.parse(response.getContentText());
  if (body.error) return 'ERROR: ' + body.error.code;
  return body.result.content;
}

Save, and the function is live in your sheet. The @customfunction tag is what registers it with autocomplete; muteHttpExceptions keeps a 404 from exploding into a red cell with a stack trace nobody asked for.

For the key: request https://youtube2text.org/api/demo-key in your browser and it hands back {"success": true, "apiKey": "yt_..."} — a free shared key, no account, limited to 5 videos a month per IP. Fine for trying this out; get your own for real use.

Using it

1. Put video URLs in column A — full links, youtu.be short links, or bare 11-character ids all work.
2. In B2, type =YT_TRANSCRIPT(A2).
3. Need more or less text? =YT_TRANSCRIPT(A2, 20000) sets the cap explicitly.
4. Drag the formula down the column and watch the cells fill in.

Transcripts come from YouTube's own captions — manual when the creator uploaded them, auto-generated otherwise, English preferred with a fallback to whatever language exists.

Why maxChars matters more here than anywhere else

A Google Sheets cell holds at most 50,000 characters. That's a hard platform limit, and the API's default cap of 150,000 characters sails right past it — a long podcast episode would fail to land in the cell. So in Sheets, always pass maxChars of 50000 or less. I default the script to 5000, which covers summaries, keyword checks, and feeding an AI formula, without turning your spreadsheet into a novel.

The recalculation gotcha

Here's the honest limitation of the custom-function approach: Sheets re-runs custom functions whenever it recalculates — including every time the file is reopened. Each re-run is a real API call, so a sheet with 40 transcript formulas quietly re-fetches 40 videos on open and eats your quota for breakfast. The fix is simple: once a transcript arrives, select the cells and paste them back as values (Copy, then Paste special > Values only). The text stays; the formula, and its appetite, goes.

When a cell says ERROR

The function returns readable codes instead of crashing. ERROR: TRANSCRIPT_UNAVAILABLE means the video has no captions at all — nothing to fetch. ERROR: RATE_LIMIT_EXCEEDED means the key's monthly quota ran out, which on the shared demo key happens fast. ERROR: INVALID_URL means the cell doesn't contain anything resembling a YouTube video.

Where to take it

A sheet full of transcripts is a surprisingly good research tool — I walk through that workflow in the content research post. And when you outgrow formulas and want transcripts fetched automatically as videos publish, the n8n workflow does it hands-free.

For your own key — no shared quota, no phone, no card — sign in with Google at youtube2text.org/app/keys. Free covers 5 videos a month; $5.99 gets 50, $9.99 gets 500, $19.99 is unlimited. Paste it into API_KEY and the sheet does the rest.