{"id":16856,"date":"2024-12-21T20:29:42","date_gmt":"2024-12-21T11:29:42","guid":{"rendered":"https:\/\/84office.jp\/?p=16856"},"modified":"2024-12-21T20:29:43","modified_gmt":"2024-12-21T11:29:43","slug":"gas-youtube-data-acquisition","status":"publish","type":"post","link":"https:\/\/84office.jp\/en\/gas-youtube-data-acquisition\/","title":{"rendered":"How to Use the YouTube API to Fetch Video Data in Google Sheets: Automate with Google Apps Script!"},"content":{"rendered":"
<\/div>\n

n this article, I\u2019ll show you how to use the API key set up<\/a> in the previous post and leverage Google Apps Script (GAS) to compile YouTube video information into a Google Sheet.<\/p>\n\n\n\n

By using the YouTube API, you can easily analyze your own channel or compile video information from your favorite channels into a Google Sheet.<\/p>\n\n\n\n

Manual input is no longer required, and data such as video title, release date, channel title, and video URL can be efficiently retrieved.<\/p>\n\n\n

\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\u5408\u540c\u4f1a\u793e\u6a4b\u722a\u30aa\u30d5\u30a3\u30b9<\/span>\n\t\t\t\t\t
\"\"<\/figure><\/div>\t\t\t\t\t
\n\t\t\t\t\t\tYouTube API Key Setup: Export and Manage Video Data in Google Sheets | \u5408\u540c\u4f1a\u793e\u6a4b\u722a\u30aa\u30d5\u30a3\u30b9<\/a>\n\t\t\t\t\t\tTo retrieve video data and channel information from YouTube and manage it in Google Sheets, the YouTube API is a powerfu<\/span>\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t<\/div>\n\t\t<\/div>\n\n\n

Completed image<\/h2>\n\n\n\n
\"\"<\/figure>\n\n\n\n

Listed in a spreadsheet based on the specified channel ID and number of videos<\/p>\n\n\n\n


Basic information about each video is listed in a spreadsheet for easy visual understanding and organization.<\/p>\n\n\n\n

Prepare YouTube API Key<\/h2>\n\n\n\n

Obtain a YouTube Data API v3 API key and set it to a script property.<\/p>\n\n\n\n

Script Property Name Example: \"YOUTUBE_DATA_API_KEY\"<\/code><\/p>\n\n\n

\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\u5408\u540c\u4f1a\u793e\u6a4b\u722a\u30aa\u30d5\u30a3\u30b9<\/span>\n\t\t\t\t\t
\"\"<\/figure><\/div>\t\t\t\t\t
\n\t\t\t\t\t\tYouTube API Key Setup: Export and Manage Video Data in Google Sheets | \u5408\u540c\u4f1a\u793e\u6a4b\u722a\u30aa\u30d5\u30a3\u30b9<\/a>\n\t\t\t\t\t\tTo retrieve video data and channel information from YouTube and manage it in Google Sheets, the YouTube API is a powerfu<\/span>\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t<\/div>\n\t\t<\/div>\n\n\n

Steps<\/h2>\n\n\n\n
\n
STEP<\/span><\/div>
Preparing Spreadsheets<\/div>
\n
\"\"<\/figure>\n<\/div>\n\n\n

Enter the YouTube Channel ID in cell B1<\/strong>.<\/p>\n\n\n\n

In cell B2<\/strong>, enter the starting position, and in cell B3<\/strong>, enter the ending position to retrieve data.<\/p>\n\n\n\n

For example, if you set it to 1\u201310, the script will retrieve the 10 most recent videos.<\/p>\n\n\n\n

The sheet is referenced as “Video Information” during script execution.<\/p>\n\n\n\n

Modify the sheet name as needed.<\/p>\n\n\n\n

In row 5, enter the following items as headers:<\/p>\n\n\n\n

Header Items (Example):<\/p>\n\n\n\n

\u5165\u529b\u5185\u5bb9<\/th><\/tr><\/thead>
A5<\/td>Video ID<\/td><\/tr>
B5<\/td>Title<\/td><\/tr>
C5<\/td>Published Date<\/td><\/tr>
D5<\/td>Thumbnail Image<\/td><\/tr>
E5<\/td>Channel Title<\/td><\/tr>
F5<\/td>View Count<\/td><\/tr>
G5<\/td>Like Count<\/td><\/tr>
H5<\/td>Video Duration<\/td><\/tr>
I5<\/td>Video URL<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n\n<\/div><\/div>\n\n\n\n
STEP<\/span><\/div>
Open Apps Script Editor<\/div>
\n
\"\"<\/figure>\n<\/div>\n\n\n

Open a Google Spreadsheet and click on \u201cExtensions<\/strong>\u201d in the menu, then \u201cApps Script<\/strong>\u201d to open the Apps Script editor.<\/p>\n<\/div><\/div>\n\n\n\n

STEP<\/span><\/div>
Create the Script<\/strong><\/div>
\n
\"\"<\/figure>\n<\/div>\n\n\n

function myFunction(){<\/p>\n\n\n\n

}<\/p>\n\n\n\n

is included from the beginning, so delete it and paste the previous script.<\/p>\n\n\n\n

<\/path><\/path><\/svg><\/span>
function<\/span> <\/span>fetchAndWriteYouTubeData<\/span>() {<\/span><\/span>\n    <\/span>var<\/span> sheet <\/span>=<\/span> SpreadsheetApp.<\/span>getActiveSpreadsheet<\/span>().<\/span>getSheetByName<\/span>(<\/span>"Video Information"<\/span>);<\/span><\/span>\n    <\/span>if<\/span> (<\/span>!<\/span>sheet) {<\/span><\/span>\n        Logger.<\/span>log<\/span>(<\/span>"Sheet 'Video Information' not found."<\/span>);<\/span><\/span>\n        <\/span>return<\/span>;<\/span><\/span>\n    }<\/span><\/span>\n<\/span>\n    <\/span>\/\/ Retrieve the API key from script properties<\/span><\/span>\n    <\/span>var<\/span> apiKey <\/span>=<\/span> PropertiesService.<\/span>getScriptProperties<\/span>().<\/span>getProperty<\/span>(<\/span>'YOUTUBE_DATA_API_KEY'<\/span>);<\/span><\/span>\n    Logger.<\/span>log<\/span>(<\/span>"API Key: "<\/span> <\/span>+<\/span> apiKey);<\/span><\/span>\n    <\/span><\/span>\n    <\/span>\/\/ Retrieve the channel ID from cell B1<\/span><\/span>\n    <\/span>var<\/span> channelId <\/span>=<\/span> sheet.<\/span>getRange<\/span>(<\/span>"B1"<\/span>).<\/span>getValue<\/span>();<\/span><\/span>\n    Logger.<\/span>log<\/span>(<\/span>"Channel ID: "<\/span> <\/span>+<\/span> channelId);<\/span><\/span>\n    <\/span><\/span>\n    <\/span>\/\/ Retrieve the range from cells B2 and B3<\/span><\/span>\n    <\/span>var<\/span> startIndex <\/span>=<\/span> <\/span>parseInt<\/span>(sheet.<\/span>getRange<\/span>(<\/span>"B2"<\/span>).<\/span>getValue<\/span>()) <\/span>||<\/span> <\/span>1<\/span>;<\/span><\/span>\n    <\/span>var<\/span> endIndex <\/span>=<\/span> <\/span>parseInt<\/span>(sheet.<\/span>getRange<\/span>(<\/span>"B3"<\/span>).<\/span>getValue<\/span>()) <\/span>||<\/span> <\/span>10<\/span>;<\/span><\/span>\n    <\/span>var<\/span> maxResults <\/span>=<\/span> Math.<\/span>min<\/span>(endIndex <\/span>-<\/span> startIndex <\/span>+<\/span> <\/span>1<\/span>, <\/span>100<\/span>);<\/span><\/span>\n    Logger.<\/span>log<\/span>(<\/span>`Start Index: <\/span>${<\/span>startIndex<\/span>}<\/span>, End Index: <\/span>${<\/span>endIndex<\/span>}<\/span>`<\/span>);<\/span><\/span>\n<\/span>\n    <\/span>\/\/ YouTube Data API URL<\/span><\/span>\n    <\/span>var<\/span> baseUrl <\/span>=<\/span> <\/span>`https:\/\/www.googleapis.com\/youtube\/v3\/search?key=<\/span>${<\/span>apiKey<\/span>}<\/span>&channelId=<\/span>${<\/span>channelId<\/span>}<\/span>&part=snippet&order=date&type=video&maxResults=100`<\/span>;<\/span><\/span>\n    <\/span><\/span>\n    <\/span>var<\/span> videos <\/span>=<\/span> []; <\/span>\/\/ Store video data<\/span><\/span>\n    <\/span>var<\/span> nextPageToken <\/span>=<\/span> <\/span>''<\/span>;<\/span><\/span>\n    <\/span>var<\/span> totalFetched <\/span>=<\/span> <\/span>0<\/span>;<\/span><\/span>\n<\/span>\n    <\/span>\/\/ Use pagination to fetch video data<\/span><\/span>\n    <\/span>while<\/span> (nextPageToken <\/span>!==<\/span> <\/span>null<\/span> <\/span>&&<\/span> totalFetched <\/span><<\/span> endIndex) {<\/span><\/span>\n        <\/span>var<\/span> url <\/span>=<\/span> baseUrl <\/span>+<\/span> (nextPageToken <\/span>?<\/span> <\/span>`&pageToken=<\/span>${<\/span>nextPageToken<\/span>}<\/span>`<\/span> <\/span>:<\/span> <\/span>''<\/span>);<\/span><\/span>\n        <\/span>var<\/span> response <\/span>=<\/span> UrlFetchApp.<\/span>fetch<\/span>(url);<\/span><\/span>\n        <\/span>var<\/span> data <\/span>=<\/span> JSON.<\/span>parse<\/span>(response.<\/span>getContentText<\/span>());<\/span><\/span>\n        <\/span>if<\/span> (<\/span>!<\/span>data.items) {<\/span><\/span>\n            Logger.<\/span>log<\/span>(<\/span>"No data retrieved."<\/span>);<\/span><\/span>\n            <\/span>break<\/span>;<\/span><\/span>\n        }<\/span><\/span>\n<\/span>\n        videos <\/span>=<\/span> videos.<\/span>concat<\/span>(data.items);<\/span><\/span>\n        totalFetched <\/span>+=<\/span> data.items.length;<\/span><\/span>\n<\/span>\n        nextPageToken <\/span>=<\/span> data.nextPageToken <\/span>||<\/span> <\/span>null<\/span>;<\/span><\/span>\n        Logger.<\/span>log<\/span>(<\/span>`Fetched <\/span>${<\/span>data.items.length<\/span>}<\/span> videos, Total fetched: <\/span>${<\/span>totalFetched<\/span>}<\/span>`<\/span>);<\/span><\/span>\n    }<\/span><\/span>\n<\/span>\n    <\/span>\/\/ Extract the required range and retrieve video IDs<\/span><\/span>\n    <\/span>var<\/span> videoIds <\/span>=<\/span> videos.<\/span>slice<\/span>(startIndex <\/span>-<\/span> <\/span>1<\/span>, endIndex).<\/span>map<\/span>(<\/span>item<\/span> <\/span>=><\/span> item.id.videoId).<\/span>join<\/span>(<\/span>','<\/span>);<\/span><\/span>\n<\/span>\n    <\/span>\/\/ Fetch detailed information (view count, likes, duration) using the videos endpoint<\/span><\/span>\n    <\/span>var<\/span> detailsUrl <\/span>=<\/span> <\/span>`https:\/\/www.googleapis.com\/youtube\/v3\/videos?key=<\/span>${<\/span>apiKey<\/span>}<\/span>&id=<\/span>${<\/span>videoIds<\/span>}<\/span>&part=snippet,contentDetails,statistics`<\/span>;<\/span><\/span>\n    <\/span>var<\/span> detailsResponse <\/span>=<\/span> UrlFetchApp.<\/span>fetch<\/span>(detailsUrl);<\/span><\/span>\n    <\/span>var<\/span> detailsData <\/span>=<\/span> JSON.<\/span>parse<\/span>(detailsResponse.<\/span>getContentText<\/span>());<\/span><\/span>\n<\/span>\n    <\/span>\/\/ Get the next row to write data<\/span><\/span>\n    <\/span>var<\/span> lastRow <\/span>=<\/span> sheet.<\/span>getLastRow<\/span>();<\/span><\/span>\n<\/span>\n    <\/span>\/\/ Write data to the spreadsheet<\/span><\/span>\n    detailsData.items.<\/span>forEach<\/span>(<\/span>function<\/span>(<\/span>item<\/span>, <\/span>index<\/span>) {<\/span><\/span>\n        <\/span>var<\/span> videoId <\/span>=<\/span> item.id;<\/span><\/span>\n        <\/span>var<\/span> title <\/span>=<\/span> item.snippet.title;<\/span><\/span>\n        <\/span>var<\/span> publishedAt <\/span>=<\/span> item.snippet.publishedAt;<\/span><\/span>\n        <\/span>var<\/span> thumbnailUrl <\/span>=<\/span> item.snippet.thumbnails.default.url; <\/span>\/\/ Default thumbnail URL<\/span><\/span>\n        <\/span>var<\/span> channelTitle <\/span>=<\/span> item.snippet.channelTitle;<\/span><\/span>\n        <\/span>var<\/span> viewCount <\/span>=<\/span> item.statistics.viewCount;<\/span><\/span>\n        <\/span>var<\/span> likeCount <\/span>=<\/span> item.statistics.likeCount;<\/span><\/span>\n        <\/span>var<\/span> videoUrl <\/span>=<\/span> <\/span>`https:\/\/www.youtube.com\/watch?v=<\/span>${<\/span>videoId<\/span>}<\/span>`<\/span>;<\/span><\/span>\n<\/span>\n        <\/span>\/\/ Format the video duration from ISO 8601<\/span><\/span>\n        <\/span>var<\/span> duration <\/span>=<\/span> <\/span>formatDuration<\/span>(item.contentDetails.duration);<\/span><\/span>\n<\/span>\n        Logger.<\/span>log<\/span>(<\/span>`Writing to row <\/span>${<\/span>lastRow <\/span>+<\/span> index <\/span>+<\/span> <\/span>1<\/span>}<\/span>: <\/span>${<\/span>title<\/span>}<\/span>`<\/span>);<\/span><\/span>\n<\/span>\n        sheet.<\/span>getRange<\/span>(lastRow <\/span>+<\/span> index <\/span>+<\/span> <\/span>1<\/span>, <\/span>1<\/span>).<\/span>setValue<\/span>(videoId);         <\/span>\/\/ Column A: Video ID<\/span><\/span>\n        sheet.<\/span>getRange<\/span>(lastRow <\/span>+<\/span> index <\/span>+<\/span> <\/span>1<\/span>, <\/span>2<\/span>).<\/span>setValue<\/span>(title);           <\/span>\/\/ Column B: Title<\/span><\/span>\n        sheet.<\/span>getRange<\/span>(lastRow <\/span>+<\/span> index <\/span>+<\/span> <\/span>1<\/span>, <\/span>3<\/span>).<\/span>setValue<\/span>(publishedAt);     <\/span>\/\/ Column C: Publish Date<\/span><\/span>\n        sheet.<\/span>getRange<\/span>(lastRow <\/span>+<\/span> index <\/span>+<\/span> <\/span>1<\/span>, <\/span>4<\/span>).<\/span>setFormula<\/span>(<\/span>`=IMAGE("<\/span>${<\/span>thumbnailUrl<\/span>}<\/span>")`<\/span>); <\/span>\/\/ Column D: Thumbnail<\/span><\/span>\n        sheet.<\/span>getRange<\/span>(lastRow <\/span>+<\/span> index <\/span>+<\/span> <\/span>1<\/span>, <\/span>5<\/span>).<\/span>setValue<\/span>(channelTitle);    <\/span>\/\/ Column E: Channel Title<\/span><\/span>\n        sheet.<\/span>getRange<\/span>(lastRow <\/span>+<\/span> index <\/span>+<\/span> <\/span>1<\/span>, <\/span>6<\/span>).<\/span>setValue<\/span>(viewCount);       <\/span>\/\/ Column F: View Count<\/span><\/span>\n        sheet.<\/span>getRange<\/span>(lastRow <\/span>+<\/span> index <\/span>+<\/span> <\/span>1<\/span>, <\/span>7<\/span>).<\/span>setValue<\/span>(likeCount);       <\/span>\/\/ Column G: Like Count<\/span><\/span>\n        sheet.<\/span>getRange<\/span>(lastRow <\/span>+<\/span> index <\/span>+<\/span> <\/span>1<\/span>, <\/span>8<\/span>).<\/span>setValue<\/span>(duration);        <\/span>\/\/ Column H: Video Duration<\/span><\/span>\n        sheet.<\/span>getRange<\/span>(lastRow <\/span>+<\/span> index <\/span>+<\/span> <\/span>1<\/span>, <\/span>9<\/span>).<\/span>setValue<\/span>(videoUrl);        <\/span>\/\/ Column I: Video URL<\/span><\/span>\n    });<\/span><\/span>\n}<\/span><\/span>\n<\/span>\n\/\/ Convert ISO 8601 duration to "HH:MM:SS" format<\/span><\/span>\nfunction<\/span> <\/span>formatDuration<\/span>(<\/span>duration<\/span>) {<\/span><\/span>\n    <\/span>var<\/span> matches <\/span>=<\/span> duration.<\/span>match<\/span>(<\/span>\/PT(<\/span>\\d<\/span>+<\/span>H)<\/span>?<\/span>(<\/span>\\d<\/span>+<\/span>M)<\/span>?<\/span>(<\/span>\\d<\/span>+<\/span>S)<\/span>?<\/span>\/<\/span>);<\/span><\/span>\n    <\/span>var<\/span> hours <\/span>=<\/span> (matches[<\/span>1<\/span>] <\/span>?<\/span> matches[<\/span>1<\/span>].<\/span>replace<\/span>(<\/span>'H'<\/span>, <\/span>''<\/span>) <\/span>:<\/span> <\/span>'00'<\/span>).<\/span>padStart<\/span>(<\/span>2<\/span>, <\/span>'0'<\/span>);<\/span><\/span>\n    <\/span>var<\/span> minutes <\/span>=<\/span> (matches[<\/span>2<\/span>] <\/span>?<\/span> matches[<\/span>2<\/span>].<\/span>replace<\/span>(<\/span>'M'<\/span>, <\/span>''<\/span>) <\/span>:<\/span> <\/span>'00'<\/span>).<\/span>padStart<\/span>(<\/span>2<\/span>, <\/span>'0'<\/span>);<\/span><\/span>\n    <\/span>var<\/span> seconds <\/span>=<\/span> (matches[<\/span>3<\/span>] <\/span>?<\/span> matches[<\/span>3<\/span>].<\/span>replace<\/span>(<\/span>'S'<\/span>, <\/span>''<\/span>) <\/span>:<\/span> <\/span>'00'<\/span>).<\/span>padStart<\/span>(<\/span>2<\/span>, <\/span>'0'<\/span>);<\/span><\/span>\n    <\/span>return<\/span> <\/span>`<\/span>${<\/span>hours<\/span>}<\/span>:<\/span>${<\/span>minutes<\/span>}<\/span>:<\/span>${<\/span>seconds<\/span>}<\/span>`<\/span>;<\/span><\/span>\n}<\/span><\/span>\n<\/span><\/code><\/pre><\/div>\n\n\n\n
\n
Script Description<\/span><\/i><\/i><\/span><\/summary>
\n
\n
Checking the Sheet and Retrieving the API Key<\/strong><\/dt>\n\n\n\n
\n
<\/path><\/path><\/svg><\/span>
    <\/span>var<\/span> sheet <\/span>=<\/span> SpreadsheetApp.<\/span>getActiveSpreadsheet<\/span>().<\/span>getSheetByName<\/span>(<\/span>"Video Information"<\/span>);<\/span><\/span>\n    <\/span>if<\/span> (<\/span>!<\/span>sheet) {<\/span><\/span>\n        Logger.<\/span>log<\/span>(<\/span>"Sheet 'Video Information' not found."<\/span>);<\/span><\/span>\n        <\/span>return<\/span>;<\/span><\/span>\n    }<\/span><\/span>\n<\/span>\n    <\/span>\/\/ Retrieve the API key from script properties<\/span><\/span>\n    <\/span>var<\/span> apiKey <\/span>=<\/span> PropertiesService.<\/span>getScriptProperties<\/span>().<\/span>getProperty<\/span>(<\/span>'YOUTUBE_DATA_API_KEY'<\/span>);<\/span><\/span>\n    Logger.<\/span>log<\/span>(<\/span>"API Key: "<\/span> <\/span>+<\/span> apiKey);<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n