Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
recursing through the hubspot deals api
let
iterations = 10, // Number of iterations
url =
"https://api.hubapi.com/deals/v1/deal/paged?" & "hapikey=demo" & "&includeAssociations=true" & "&limit=1" & "&properties=dealname" & "&propertiesWithHistory=true"
,
FnGetOnePage =
(url) as record =>
let
Source = Json.Document(Web.Contents(url)),
data = try Source[deals] otherwise null,
next = try url & "&offset=" & Number.ToText(Source[offset]) otherwise null,
res = [Data=data, Next=next]
in
res
,
GeneratedList =
List.Generate(
()=>[i=0, res = FnGetOnePage(url)],
each [i]<iterations and [res][Next]<>null,
each [i=[i]+1, res = FnGetOnePage([res][Next])],
each [res][Data])
,
#"Converted to Table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
,
#"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1")
,
#"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"portalId", "dealId", "isDeleted", "associations", "properties", "imports", "stateChanges"})
in
#"Expanded Column2"
@stretcharm

This comment has been minimized.

Copy link

@stretcharm stretcharm commented Nov 2, 2018

Try this you seem to be adding the &offset multple times

`let
iterations = 10, // Number of iterations
url =
"https://api.hubapi.com/deals/v1/deal/paged?" & "hapikey=demo" & "&includeAssociations=true" & "&limit=1" & "&properties=dealname" & "&propertiesWithHistory=true"
,
FnGetOnePage =
(url, offset) as record =>
let
nexturl = if offset is null then url else url & "&offset=" & Number.ToText(offset),
Source = Json.Document(Web.Contents(nexturl)),
data = try Source[deals] otherwise null,
res = [Data=data, Next=Source[offset],nexturl=nexturl]
in
res
,
GeneratedList =
List.Generate(
()=>[i=0, res = FnGetOnePage(url,null)],
each [i]<iterations and [res][Next]<>null,
each [i=[i]+1, res = FnGetOnePage(url, [res][Next])],
each [res][Data])
,
#"Converted to Table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
,
#"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1")
,
#"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"portalId", "dealId", "isDeleted", "associations", "properties", "imports", "stateChanges"})

in
#"Expanded Column2"`

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment