Auto-Transcribe and Summarize YouTube Videos with Google Apps Script and Supadata

Manually watching every YouTube video and writing summaries simply doesn't scale. The original author aggregates AI, agent, robotics, and humanoid robot videos in four languages on iadots.com — to make that work, they built a serverless automation pipeline: Google Apps Script calls the Supadata API to fetch transcripts, passes them to a large language model for summarization, and writes the results back to a Google Sheet. The entire solution is just over a hundred lines of code, yet it's packed with engineering details worth borrowing for production use.

Overall Architecture and Prerequisites

This automation uses a Google Sheet as a video queue. The sheet has a tab called Videos with three columns: youtubeId (the video ID after v= in the URL), lang (the video's original language, e.g. en or pt), and resumo (the summary column, initially empty). The script runs on a schedule, scanning the sheet and filling in summaries for any rows that don't have one yet.

To get everything running, you need three things: a Google account for Apps Script and Sheets; a Supadata API key (the free tier is enough for testing); and a Gemini API key generated in Google AI Studio. The author notes that Gemini can be swapped out for another model by simply adjusting the summarization function. The project can be created from the sheet under Extensions > Apps Script, or directly at script.google.com.

Key Management: Never Hardcode Secrets

The first engineering habit is keeping secrets out of your code. The author's approach is to create two properties — SUPADATA_API_KEY and GEMINI_API_KEY — under Project Settings > Script Properties in the Apps Script editor, then read them at runtime via PropertiesService.getScriptProperties().

The accompanying chave() helper function validates existence when reading: if a property is missing, it throws an exception immediately rather than letting subsequent requests fail with an empty key. This small "fail fast" design saves a lot of debugging time in unattended scheduled jobs. A global constant LIMITE_BYTES = 180000 is also defined upfront, foreshadowing the text size control described later.

PropertiesService is a key-value storage service provided by Google Apps Script, available at three scopes: Script, User, and Document. Script-level properties are visible to all users running the script and are ideal for storing global config like API keys. The key advantage over hardcoding is that property values never enter your Git history with the code — even if the project is shared or deployed to another account, the keys aren't exposed alongside the source. The "fail fast" principle means letting the program crash early with a clear error when it encounters an invalid state, rather than continuing with bad data until something fails in a hard-to-locate spot. For unattended scheduled tasks, one clear startup failure is far easier to find and fix than a silent bad execution.

Three Key Parameters in the Transcript Function

Fetching transcripts uses the GET /v1/transcript endpoint with authentication via the x-api-key header. The author uses three key parameters: mode=native retrieves only existing captions without triggering AI-generated transcription; text=true returns continuous text rather than timestamped segments; and lang specifies the original language.

There's a subtle documentation gotcha worth noting: if the requested language doesn't exist, Supadata returns the first available language instead. To avoid feeding a Japanese video's translated captions into the summarizer by mistake, the script validates the lang field in the response — if it doesn't match the requested language, the result is discarded (returning idioma_ou_texto_indisponivel). This check might seem redundant, but it's critical for keeping content in four languages from bleeding into each other.

For very long videos, the API may return status code 202 and a jobId, requiring polling GET /v1/transcript/{jobId} until the status becomes completed or failed. The author's aguardarJob function polls up to 12 times with 5-second intervals, balancing async wait time with timeout protection. The 180,000-byte cap is a practical constraint: overly long transcripts slow down summarization, increase cost, and risk blowing past Apps Script's execution time limit.

HTTP 202 in REST API design means "the request has been accepted but processing isn't complete yet" — commonly used for long-running async tasks. The polling pattern (repeatedly querying for task status at fixed or increasing intervals until a terminal state is reached) is the simplest way to retrieve async results. The alternative is a Webhook callback, where the server pushes the result when done, eliminating polling overhead — but it requires the client to expose an externally accessible callback URL. Since Apps Script has no persistent process and can't receive Webhooks, polling is the only viable option. 12 × 5 seconds = 60 seconds of maximum wait, which stays well within Apps Script's ~6-minute per-execution limit while leaving room to process multiple videos.

Summarization: Keep Keys Out of Logs

The summarization step calls Gemini's generateContent method, also via a direct UrlFetchApp request. One detail: the key is placed in the x-goog-api-key request header rather than the URL, specifically to keep it out of logs — a continuation of the "no secrets in code" principle established earlier.

For the payload, the author uses systemInstruction to explicitly tell the model to "faithfully summarize tech video transcripts without fabricating information," and sets temperature to 0.3 in generationConfig to reduce hallucination. The prompt asks for 3 to 5 sentences in the video's original language, so each language version of the site maintains consistent content. The response parser also filters out any parts tagged with thought, keeping only actual text output — a practical touch when integrating with the newer generation of reasoning models.

Idempotency Design: Avoid Double-Charging

This is the part the author cares most about. If a Supadata request times out mid-flight, the script can't tell whether the quota was already deducted. If the next run blindly retries, you could end up paying twice for the same video.

The solution is to write a marker to PropertiesService (TX_DONE_{youtubeId}_{lang}) before making the request. If the marker already exists, the video is skipped. The marker is then updated in a finally block to reflect the final result — resumido on success, or a specific error reason on failure. This means if transcription succeeds but summarization fails, the video is marked erro and stays put — requiring a human to investigate and manually delete the property before it's retried. The author acknowledges this is a deliberate trade-off: "I'd rather manually review one case than unknowingly pay twice." In cost-sensitive API scenarios, this idempotency-for-safety approach is well worth referencing.

Idempotency is a core concept in distributed systems and API design: executing the same operation multiple times produces the same result as executing it once. In network request scenarios, clients often can't distinguish between "the server processed the request but the response was lost" and "the server never received the request" — blind retries can lead to duplicate charges or duplicate data writes. Common engineering approaches include: using unique request IDs to let the server deduplicate, maintaining local state markers on the client (the approach used here), and choosing inherently idempotent HTTP methods (e.g., PUT instead of POST). Writing the marker before the request is a conservative "claim-slot-first, act-second" strategy that sacrifices a small amount of retryability in exchange for guaranteed zero duplicate charges — a reasonable engineering trade-off when calling external paid APIs.

Scheduled Triggers and Concurrency Control

Finally, the main function rodarAutomacao processes only 5 videos per run, driven by a time-based trigger that fires every hour. LockService.getScriptLock() ensures two executions can't run simultaneously and grab the same video. The 5-video limit again comes from Apps Script's per-execution time constraint.

The criarGatilho function deletes any existing trigger of the same name before creating a new one, preventing duplicate registrations. Run criarGatilho manually once and grant permissions — from then on, any new row added to the sheet will automatically get a summary within an hour.

Common Errors and Debugging Tips

The author also summarizes several frequent issues: a 206 status code or transcript-unavailable means the video has no captions in the requested language — this is expected behavior with mode=native. If you need to cover these cases, try mode=auto to trigger AI-generated transcription, but confirm the quota impact first. Language codes should use ISO 639-1 (en, pt, es) — the script already strips suffixes like -BR automatically. PropertiesService has storage quotas, so only store status there; keep the summaries themselves in the sheet or a database.

The whole approach proves a point: no server, no infrastructure costs — just over a hundred lines of Apps Script turning a list of videos into publish-ready multilingual summaries. What actually makes the difference are three engineering choices: validating the returned language, capping text size, and writing the marker before consuming quota.

Note: The original post contains a referral link to Supadata. The author discloses they may receive credit for signups made through it.