Forum Discussion
Getting data from web where next page token generates uniquely every time Eg Youtube api
URL: https://www.googleapis.com/youtube/v3/playlistItems?part=snippet&maxResults=50&playlistId=myPlaylistId&key=myApiKey&pageToken=UniqueTokenGenerated
this page token generated will be used in url for next 50 items
Recursion need to be done,
I am sure function List.Generate will be used, but not able to use and I don't know where I am going wrong.
I need a fresh code to get this result. And if there is some other way to get all the records , there are more then 9000 records to it will iterate for 180 times
Seeking help
Problem solved
Created a table on sheet1 with name youtube
Youtube String Playlist PLojk56IbG34w6q67wt_JQjgITJPdwtto8 Key [mykey] Url https://www.googleapis.com/youtube/v3/playlistItems?part=snippet&maxResults=50&playlistId=PLojk56IbG34w6q67wt_JQjgITJPdwtto8&key=[mykey]&pageToken= and I wrote the code as follows, if anyone wants help , i can explain
let
Source = Excel.CurrentWorkbook(){[Name="YouTube"]}[Content],
url = Source{2}[String],FnGetOnePage =
(url) as record =>
let
Source = Json.Document(Web.Contents(url)),
nextPageToken = try Source[nextPageToken] otherwise null,
items = try Source[items] otherwise null,
Res = [Data = items , Token = nextPageToken]
in
Res,TotalCount = Json.Document(Web.Contents(url)),
pageInfo = TotalCount[pageInfo],
totalResults = pageInfo[totalResults],
iterations = Number.RoundUp(Value.Divide(totalResults,50)),GeneratedList =
List.Generate(
()=>[i=0, res = FnGetOnePage(url)],
each [i]<iterations,
each [i=[i]+1, res = FnGetOnePage(url&[res][Token])],
each [res][Data]),ToTable = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Expand = Table.ExpandListColumn(ToTable, "Column1"),
Snippet = Table.ExpandRecordColumn(Expand, "Column1", {"snippet"}, {"snippet"}),
GenList = Table.ExpandRecordColumn(Snippet, "snippet", {"publishedAt", "title", "description", "position", "resourceId"}, {"publishedAt", "title", "description", "position", "resourceId"}),
VidID = Table.ExpandRecordColumn(GenList, "resourceId", {"videoId"}, {"videoId"})
in
VidID
1 Reply
Problem solved
Created a table on sheet1 with name youtube
Youtube String Playlist PLojk56IbG34w6q67wt_JQjgITJPdwtto8 Key [mykey] Url https://www.googleapis.com/youtube/v3/playlistItems?part=snippet&maxResults=50&playlistId=PLojk56IbG34w6q67wt_JQjgITJPdwtto8&key=[mykey]&pageToken= and I wrote the code as follows, if anyone wants help , i can explain
let
Source = Excel.CurrentWorkbook(){[Name="YouTube"]}[Content],
url = Source{2}[String],FnGetOnePage =
(url) as record =>
let
Source = Json.Document(Web.Contents(url)),
nextPageToken = try Source[nextPageToken] otherwise null,
items = try Source[items] otherwise null,
Res = [Data = items , Token = nextPageToken]
in
Res,TotalCount = Json.Document(Web.Contents(url)),
pageInfo = TotalCount[pageInfo],
totalResults = pageInfo[totalResults],
iterations = Number.RoundUp(Value.Divide(totalResults,50)),GeneratedList =
List.Generate(
()=>[i=0, res = FnGetOnePage(url)],
each [i]<iterations,
each [i=[i]+1, res = FnGetOnePage(url&[res][Token])],
each [res][Data]),ToTable = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Expand = Table.ExpandListColumn(ToTable, "Column1"),
Snippet = Table.ExpandRecordColumn(Expand, "Column1", {"snippet"}, {"snippet"}),
GenList = Table.ExpandRecordColumn(Snippet, "snippet", {"publishedAt", "title", "description", "position", "resourceId"}, {"publishedAt", "title", "description", "position", "resourceId"}),
VidID = Table.ExpandRecordColumn(GenList, "resourceId", {"videoId"}, {"videoId"})
in
VidID