Forum Discussion
tommy_8
5 years agoRegular Visitor
Paginate API (Facebook Graph API) call
Hi API experts, I need some hints regarding paginating through my API Call. I'm retrieving data from Facebook Graph API, which gives me the first 25 rows and the "next page" as another column. ...
- 5 years ago
I've not been successful using List.Accumulate().
Instead List.Generate() gives me the list of pages, which can be converted to Table.
For any interested user:
let API_Bearer = "Bearer XXX", Source = List.Generate( ()=> [ // API Config and paramters facebook_account = "act_XXX", Facebook_API_URL = "https://graph.facebook.com/v11.0/" & facebook_account & "/insights?", level = "level=ad&", fields = "fields=campaign_name,ad_name,impressions,inline_link_clicks,clicks&", time_increment = "time_increment=1&", date_preset = "date_preset=last_30d&", URL = Facebook_API_URL & level & fields & time_increment & date_preset, Result = Json.Document(Web.Contents(URL, [Headers=[Authorization=API_Bearer]]), 65001) ], each [URL]<>null, each [ URL = try [Result][paging][next] otherwise null, Result = Json.Document(Web.Contents(URL, [Headers=[Authorization=API_Bearer]]), 65001) ] ), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Result"}, {"Column1.Result"}), #"Expanded Column1.Result" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.Result", {"data"}, {"Column1.Result.data"}), #"Expanded Column1.Result.data" = Table.ExpandListColumn(#"Expanded Column1.Result", "Column1.Result.data"), #"Expanded Column1.Result.data1" = Table.ExpandRecordColumn(#"Expanded Column1.Result.data", "Column1.Result.data", {"campaign_name", "ad_name", "impressions", "inline_link_clicks", "clicks", "date_start"}, {"campaign_name", "ad_name", "impressions", "inline_link_clicks", "clicks", "date"}), #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1.Result.data1",{{"campaign_name", type text}, {"ad_name", type text}, {"impressions", Int64.Type}, {"inline_link_clicks", Int64.Type}, {"clicks", Int64.Type}, {"date", type date}}) in #"Changed Type"Best regards, Tommy
tommy_8
5 years agoRegular Visitor
I've not been successful using List.Accumulate().
Instead List.Generate() gives me the list of pages, which can be converted to Table.
For any interested user:
let
API_Bearer = "Bearer XXX",
Source = List.Generate( ()=>
[
// API Config and paramters
facebook_account = "act_XXX",
Facebook_API_URL = "https://graph.facebook.com/v11.0/" & facebook_account & "/insights?",
level = "level=ad&",
fields = "fields=campaign_name,ad_name,impressions,inline_link_clicks,clicks&",
time_increment = "time_increment=1&",
date_preset = "date_preset=last_30d&",
URL = Facebook_API_URL & level & fields & time_increment & date_preset,
Result = Json.Document(Web.Contents(URL, [Headers=[Authorization=API_Bearer]]), 65001)
],
each [URL]<>null,
each [
URL = try [Result][paging][next] otherwise null,
Result = Json.Document(Web.Contents(URL, [Headers=[Authorization=API_Bearer]]), 65001)
]
),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Result"}, {"Column1.Result"}),
#"Expanded Column1.Result" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.Result", {"data"}, {"Column1.Result.data"}),
#"Expanded Column1.Result.data" = Table.ExpandListColumn(#"Expanded Column1.Result", "Column1.Result.data"),
#"Expanded Column1.Result.data1" = Table.ExpandRecordColumn(#"Expanded Column1.Result.data", "Column1.Result.data", {"campaign_name", "ad_name", "impressions", "inline_link_clicks", "clicks", "date_start"}, {"campaign_name", "ad_name", "impressions", "inline_link_clicks", "clicks", "date"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1.Result.data1",{{"campaign_name", type text}, {"ad_name", type text}, {"impressions", Int64.Type}, {"inline_link_clicks", Int64.Type}, {"clicks", Int64.Type}, {"date", type date}})
in
#"Changed Type"Best regards, Tommy
- ajperezmorales4 years agoNew Member
What does API_bearer stand for?
- lbendlin4 years agoSuper User
See the top of the code. A text string with the word Bearer, a space, and the token you received during authentication.