Skip to content
videodataapi.com
Google Apps ScriptSetup time: ~4 minutes · Zero proxy config

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.

GET /v1/youtube/video →10 Credits / call

$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.

Copy or Download Template

Ready-to-Import Code & Configuration

Need an API key? Claim 3,000 Free Credits ($0.30) →
Code.gs (Google Apps Script)
/**
 * 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

  1. 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.

  2. 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.

  3. 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.

  4. 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

  1. 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.
  2. 02Click Extensions -> Apps Script, replace Code.gs with the script above, press Save, and reload your Google Sheet.
  3. 03Click VideoData API -> Set API Key (PropertiesService) in the top menu bar and paste your yt_live_... key.
  4. 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 Integrations