0

Here's a concrete example:

https://www.youtube.com/user/AmazonWebServices/videos

I estimate one would have to scroll that page for many hours before getting it to show all the videos in that collection.

I'm looking for a faster way to generate a two-column table consisting of the title and the url for all the videos in that collection.

As this example illustrates, strategies that involve scrolling are unacceptably slow for cases like this one.

(Solutions that involve the Unix command line and/or some scripting are acceptable, as long as the commands and/or source code are provided in the answer.)


EDIT: I apologize: I should have specified that my OS is Linux.

EDIT2: Here's what I ultimately did, based on the accepted answer (at the Unix shell prompt):

% youtube-dl --get-id --get-title https://www.youtube.com/user/AmazonWebServices/videos | \
     | perl -pe 's/^(?=\S{11}$)/\t/ || s/\r?\n\z//' > videos.tsv

The youtube-dl command above returns lines of titles alternating with video IDs. The perl one-liner, either inserts a tab or deletes the line-terminating sequence, as the case may be, resulting in the desired 2-column listing. (I'll probably tweak this a bit further to reverse the order of the columns, but this is an inessential detail.)

NB: Although the above procedure still takes a long time (mine has been running for about 15 minutes and has yielded ~1000 lines, but it's still going strong). Nonetheless, once one types the command above, the rest is pretty much "hands-free". It certainly beats manually scrolling through the page.

1

youtube-dl --get-id --get-title https://www.youtube.com/user/AmazonWebServices/videos > list.txt

  • run/execute BATCH.cmd by double-clicking
  • wait until cmd window closes (it can take even half an hour - depends on your internet speed)
  • when done you will get a non-coma-non-space separated bulk of text:

  • to get it formated open it in WordPad or MS Word

  • copy it and paste it into the spreadsheet column A - https://docs.ggl.com/spreadsheets/d/
  • where cell B1: =FILTER(A:A; ROW(A:A)=EVEN(ROW(A:A)))
  • and where cell C1: =FILTER(A:A; ROW(A:A)=ODD(ROW(A:A)))

  • and then on another sheet create your construct
  • cell A2: =IF(Sheet2!B1<>"";"https://www.youtube.com/watch?v="&Sheet2!B1;)
  • cell B2: =IF(Sheet2!C1<>""; Sheet2!C1;)

1

well, you can use YT API like shown here: https://www.youtube.com/watch?v=

either with your own API key from here: https://console.developers.google.com/api/credentials

and constructing own URL:

https://www.googleapis.com/youtube/v3/playlistItems?
        part=snippet
        &maxResults=50
        &playlistId=UUd6MoB9NC6uYN2grvUNT-Zg
        &key=YOUR-API-KEY-HERE

or using their API explorer: https://developers.google.com/apis-explorer/#p/
via youtube.playlistitems.list where:

https://developers.google.com/apis-explorer/
        #search/youtube.playlistitems.list/m/youtube/v3/youtube.playlistItems.list?
        part=snippet
        &maxResults=50
        &playlistId=UUd6MoB9NC6uYN2grvUNT-Zg

which will give you output (after pressing Execute button) like:

Response

200

- Show headers -

{
 "kind": "youtube#playlistItemListResponse",
 "etag": "\"XI7nbFXulYBIpL0ayR_gDh3eu1k/D7gBQAd-4MQN69UKlztEwfCDya8\"",
 "nextPageToken": "CDIQAA",
 "pageInfo": {
  "totalResults": 6569,
  "resultsPerPage": 50
 },
 "items": [
  {


   "kind": "youtube#playlistItem",
   "etag": "\"XI7nbFXulYBIpL0ayR_gDh3eu1k/_ty6s1XIJUHuVI8260HoGSW5J2E\"",
   "id": "VVVkNk1vQjlOQzZ1WU4yZ3J2VU5ULVpnLk10VzI3MS12RWFJ",
   "snippet": {
    "publishedAt": "2018-08-03T18:23:43.000Z",
    "channelId": "UCd6MoB9NC6uYN2grvUNT-Zg",
    "title": "Amazon Redshift の紹介 | AWS (日本語字幕) (03:21)",
    "description": "Amazon Redshift は高速で、スケーラブルなデータウェアハウスで、お使いのデータウェアハウスとデータレイクすべてにわたる分析をシンプルで費用対効果高く行えます. 詳細はこちら » https://amzn.to/2O8WhNZ",
    "thumbnails": {
     "default": {
      "url": "https://i.ytimg.com/vi/MtW271-vEaI/default.jpg",
      "width": 120,
      "height": 90
     },
     "medium": {
      "url": "https://i.ytimg.com/vi/MtW271-vEaI/mqdefault.jpg",
      "width": 320,
      "height": 180
     },
     "high": {
      "url": "https://i.ytimg.com/vi/MtW271-vEaI/hqdefault.jpg",
      "width": 480,
      "height": 360
     },
     "standard": {
      "url": "https://i.ytimg.com/vi/MtW271-vEaI/sddefault.jpg",
      "width": 640,
      "height": 480
     },
     "maxres": {
      "url": "https://i.ytimg.com/vi/MtW271-vEaI/maxresdefault.jpg",
      "width": 1280,
      "height": 720
     }
    },
    "channelTitle": "Amazon Web Services",
    "playlistId": "UUd6MoB9NC6uYN2grvUNT-Zg",
    "position": 0,
    "resourceId": {
     "kind": "youtube#video",
     "videoId": "MtW271-vEaI"
    }
   }
  },
  {


   "kind": "youtube#playlistItem",
   "etag": "\"XI7nbFXulYBIpL0ayR_gDh3eu1k/NIq5gMBZHwB-uYYfjFTPf21ZzmU\"",
   "id": "VVVkNk1vQjlOQzZ1WU4yZ3J2VU5ULVpnLjNFYmU3U3JZSkxB",
   "snippet": {
    "publishedAt": "2018-08-03T18:23:09.000Z",
    "channelId": "UCd6MoB9NC6uYN2grvUNT-Zg",
    "title": "Amazon EC2 リザーブドインスタンス (RI) の紹介 | AWS (日本語字幕) (03:11)",
    "description": "Amazon EC2 リザーブドインスタンス (RI) では、オンデマンド料金に比べて大幅な割引価格 (最大 75%) が適用され、特定のアベイラビリティーゾーンで使用するキャパシティーを予約できます。詳細はこちら » https://amzn.to/2vuQIle",
    "thumbnails": {
     "default": {
      "url": "https://i.ytimg.com/vi/3Ebe7SrYJLA/default.jpg",
      "width": 120,
      "height": 90
     },
     "medium": {
      "url": "https://i.ytimg.com/vi/3Ebe7SrYJLA/mqdefault.jpg",
      "width": 320,
      "height": 180
     },
     "high": {
      "url": "https://i.ytimg.com/vi/3Ebe7SrYJLA/hqdefault.jpg",
      "width": 480,
      "height": 360
     },
     "standard": {
      "url": "https://i.ytimg.com/vi/3Ebe7SrYJLA/sddefault.jpg",
      "width": 640,
      "height": 480
     },
     "maxres": {
      "url": "https://i.ytimg.com/vi/3Ebe7SrYJLA/maxresdefault.jpg",
      "width": 1280,
      "height": 720
     }
    },
    "channelTitle": "Amazon Web Services",
    "playlistId": "UUd6MoB9NC6uYN2grvUNT-Zg",
    "position": 1,
    "resourceId": {
     "kind": "youtube#video",
     "videoId": "3Ebe7SrYJLA"
    }
   }
  },
  {


   "kind": "youtube#playlistItem",
   "etag": "\"XI7nbFXulYBIpL0ayR_gDh3eu1k/fikLEPy1a1IvwPkRegGMStcm4sM\"",
   "id": "VVVkNk1vQjlOQzZ1WU4yZ3J2VU5ULVpnLk1VX0lkdTkzRTdZ",
   "snippet": {
    "publishedAt": "2018-08-03T18:24:10.000Z",
    "channelId": "UCd6MoB9NC6uYN2grvUNT-Zg",
    "title": "Amazon DynamoDB の紹介 | AWS (日本語字幕) (00:39)",
    "description": "Amazon DynamoDB は、どのような規模でも信頼性の高いパフォーマンスを維持できる、非リレーショナルデータベースです。詳細はこちら » https://amzn.to/2vBlRn4",
    "thumbnails": {
     "default": {
      "url": "https://i.ytimg.com/vi/MU_Idu93E7Y/default.jpg",
      "width": 120,
      "height": 90
     },
     "medium": {
      "url": "https://i.ytimg.com/vi/MU_Idu93E7Y/mqdefault.jpg",
      "width": 320,
      "height": 180
     },
     "high": {
      "url": "https://i.ytimg.com/vi/MU_Idu93E7Y/hqdefault.jpg",
      "width": 480,
      "height": 360
     },
     "standard": {
      "url": "https://i.ytimg.com/vi/MU_Idu93E7Y/sddefault.jpg",
      "width": 640,
      "height": 480
     },
     "maxres": {
      "url": "https://i.ytimg.com/vi/MU_Idu93E7Y/maxresdefault.jpg",
      "width": 1280,
      "height": 720
     }
    },
    "channelTitle": "Amazon Web Services",
    "playlistId": "UUd6MoB9NC6uYN2grvUNT-Zg",
    "position": 2,
    "resourceId": {
     "kind": "youtube#video",
     "videoId": "MU_Idu93E7Y"
    }
   }
  },
  {


   "kind": "youtube#playlistItem",
   "etag": "\"XI7nbFXulYBIpL0ayR_gDh3eu1k/kiueI_etzVk4p3-Emyd7Bor8Xi0\"",
   "id": "VVVkNk1vQjlOQzZ1WU4yZ3J2VU5ULVpnLndmX0Q5ckdZOHdN",
   "snippet": {
    "publishedAt": "2018-08-03T18:22:46.000Z",
    "channelId": "UCd6MoB9NC6uYN2grvUNT-Zg",
    "title": "Amazon Comprehend の紹介 | AWS (日本語字幕) (03:08)",
    "description": "Amazon Comprehend は、機械学習を使用してテキスト内でインサイトや関係性を検出する自然言語処理 (NLP) サービスです。詳細はこちら » hthttps://amzn.to/2LU1k8E",
    "thumbnails": {
     "default": {
      "url": "https://i.ytimg.com/vi/wf_D9rGY8wM/default.jpg",
      "width": 120,
      "height": 90
     },
     "medium": {
      "url": "https://i.ytimg.com/vi/wf_D9rGY8wM/mqdefault.jpg",
      "width": 320,
      "height": 180
     },
     "high": {
      "url": "https://i.ytimg.com/vi/wf_D9rGY8wM/hqdefault.jpg",
      "width": 480,
      "height": 360
     },
     "standard": {
      "url": "https://i.ytimg.com/vi/wf_D9rGY8wM/sddefault.jpg",
      "width": 640,
      "height": 480
     },
     "maxres": {
      "url": "https://i.ytimg.com/vi/wf_D9rGY8wM/maxresdefault.jpg",
      "width": 1280,
      "height": 720
     }
    },
    "channelTitle": "Amazon Web Services",
    "playlistId": "UUd6MoB9NC6uYN2grvUNT-Zg",
    "position": 3,
    "resourceId": {
     "kind": "youtube#video",
     "videoId": "wf_D9rGY8wM"
    }
   }
  },
  {


   "kind": "youtube#playlistItem",
   "etag": "\"XI7nbFXulYBIpL0ayR_gDh3eu1k/Er5sx2hR2t3jY4ORHB08vnIFwUg\"",
   "id": "VVVkNk1vQjlOQzZ1WU4yZ3J2VU5ULVpnLkk2eTRCTElwZjdJ",
   "snippet": {
    "publishedAt": "2018-08-03T18:20:43.000Z",
    "channelId": "UCd6MoB9NC6uYN2grvUNT-Zg",
    "title": "AWS Elemental MediaLive の紹介 | AWS (日本語字幕) (00:41)",
    "description": "AWS Elemental MediaLive は、ブロードキャストグレードのライブ動画処理サービスです。テレビ放送やインターネット接続のマルチスクリーンデバイス (インターネット接続対応の TV、タブレット、スマートフォン、セットトップボックス) での配信用に、高品質なビデオストリームを作成できます。詳細はこちら » https://amzn.to/2vd54rs",
    "thumbnails": {
     "default": {
      "url": "https://i.ytimg.com/vi/I6y4BLIpf7I/default.jpg",
      "width": 120,
      "height": 90
     },
     "medium": {
      "url": "https://i.ytimg.com/vi/I6y4BLIpf7I/mqdefault.jpg",
      "width": 320,
      "height": 180
     },
     "high": {
      "url": "https://i.ytimg.com/vi/I6y4BLIpf7I/hqdefault.jpg",
      "width": 480,
      "height": 360
     },
     "standard": {
      "url": "https://i.ytimg.com/vi/I6y4BLIpf7I/sddefault.jpg",
      "width": 640,
      "height": 480
     },
     "maxres": {
      "url": "https://i.ytimg.com/vi/I6y4BLIpf7I/maxresdefault.jpg",
      "width": 1280,
      "height": 720
     }
    },
    "channelTitle": "Amazon Web Services",
    "playlistId": "UUd6MoB9NC6uYN2grvUNT-Zg",
    "position": 4,
    "resourceId": {
     "kind": "youtube#video",
     "videoId": "I6y4BLIpf7I"
    }
   }
  },
  {

etc..

from which you need to scrape "videoId": to construct URL and "title": as requested

NOTE: this method sucks because there is 50-result-limitation per page load, so to get results 51-100 you need to enter "nextPageToken": under pageToken and repeat execution like:

https://developers.google.com/apis-explorer/
        #search/youtube.playlistitems.list/m/youtube/v3/youtube.playlistItems.list?
        part=snippet
        &maxResults=50
        &playlistId=UUd6MoB9NC6uYN2grvUNT-Zg
        &pageToken=CDIQAA

for page 3:

https://developers.google.com/apis-explorer/
        #search/youtube.playlistitems.list/m/youtube/v3/youtube.playlistItems.list?
        part=snippet
        &maxResults=50
        &playlistId=UUd6MoB9NC6uYN2grvUNT-Zg
        &pageToken=CJYBEAA

etc...

and then use a spreadsheet where you filter out only what you need using =FILTER / =QUERY / =REGEXMATCH etc...

0

if you possess video IDs you can use a spreadsheet to generate what you need:

  • cell A5: ID
  • cell B5: =IF(A5<>""; "https://www.youtube.com/watch?v="&A5;)
  • cell C5: =IF(A5<>""; INDEX( IMPORTXML(B5; "//div/h1"); 2; 1);)

NOTE: =IMPORTXML() requests can't be handled at once if they are too much eg. it can take some time for a sheet to pick up those data flows

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.