{
  "$type": "site.standard.document",
  "bskyPostRef": {
    "cid": "bafyreig5wp2zg7kkccoaq63fdvgz74v3qtwgjgrpxavtaa5r6ciwzh7mzq",
    "uri": "at://did:plc:h5ir2dlz27wbjmzflpi5offd/app.bsky.feed.post/3mmsyowt7hmh2"
  },
  "coverImage": {
    "$type": "blob",
    "ref": {
      "$link": "bafkreie5pewhjvsxzkzaro5xgxcljuwikkoqxmyxyqnwup4tjinhzqtlbq"
    },
    "mimeType": "image/webp",
    "size": 12890
  },
  "description": "Google Apps Script + the YouTube Data API + a Google Sheet = a fully automated personal watch queue that runs itself. Here's how it works.",
  "path": "/automating-my-youtube-watch-queue-with-google-apps-script/",
  "publishedAt": "2026-05-27T07:35:43.000Z",
  "site": "https://jasontucker.blog",
  "tags": [
    "Automating YouTube Playlists generation with YouTube API",
    "Automating Our Sunday Night YouTube Routine with Youtarr (and Why I'm Probably Over-Engineering This)",
    "sheets.google.com",
    "youtube.com",
    "commentpicker.com/youtube-channel-id.php",
    "script.google.com",
    "GitHub Gist",
    "update-youtube-weekly-playlist.jsGitHub Gist: instantly share code, notes, and snippets.Gist262588213843476"
  ],
  "textContent": "I watch a lot of YouTube — but I hate the YouTube algorithm. I don't want recommendations, I don't want trending videos, and I definitely don't want my watch history influencing what gets surfaced next. What I _do_ want is a clean, automated queue of recent videos from channels I've specifically chosen to follow, with watched items automatically cleaned out.\n\nSo I vibe coded it myself with Google Apps Script and early\n\nThis post walks through exactly what the script does, how it's set up, and what happens each time it runs.\n\n* * *\n\n## I've been scratching this itch for a while now\n\nBack in 2024 I posted about this solution I came up with using Python and wrote about it (as I do) on this blog. It was titled Automating YouTube Playlists generation with YouTube API. I recently posted about how do to this sort of thing in Plex using a Docker Container. Yesterday I wrote about Automating Our Sunday Night YouTube Routine with Youtarr (and Why I'm Probably Over-Engineering This) using Youtarr. I thought I'd circle back on my most recent solution, which I've been using for a while now using Google Apps Script.\n\n## What It Does (The Short Version)\n\nThe script monitors a list of YouTube channels you define, grabs any videos posted in the last 8 days that are longer than 2 minutes, and adds them to a private YouTube playlist — but only if you haven't already watched them. It also keeps a Google Spreadsheet log of every video it's added, and a second scheduled run uses that log to clean watched videos back out of the playlist automatically.\n\nThe result: a self-maintaining queue that acts like a personal RSS-to-playlist feed.\n\n* * *\n\n## The Architecture\n\nThere are three moving parts:\n\n  1. **A Google Apps Script** — the brains. Two scheduled functions handle population and cleanup.\n  2. **A YouTube Playlist** — your personal watch queue, managed entirely by the script.\n  3. **A Google Spreadsheet** — acts as a persistent log (and \"watched\" list) across runs.\n\n\n\nThe script uses Google's built-in YouTube Advanced Service (no external API keys needed beyond enabling it in your Apps Script project) and SpreadsheetApp for the log.\n\n* * *\n\n## Configuration\n\nAt the top of the script, there are five variables you'll need to set for your own use:\n\n\n    var CACHE_DURATION_DAYS = 7;\n    var VIDEO_DURATION_THRESHOLD = 120; // 2 minutes, in seconds\n\n    var PLAYLIST_ID = 'YOUR_PLAYLIST_ID';\n    var SPREADSHEET_ID = 'YOUR_SPREADSHEET_ID';\n    var CHANNEL_IDS = [\n      'CHANNEL_ID_1',\n      'CHANNEL_ID_2',\n      // ...\n    ];\n\n\n  * VIDEO_DURATION_THRESHOLD — filters out Shorts and other clips below this length. 120 seconds (2 minutes) is a sensible floor, but raise it if you only want longer-form content.\n  * PLAYLIST_ID — the ID from your YouTube playlist URL. Create a private playlist in your YouTube account and grab the ID from the URL: `youtube.com/playlist?list=YOUR_ID_HERE`.\n  * SPREADSHEET_ID — the ID from your Google Sheets URL. Create a blank spreadsheet; the script will set up the tab and headers on first run.\n  * CHANNEL_IDS — an array of YouTube channel IDs for every channel you want to track. You can find a channel's ID in its \"About\" page URL or via a YouTube channel ID lookup tool.\n\n\n\n* * *\n\n## Run 1: `updatePlaylist()` — Populating the Queue\n\nThis is the main function and should run on a schedule (I have mine set to daily). Here's what it does step by step.\n\n### Step 1 — Initialize the Spreadsheet\n\n\n    initializeSheet(SPREADSHEET_ID);\n\n\nOn first run (or if someone deletes the sheet), this creates a tab called \"Watched Videos\" with four column headers: Video ID, Channel Name, Video Title, and Publication Date/Time. On subsequent runs it's essentially a no-op.\n\n### Step 2 — Fetch Recent Videos from Each Channel\n\n\n    var eightDaysAgo = new Date();\n    eightDaysAgo.setDate(eightDaysAgo.getDate() - 8);\n\n    for (var i = 0; i < CHANNEL_IDS.length; i++) {\n      var videos = getLatestVideos(CHANNEL_IDS[i], eightDaysAgo);\n      allVideos = allVideos.concat(videos);\n    }\n\n\nFor each channel, the script calls `YouTube.Search.list` with a `publishedAfter` filter set to 8 days ago, fetching up to 10 recent videos per channel. The 8-day window gives a little buffer around a weekly schedule.\n\nThe search call returns `id` and `snippet` data — enough for the video ID, channel name, title, and publish date — but _not_ duration. That requires a separate call.\n\n### Step 3 — Get Video Durations in Batches\n\n\n    var videoDetails = getVideoDetailsInBatches(allVideoIds);\n\n\nYouTube's Search API doesn't return duration. The script collects all video IDs from the search results and calls `YouTube.Videos.list` with `contentDetails` to get the ISO 8601 duration string (e.g., `PT14M32S`).\n\nThis is done in batches of 50 (the API maximum) and uses a `parseDuration()` helper to convert `PT14M32S` → `872` seconds.\n\nResults are cached for 6 hours using `CacheService.getScriptCache()` to avoid redundant API calls on reruns.\n\n### Step 4 — Filter and Add\n\nFor each video, the script runs three checks before adding it to the playlist:\n\n\n    if (fullVideoData.durationInSeconds > VIDEO_DURATION_THRESHOLD &&\n        !watchedVideos.includes(videoId) &&\n        !videoInPlaylist(videoId, PLAYLIST_ID)) {\n\n      addToPlaylist(videoId, PLAYLIST_ID);\n      addToWatchedList(fullVideoData, SPREADSHEET_ID);\n    }\n\n\n  1. **Duration check** — is it longer than the threshold? Filters out Shorts.\n  2. **Watched check** — is the video ID already in the Spreadsheet log? Prevents re-adding something you've seen.\n  3. **Playlist membership check** — is it already in the queue? Prevents duplicates on reruns.\n\n\n\nIf all three pass, the video gets added to the playlist and logged to the spreadsheet with its channel name, title, and publish date.\n\n* * *\n\n## Run 2: runPlaylistCleanup() — Clearing Watched Videos\n\nThe second scheduled function is runPlaylistCleanup(), which calls cleanPlaylist(). I have this one set to run less frequently — a few times a week is plenty.\n\n### How It Works\n\n\n    var watchedVideoIds = getWatchedVideos(spreadsheetId);\n    var playlistItems = getAllPlaylistItems(playlistId);\n\n    playlistItems.forEach(function(item) {\n      var videoId = item.snippet.resourceId.videoId;\n      if (watchedVideoIds.includes(videoId)) {\n        YouTube.PlaylistItems.remove(item.id);\n      }\n    });\n\n\nIt loads the full list of video IDs from the spreadsheet (Column A, everything from row 2 down), then fetches all items currently in the playlist using getAllPlaylistItems(), which handles pagination automatically to get past the 50-item API limit.\n\nFor every playlist item whose video ID appears in the spreadsheet log, it issues a PlaylistItems.remove() call. This is a 50-quota-unit operation, so the function is intentionally conservative — it only removes what it knows has been tracked.\n\n> **A note on \"watched\" semantics:** The spreadsheet log records videos _added_ to the playlist, not videos you've actually watched in YouTube. The assumption is that if a video has been in your queue long enough for the cleanup to run, you've either watched it or decided to skip it. If you want true watch-state tracking, you'd need the YouTube Data API's playbackPosition or history features, which have their own quota and OAuth complications.\n\n* * *\n\n## Reliability: Exponential Backoff\n\nAll YouTube API calls are wrapped in an `exponentialBackoff()` helper:\n\n\n    function exponentialBackoff(func, maxRetries = 5) {\n      for (let i = 0; i < maxRetries; i++) {\n        try {\n          return func();\n        } catch (e) {\n          if (i === maxRetries - 1) throw e;\n          const waitTime = Math.pow(2, i) * 1000 + Math.random() * 1000;\n          Utilities.sleep(waitTime);\n        }\n      }\n    }\n\n\nIf a call fails (rate limit, transient error, whatever), it waits 1s, then 2s, then 4s, then 8s, then 16s before giving up. The random jitter on top prevents thundering herd issues if multiple runs somehow overlap.\n\n* * *\n\n## Setting It Up\n\nThere are four things to create before the script can run: a Google Spreadsheet, a YouTube playlist, a Google Cloud project (for the YouTube API), and the Apps Script itself. Here's the full walkthrough. Link to the full script is at the bottom of this post.\n\n* * *\n\n### Step 1 — Create the Google Spreadsheet\n\nThe script uses a Google Sheet to log every video it adds. It will create the tab and headers automatically on first run, so you just need a blank spreadsheet to point it at.\n\n  1. Go to sheets.google.com and click **Blank spreadsheet**.\n  2. Give it a name you'll recognize — something like \"YouTube Watch Queue Log\".\n  3. Paste that ID into the SPREADSHEET_ID variable at the top of the script.\n\n\n\nCopy the **Spreadsheet ID** from the URL. It's the long string between /d/ and /edit in the address bar:\n\n\n    https://docs.google.com/spreadsheets/d/THIS_IS_YOUR_SPREADSHEET_ID/edit\n\n\nThat's it — don't create any tabs or add any headers yourself. The script's `initializeSheet()` function will build the \"Watched Videos\" tab with the correct columns on the first run.\n\nThe sheet will end up looking like this once videos start flowing in:\n\nA — Video ID | B — Channel Name | C — Video Title | D — Publication Date/Time\n---|---|---|---\n`dQw4w9WgXcQ` | Some Channel | Video Title Here | 2025-05-20T14:00:00Z\n\n> **Tip:** Don't manually delete rows from this sheet. The Video ID in Column A is how the cleanup function knows what to remove from the playlist. If you delete a row, that video could get re-added on the next run.\n\n* * *\n\n### Step 2 — Create a YouTube Playlist\n\n  1. Go to youtube.com and sign in.\n  2. Click your profile icon → **Your channel** → **Playlists** → **New playlist**.\n  3. Give it a name (e.g., \"Auto Queue\") and set the visibility to **Private** (unless you want others to see it).\n  4. Paste that into the `PLAYLIST_ID` variable in the script.\n\n\n\nAfter creating it, open the playlist. Copy the **Playlist ID** from the URL — it's the value after `?list=`:\n\n\n    https://www.youtube.com/playlist?list=THIS_IS_YOUR_PLAYLIST_ID\n\n\n* * *\n\n### Step 3 — Find Your Channel IDs\n\nFor each YouTube channel you want to track, you'll need its channel ID — not just its name or handle.\n\nThe easiest way: go to the channel's page, click **About** , then look at the share/copy link option. The URL will contain the channel ID starting with `UC`. Alternatively, sites like commentpicker.com/youtube-channel-id.php can look them up by URL.\n\nAdd them all to the `CHANNEL_IDS` array in the script:\n\n\n    var CHANNEL_IDS = [\n      'UCxxxxxxxxxxxxxxxxxxxxxx',\n      'UCyyyyyyyyyyyyyyyyyyyyyy',\n      // one per line, as many as you want\n    ];\n\n\n* * *\n\n### Step 4 — Create the Apps Script\n\n  1. Go to script.google.com and click **New project**.\n  2. Delete the default empty function in the editor.\n  3. Paste the full script in.\n  4. Update the configuration variables at the top with your Spreadsheet ID, Playlist ID, and Channel IDs from the steps above.\n  5. Give the project a name (click \"Untitled project\" at the top).\n\n\n\n* * *\n\n### Step 5 — Enable the YouTube Data API\n\nThe script uses Google's Advanced YouTube Service, which has to be explicitly enabled.\n\n  1. In the Apps Script editor, click **Extensions** in the top menu → **Advanced Google Services**.\n  2. Find **YouTube Data API v3** in the list and toggle it **on**.\n  3. Click **OK**.\n\n\n\nIf you see a prompt to also enable it in Google Cloud Console, follow the link and enable it there too. You'll need a Google Cloud project — Apps Script usually creates one automatically, or you can link an existing one under **Project Settings** (the gear icon in the left sidebar).\n\n* * *\n\n### Step 6 — Set Up the Scheduled Triggers\n\nThis is what makes the script run automatically without you having to do anything.\n\n  1. In the Apps Script editor, click the **clock icon** in the left sidebar (Triggers).\n  2. Click **+ Add Trigger** (bottom right).\n  3. Set up the first trigger:\n     * **Function** : `updatePlaylist`\n     * **Event source** : Time-driven\n     * **Type** : Day timer (or Hour timer if you want it more frequent)\n     * **Time** : Pick a time when you're unlikely to be actively using YouTube\n  4. Click **Save** , then **+ Add Trigger** again for the second one:\n     * **Function** : `runPlaylistCleanup`\n     * **Event source** : Time-driven\n     * **Type** : Week timer or Day timer (every 2–3 days is plenty)\n  5. Click **Save**.\n\n\n\n* * *\n\n### Step 7 — Authorize and Test\n\n  1. Back in the editor, select `updatePlaylist` from the function dropdown at the top.\n  2. Click **Run**.\n  3. A permissions dialog will appear — click **Review permissions** , choose your Google account, and click **Allow**.\n\n\n\nThe script needs permission to:\n\n  * Manage your YouTube account (to add/remove playlist items)\n  * Access Google Sheets (to read and write the log)\n\n\n\nBoth scopes are required. After authorizing, the function will run and you should see log output in the **Execution log** panel at the bottom. If everything is configured correctly, any qualifying videos from your channels posted in the last 8 days will show up in your playlist and spreadsheet.\n\n* * *\n\n## YouTube API Quota Considerations\n\nThe YouTube Data API v3 has a daily quota of 10,000 units. Here's a rough breakdown per `updatePlaylist` run:\n\nOperation | Cost | Typical calls\n---|---|---\n`Search.list` per channel | 100 units | 1 per channel\n`Videos.list` (batch of 50) | 1 unit | 1–2\n`PlaylistItems.list` (check) | 1 unit | per new video\n`PlaylistItems.insert` | 50 units | per new video\n\nWith 8 channels, you're spending ~800 units on searches alone per run. Add insertions and checks and a daily run should land well under 2,000 units — comfortably within the free quota.\n\n`cleanPlaylist` is the expensive one per-deletion (50 units each), so it's intentionally run less frequently.\n\n* * *\n\n## Ideas for Extending It\n\nA few things I've thought about but haven't implemented yet:\n\n  * **Category filtering** — use the `topicDetails` field to filter by topic category (e.g., only tech content).\n  * **Keyword exclusion** — skip videos whose titles match certain terms (useful for filtering out reaction videos or sponsored content).\n  * **Priority ordering** — insert high-priority channels' videos at the top of the playlist instead of appending.\n  * **Email digest** — send a weekly summary of what was added via `MailApp.sendEmail()`.\n  * **True watch state** — poll the YouTube History API (if you can work through its OAuth complexity) instead of using the log as a proxy.\n\n\n\n* * *\n\nThe full script is available as a GitHub Gist. If you set it up or adapt it for your own channels, I'd love to hear what changes you made.\n\nupdate-youtube-weekly-playlist.jsGitHub Gist: instantly share code, notes, and snippets.Gist262588213843476",
  "title": "Automating My YouTube Watch Queue with Google Apps Script",
  "updatedAt": "2026-05-27T07:49:37.995Z"
}