Extract YouTube Transcripts & Stats in Google Sheets
Enrich hundreds of YouTube URLs with titles, channel handles, view counts, likes, and full plain-text transcripts directly inside Google Sheets — driven by an explicit custom menu button so sheet reloads never re-trigger API charges.
$1.00 / 1K PAYG (~$0.60 / 1K on Pro · ~$0.40 / 1K on Business)
$1.00 / 1K PAYG (~$0.60 / 1K on Pro · ~$0.40 / 1K on Business)
⚠ Credit Safety Guardrail: Never Use Bare Cell Formulas (=IMPORTDATA / Custom Functions)
Google Sheets automatically recalculates custom cell formulas every time a workbook is opened, sorted, or edited — silently re-billing thousands of API calls. This integration intentionally uses a menu-triggered Apps Script ('VideoData API -> Enrich Selected Rows (Skip Filled)') backed by PropertiesService key storage and a 6-hour CacheService layer.
Ready-to-Import Code & Configuration
/**
* VideoData API — Google Sheets Batch YouTube Transcript & Stats Extractor
* Zero-Formula Architecture: Never uses bare cell formulas that re-fire on sheet reload.
*
* Sheet Column Layout (Row 1 = Headers):
* Col A: YouTube Video URL or 11-char Video ID (Input)
* Col B: Status ("DONE" rows are automatically skipped)
* Col C: Video Title
* Col D: Channel Handle / Name
* Col E: Views
* Col F: Likes
* Col G: Duration (sec)
* Col H: Language Code
* Col I: Full Plain-Text Transcript
*/
const VIDEODATA_API_BASE = "https://api.videodataapi.com/v1/youtube";
const CACHE_TTL_SECONDS = 21600; // 6 hours CacheService protection
function onOpen() {
SpreadsheetApp.getUi()
.createMenu("VideoData API")
.addItem("Enrich Selected Rows (Skip Filled)", "enrichSelectedRowsSkipFilled")
.addSeparator()
.addItem("Set API Key (PropertiesService)", "promptForApiKey")
.addItem("Clear 6h Local Cache", "clearLocalScriptCache")
.addToUi();
}
function promptForApiKey() {
const ui = SpreadsheetApp.getUi();
const res = ui.prompt(
"VideoData API Key",
"Paste your yt_live_... API key from https://videodataapi.com/dashboard (stored privately in PropertiesService):",
ui.ButtonSet.OK_CANCEL
);
if (res.getSelectedButton() === ui.Button.OK) {
const key = res.getResponseText().trim();
if (!key) {
ui.alert("API key cannot be empty.");
return;
}
PropertiesService.getScriptProperties().setProperty("VIDEODATAAPI_KEY", key);
ui.alert("Saved! Select rows in Column A and click: VideoData API -> Enrich Selected Rows (Skip Filled).");
}
}
function extractVideoId_(raw) {
const text = String(raw || "").trim();
if (/^[A-Za-z0-9_-]{11}$/.test(text)) return text;
const m = text.match(/(?:v=|youtu\.be\/|shorts\/|embed\/)([A-Za-z0-9_-]{11})/);
return m ? m[1] : "";
}
function cachedGetJson_(pathWithQuery, apiKey) {
const cache = CacheService.getScriptCache();
const cacheKey = "vd_" + Utilities.base64EncodeWebSafe(pathWithQuery).slice(0, 200);
const hit = cache.get(cacheKey);
if (hit) {
return JSON.parse(hit);
}
const resp = UrlFetchApp.fetch(VIDEODATA_API_BASE + pathWithQuery, {
method: "get",
muteHttpExceptions: true,
headers: {
"x-api-key": apiKey,
"Accept": "application/json"
}
});
const status = resp.getResponseCode();
const payload = JSON.parse(resp.getContentText() || "{}");
if (status !== 200) {
throw new Error("HTTP " + status + ": " + (payload.message || payload.error || "Upstream error"));
}
const serialized = JSON.stringify(payload);
// CacheService per-key limit is 100 KB; cache compact payloads for 6 hours
if (serialized.length < 95000) {
cache.put(cacheKey, serialized, CACHE_TTL_SECONDS);
}
return payload;
}
function enrichSelectedRowsSkipFilled() {
const ui = SpreadsheetApp.getUi();
const apiKey = PropertiesService.getScriptProperties().getProperty("VIDEODATAAPI_KEY");
if (!apiKey) {
ui.alert("Missing API key. Run 'VideoData API -> Set API Key (PropertiesService)' first.");
return;
}
const sheet = SpreadsheetApp.getActiveSheet();
const range = sheet.getActiveRange();
if (!range) {
ui.alert("Select one or more rows containing YouTube URLs in Column A.");
return;
}
const startRow = Math.max(2, range.getRow()); // Never overwrite Row 1 headers
const numRows = range.getNumRows();
const rowValues = sheet.getRange(startRow, 1, numRows, 9).getValues();
let enrichedCount = 0;
let skippedCount = 0;
for (let i = 0; i < rowValues.length; i++) {
const inputCell = rowValues[i][0];
const currentStatus = String(rowValues[i][1] || "").trim();
const existingTranscript = String(rowValues[i][8] || "").trim();
if (!inputCell) continue;
if (currentStatus === "DONE" || existingTranscript.length > 0) {
skippedCount++;
continue;
}
const videoId = extractVideoId_(inputCell);
if (!videoId) {
rowValues[i][1] = "INVALID_URL";
continue;
}
try {
const videoRes = cachedGetJson_("/video?video_id=" + encodeURIComponent(videoId), apiKey);
const txRes = cachedGetJson_("/transcript?video_id=" + encodeURIComponent(videoId) + "&lang=en", apiKey);
const v = videoRes.data || {};
const t = txRes.data || {};
const fullText = String(t.full_text || "").slice(0, 49000); // Fit Google Sheets 50k cell limit
rowValues[i][1] = "DONE";
rowValues[i][2] = v.title || "";
rowValues[i][3] = v.channel_handle || v.channel_title || "";
rowValues[i][4] = v.view_count ?? "";
rowValues[i][5] = v.like_count ?? "";
rowValues[i][6] = v.duration_seconds ?? "";
rowValues[i][7] = t.language || "en";
rowValues[i][8] = fullText;
enrichedCount++;
} catch (err) {
rowValues[i][1] = "ERROR: " + (err && err.message ? err.message : String(err));
}
}
sheet.getRange(startRow, 1, numRows, 9).setValues(rowValues);
SpreadsheetApp.flush();
ui.alert("Batch complete: " + enrichedCount + " enriched, " + skippedCount + " already-filled rows skipped.");
}
function clearLocalScriptCache() {
SpreadsheetApp.getUi().alert("Cache entries expire automatically after 6 hours (21,600s).");
}How This Pipeline Works
- 01
Secure Key Storage via PropertiesService
Stores your yt_live_... API key inside Google's server-side ScriptProperties instead of exposing secrets inside spreadsheet cells.
- 02
Menu Action: VideoData API -> Enrich Selected Rows (Skip Filled)
Processes only the highlighted rows in Column A and automatically skips any row where Column B is DONE or Column I already has a transcript.
- 03
6-Hour CacheService Deduplication
Caches JSON responses in CacheService.getScriptCache() for 21,600 seconds (6 hours) so duplicate URLs in the same sheet cost 0 extra API calls.
- 04
Single Batch Write to Columns B–I
Writes Status, Title, Channel Handle, Views, Likes, Duration, Language, and Full Plain-Text Transcript (clamped safely to Google Sheets' 50,000-character cell limit) in one atomic setValues() call.
Quick Setup Checklist
- 01Open any Google Sheet, add headers in Row 1 (URL, Status, Title, Channel, Views, Likes, Duration, Lang, Transcript), and paste YouTube URLs in Column A.
- 02Click Extensions -> Apps Script, replace Code.gs with the script above, press Save, and reload your Google Sheet.
- 03Click VideoData API -> Set API Key (PropertiesService) in the top menu bar and paste your yt_live_... key.
- 04Highlight your URL rows in Column A and click VideoData API -> Enrich Selected Rows (Skip Filled).
Transparent Credit Pricing
- • Transcripts / Video Metadata / Channels:
10 Credits= $1.00 / 1,000 PAYG (~$0.60/1K Pro · ~$0.40/1K Business) - • Search / Channel Videos / Shorts / Comments:
1 Credit / item(min 10) = $0.10 / 1,000 PAYG ($0.04–$0.06/1K Pro & Business) - • Zero-Risk Guarantee: 0 Credits charged on failed calls or empty results. Full rate card →
Frequently Asked Questions
Why shouldn't I use =IMPORTDATA() or a custom =YOUTUBE_TRANSCRIPT(A2) cell formula?
Google Sheets re-evaluates custom cell formulas periodically on workbook reload, structural edits, and tab duplication. On a 500-row sheet, that would re-fetch 500 transcripts repeatedly without user intent. Running enrichment exclusively from 'VideoData API -> Enrich Selected Rows (Skip Filled)' guarantees each row is billed once.
How much does enriching 500 YouTube URLs with both stats and full transcripts cost?
Fetching both /v1/youtube/video (10 Credits) and /v1/youtube/transcript (10 Credits) costs 20 Credits ($0.002) per row — so 500 rows cost just 10,000 Credits ($1.00 PAYG; your first 150 rows are covered free on sign-up via 3,000 Free Credits / $0.30!). Need transcript only? Comment out the /video line to cut cost in half ($1.00 per 1,000 rows, or ~$0.40–$0.60/1K on Pro & Business).
What happens if a YouTube transcript is longer than 50,000 characters?
Google Sheets enforces a hard 50,000-character limit per cell. The script slices data.full_text at 49,000 characters (~1.5 hours of spoken audio) so batch writes never throw cell-overflow exceptions.
More Workflow Integrations
← View All Integrationsn8n YouTube Transcript & Summary Workflow (Free JSON)
Import this 5-node n8n JSON workflow in 30 seconds: receive any YouTube watch or Shorts URL via Webhook or RSS, fetch clean plain-text + timed transcripts from api.videodataapi.com, summarize with an LLM, and push structured notes to Slack or Notion.
Python @tool + MCPLangChain YouTube Transcript & Search Tool (Python/MCP)
Equip LangChain, LangGraph, and ReAct agents with typed Python @tool functions for real-time YouTube search and verbatim transcript extraction — or attach all 8 endpoints in 5 lines via our Model Context Protocol server.