【General】Example of building a solution using YouTube API

Introduction

I've posted several articles on using the YouTube API in the past, and this article is related to that as well.
This time, I'm going to save my YouTube channel information in a local database and use it to create a report.
I would like to get and save Playlist information and Video information belonging to each Playlist from a YouTube channel.
I checked what kind of information can be obtained in advance and prepared the following database.
You can see a table for Playlist and a table for Video.

Since Sharperlight is good for handling data, let's build a solution with Sharperlight.

Solution

If you were to draw a diagram of your solution, it would look something like this.

DataModel

Please refer to this article for creating a data model.
Then I got a DataModel like this.
The basic information looks like this.

Schema definition here. I also tried adding a JOIN.

The DataModel is now ready.

Scheduled Task

Here, we will use Sharperlight scheduled task to create a process to get data using YouTube API.
The process flow is to first get the playlist list and save it in the playlist table. Get the PlaylistID from the table, loop for each ID, get the video information, and save it in the video table.

1. This action prepares parameters such as Google API Key and ChannelID.
2. Calling child tasks. The child task gets the Playlist list information from the YouTube API and stores it in the table [dbo].[YouTubePlayLists].
3. Define a Sharperlight query to read the Playlist information from the table [dbo].[YouTubePlayLists]. This action also serves as a For statement. You can pass the data extracted by the query row by row to the next action.
4. Using the PlaylistID passed by action 3, it now retrieves all information about the Videos contained in that Playlist from the YouTube API and stores them in the table [dbo].[YouTubeVideos].
5. This action is simply the action that marks the end of the For Loop. This is paired with the Query Combinations action.
   The actions defined between them will be executed repeatedly for the number of rows returned from the query in the Query Combinations action.

For your reference. These notations lib_task.ParamGet(), lib_sys.urlContent.Get() in the code below are JavaScript functions of Sharperlight. lib_task.ParamGet() function obtains the value of a parameter passed to the task and lib_sys.urlContent.Get() function makes a Get request.
Also, error handling has been omitted.

// Geting Information of the Playlist
var nextPageToken = "";
var url = lib_task.ParamGet('BaseAPIUrl') + "&maxResults=" + lib_task.ParamGet('MaxResultPerPage') + "&channelId=" + lib_task.ParamGet('ChannelId') + "&key=" + lib_task.ParamGet('GoogleAPIKey');
var xUrl = "";


for (var p=0; p<10; p++){
       if (nextPageToken == ""){
              xUrl = url;
       }else{
              xUrl = url + "&pageToken=" + nextPageToken;
       }
       console.log("P=" + p + ":  " + xUrl);
       let ytSnippet = lib_sys.urlContent.Get(xUrl);
       let ytSnippetObj = JSON.parse(ytSnippet);
       nextPageToken = ytSnippetObj.nextPageToken;
       let content = "";
       if (ytSnippetObj.items.length == 0) { break; }
       for (var i=0; i<ytSnippetObj.items.length; i++){
              _wb(ytSnippetObj.items[i]);
       }
       if (typeof nextPageToken === "undefined") { break; }

}

function _wb(item){
       var updateStatement = "UPDATE [dbo].[YouTubePlayLists] set \r\n \
              [ChannelId] = '<<ChannelId>>' \r\n \
              ,[Title] = N'<<Title>>' \r\n \
              ,[ChannelTitle] = N'<<ChannelTitle>>' \r\n \
              ,[PublishedAt] = '<<PublishedAt>>' \r\n \
              ,[RefreshedAt] = getdate() \r\n \
       WHERE [Id] = '<<Id>>';";
              
       var insertStatement = "\r\n IF @@ROWCOUNT=0 \r\n \
       BEGIN \r\n \
              insert into [dbo].[YouTubePlayLists] (\r\n \
              [Id], [ChannelId], [Title], [ChannelTitle], [IsEnabled], [PublishedAt], [RefreshedAt]) \r\n \
              VALUES ('<<Id>>', '<<ChannelId>>', N'<<Title>>', N'<<ChannelTitle>>', 0, '<<PublishedAt>>', getdate()); \r\n \
       END "; 

       var ups = updateStatement.replace('<<ChannelId>>', item.snippet.channelId);
       ups = ups.replace('<<Title>>', item.snippet.title.replace(',','@@@').replace(/'/g, "''"));
       ups = ups.replace('<<ChannelTitle>>', item.snippet.channelTitle.replace(',','@@@').replace(/'/g, "''"));
       ups = ups.replace('<<PublishedAt>>', item.snippet.publishedAt);
       ups = ups.replace('<<Id>>', item.id);

       var ins = insertStatement.replace('<<Id>>', item.id);
       ins = ins.replace('<<ChannelId>>', item.snippet.channelId);
       ins = ins.replace('<<Title>>', item.snippet.title.replace(',','@@@').replace(/'/g, "''"));
       ins = ins.replace('<<ChannelTitle>>', item.snippet.channelTitle.replace(',','@@@').replace(/'/g, "''"));
       ins = ins.replace('<<PublishedAt>>', item.snippet.publishedAt);



       var sqlStatement = ups + ins;
       console.log(sqlStatement);
       lib_app.sql.Execute(lib_task.UserSessionIdGet(),'YOUTUBE', sqlStatement); 
}

// Getting Video information for each Playlist
var nextPageToken = "";
var url = lib_task.ParamGet('BaseAPIUrl') + "&maxResults=" + lib_task.ParamGet('MaxResultPerPage') + "&playlistId=" + "{%Id}" + "&key=" + lib_task.ParamGet('GoogleAPIKey');
var xUrl = "";

for (var p=0; p<10; p++){
       if (nextPageToken == ""){
              xUrl = url;
       }else{
              xUrl = url + "&pageToken=" + nextPageToken;
       }
       
       console.log("P=" + p + ":  " + xUrl);
       let ytSnippet = lib_sys.urlContent.Get(xUrl);
       let ytSnippetObj = JSON.parse(ytSnippet);
       nextPageToken = ytSnippetObj.nextPageToken;
       let content = "";
       if (ytSnippetObj.items.length == 0) { break; }
       for (var i=0; i<ytSnippetObj.items.length; i++){
              -_wb(ytSnippetObj.items[i]);
       }
       if (typeof nextPageToken === "undefined") { break; }

}

function _wb(item){
       var updateStatement = "UPDATE [dbo].[YouTubeVideos] set \r\n \
              [ChannelId] = '<<ChannelId>>' \r\n \
              ,[Title] = N'<<Title>>' \r\n \
              ,[ChannelTitle] = N'<<ChannelTitle>>' \r\n \
              ,[Kind] = '<<Kind>>' \r\n \
              ,[VideoId] = '<<VideoId>>' \r\n \
              ,[Position] = '<<Position>>' \r\n \
              ,[PublishedAt] = '<<PublishedAt>>' \r\n \
              ,[RefreshedAt] = getdate() \r\n \
       WHERE [Id] = '<<Id>>' and [PlayListId] = '<<PlayListId>>';";
              
       var insertStatement = "\r\n IF @@ROWCOUNT=0 \r\n \
       BEGIN \r\n \
              insert into [dbo].[YouTubeVideos] (\r\n \
              [Id], [ChannelId], [Title], [ChannelTitle], [PlayListId], [Kind], [VideoId], [Position], [PublishedAt], [IsEnabled], [RefreshedAt]) \r\n \
              VALUES ('<<Id>>', '<<ChannelId>>', N'<<Title>>', N'<<ChannelTitle>>', '<<PlayListId>>', '<<Kind>>', '<<VideoId>>', '<<Position>>', '<<PublishedAt>>', 0, getdate()); \r\n \
       END "; 


       var ups = updateStatement.replace('<<ChannelId>>', item.snippet.channelId);
       ups = ups.replace('<<Title>>', item.snippet.title.replace(/'/g, "''"));
       ups = ups.replace('<<ChannelTitle>>', item.snippet.channelTitle.replace(/'/g, "''"));
       ups = ups.replace('<<PlayListId>>', item.snippet.playlistId);
       ups = ups.replace('<<Kind>>', item.snippet.resourceId.kind);
       ups = ups.replace('<<VideoId>>', item.snippet.resourceId.videoId);
       ups = ups.replace('<<Position>>', item.snippet.position);
       ups = ups.replace('<<PublishedAt>>', item.snippet.publishedAt);
       ups = ups.replace('<<Id>>', item.id);

       var ins = insertStatement.replace('<<Id>>', item.id);
       ins = ins.replace('<<ChannelId>>', item.snippet.channelId);
       ins = ins.replace('<<Title>>', item.snippet.title.replace(/'/g, "''"));
       ins = ins.replace('<<ChannelTitle>>', item.snippet.channelTitle.replace(/'/g, "''"));
       ins = ins.replace('<<PlayListId>>', item.snippet.playlistId);
       ins = ins.replace('<<Kind>>', item.snippet.resourceId.kind);
       ins = ins.replace('<<VideoId>>', item.snippet.resourceId.videoId);
       ins = ins.replace('<<Position>>', item.snippet.position);
       ins = ins.replace('<<PublishedAt>>', item.snippet.publishedAt);



       var sqlStatement = ups + ins;
       console.log(sqlStatement);
       lib_task.LogMessage(sqlStatement);
       lib_app.sql.Execute(lib_task.UserSessionIdGet(),'YOUTUBE', sqlStatement); 

}

It might be convenient to define a trigger for this task and run it every Monday morning at 5 am, for example.

Dataset

After the above tasks are performed, you will have a dataset like this. And you can use Sharperlight publisher to create web reports and the original video menus. Also you can use the Excel Add-in to create Excel reports.

In the next article, I will introduce the creation of an original menu for YouTube videos using this dataset.

In Conclusion

This example updates the dataset every Monday. If a new video is posted to the target YouTube channel during that time, that information will be newly saved in the database. That way, the data will be updated every week.
This method can be applied to other cloud applications as well. If only those APIs were substantial.
I hope this article helps you in your work.