Power Query. YouTube Data Api. Примеры запросов

Получаем id и названия плейлистов канала

Получим следующую таблицу:

let
   api_request = Json.Document(
      Web.Contents(
         "https://www.googleapis.com/youtube/v3/playlists", 
         [
            Query = [
               part
                  = "contentDetails,id,localizations,player,snippet,status", 
               channelId = channel_id,
               key = Api_Key, 
               maxResults = "50"
            ]
         ]
      )
   ), 
   get_data = Table.FromList(
      api_request[items], 
      Splitter.SplitByNothing(), 
      null, 
      null, 
      ExtraValues.Error
   ), 
   tab_add_col_pl_id = Table.AddColumn(
      get_data, 
      "playlist_id", 
      each [Column1][id]
   ), 
   tab_add_col_pl_name = Table.AddColumn(
      tab_add_col_pl_id, 
      "playlist_name", 
      each [Column1][snippet][title]
   ), 
   cols_select = Table.SelectColumns(
      tab_add_col_pl_name, 
      {"playlist_id", "playlist_name"}
   )
in
   cols_select

Получаем id всех видео из плейлиста

Получим следующую таблицу:

let
   tab_page_tokens = #table(
      {"pageToken"}, 
      {{"CAAQAA"}, {"CDIQAA"}, {"CGQQAA"}, {"CJYBEAA"}}
   ), 
   tab_add_col_request = Table.AddColumn(
      tab_page_tokens, 
      "Пользовательская", 
      each Json.Document(
         Web.Contents(
            "https://www.googleapis.com/youtube/v3/playlistItems", 
            [
               Query = [
                  part       = "contentDetails,id,snippet,status", 
                  playlistId = playlist_id_example, 
                  key        = Api_Key, 
                  maxResults = "50", 
                  pageToken  = [pageToken]
               ]
            ]
         )
      )[items]
   ), 
   rows_remove_errs = Table.RemoveRowsWithErrors(
      tab_add_col_request, 
      {"Пользовательская"}
   ), 
   col_expand_1 = Table.ExpandListColumn(
      rows_remove_errs, 
      "Пользовательская"
   ), 
   col_expand_2 = Table.ExpandRecordColumn(
      col_expand_1, 
      "Пользовательская", 
      {"contentDetails"}, 
      {"contentDetails"}
   ), 
   col_expand_3 = Table.ExpandRecordColumn(
      col_expand_2, 
      "contentDetails", 
      {"videoId"}, 
      {"videoId"}
   ), 
   cols_select = Table.SelectColumns(col_expand_3, {"videoId"})
in
   cols_select

Получаем информацию о видео

Получим следующую таблицу:

let
   request = Json.Document(
      Web.Contents(
         "https://www.googleapis.com/youtube/v3/videos", 
         [
            Query = [
               part
                  = "contentDetails,id,liveStreamingDetails,localizations,player,recordingDetails,snippet,statistics,status,topicDetails", 
               id = video_id_example, 
               key = Api_Key
            ]
         ]
      )
   ), 
   get_data = request[items], 
   items1 = get_data{0}, 
   tab_to_table = Record.ToTable(items1), 
   rows_select = Table.SelectRows(
      tab_to_table, 
      each (
         [Name]
            = "contentDetails" or [Name]
            = "snippet" or [Name]
            = "statistics"
      )
   ), 
   cols_select = Table.SelectColumns(rows_select, {"Value"}), 
   tab_transpose = Table.Transpose(cols_select), 
   col_expand = Table.ExpandRecordColumn(
      tab_transpose, 
      "Column1", 
      {"publishedAt", "title", "description"}, 
      {"video_date", "video_title", "video_description"}
   ), 
   col_expand_2 = Table.ExpandRecordColumn(
      col_expand, 
      "Column3", 
      {"viewCount", "likeCount", "dislikeCount", "commentCount"}, 
      {"video_views", "video_likes", "video_dislikes", "video_comments"}
   ), 
   col_expand_3 = Table.ExpandRecordColumn(
      col_expand_2, 
      "Column2", 
      {"duration"}, 
      {"duration"}
   )
in
    col_expand_3

Получаем комментарии

Получим следующую таблицу для конкретного видео:

let
   request = Json.Document(
      Web.Contents(
         "https://www.googleapis.com/youtube/v3/commentThreads", 
         [
            Query = [
               part = "id,replies,snippet", 
               //channelId = channel_id,
               videoId = video_id_example, 
               key = Api_Key, 
               maxResults = "100"
            ]
         ]
      )
   ), 
   items = Table.FromList(
      request[items], 
      Splitter.SplitByNothing(), 
      {"Comments Thread"}
   ), 
   tab_add_col_1 = Table.AddColumn(
      items, 
      "Пользовательский", 
      each [Comments Thread][snippet][topLevelComment]
   ), 
   col_expand_1 = Table.ExpandRecordColumn(
      tab_add_col_1, 
      "Пользовательский", 
      {"snippet"}, 
      {"snippet"}
   ), 
   col_expand_2 = Table.ExpandRecordColumn(
      col_expand_1, 
      "snippet", 
      {"textOriginal", "authorDisplayName", "publishedAt"}, 
      {"main_comment", "author_name", "main_comment_date"}
   ), 
   col_transform = Table.TransformColumns(
      col_expand_2, 
      {{"main_comment_date", each Text.Start(_, 10), type text}}
   ), 
   tab_add_col_2 = Table.AddColumn(
      col_transform, 
      "Main Comment", 
      each [main_comment_date]
         & "#(lf)"
         & [author_name]
         & "#(lf)"
         & [main_comment]
   ), 
   tab_add_col_3 = Table.AddColumn(
      tab_add_col_2, 
      "Пользовательский", 
      each try [Comments Thread][replies] otherwise null
   ), 
   col_expand_3 = Table.ExpandRecordColumn(
      tab_add_col_3, 
      "Пользовательский", 
      {"comments"}, 
      {"comments"}
   ), 
   col_expand_4 = Table.ExpandListColumn(col_expand_3, "comments"), 
   col_expand_5 = Table.ExpandRecordColumn(
      col_expand_4, 
      "comments", 
      {"snippet"}, 
      {"snippet"}
   ), 
   cols_expand_6 = Table.ExpandRecordColumn(
      col_expand_5, 
      "snippet", 
      {"textOriginal", "authorDisplayName", "publishedAt"}, 
      {"reply_comment", "reply_author_name", "reply_comment_date"}
   ), 
   col_transform_2 = Table.TransformColumns(
      cols_expand_6, 
      {{"reply_comment_date", each Text.Start(_, 10), type text}}
   ), 
   tab_add_col_4 = Table.AddColumn(col_transform_2, "Reply", each [reply_comment_date]
         & "#(lf)"
         & [reply_author_name]
         & "#(lf)"
         & [reply_comment]), 
   cols_select = Table.SelectColumns(
      tab_add_col_4, 
      {"main_comment_date", "Main Comment", "reply_comment_date", "Reply"}
   ), 
   types = Table.TransformColumnTypes(
      cols_select, 
      {{"main_comment_date", type date}, {"reply_comment_date", type date}}
   ), 
   tab_sort = Table.Sort(
      types, 
      {
         {"main_comment_date", Order.Descending}, 
         {"reply_comment_date", Order.Ascending}
      }
   )
in
   tab_sort