Re: REST API to get multiple lead by list id

Anonymous
Not applicable

REST API to get multiple lead by list id

I am trying to get multiple leads by list id through Excel VBA.

Get access token by running

https://xxx-xxx-xxx.mktorest.com/identity/oauth/token?grant_type=client_credentials

and run

https://xxx-xxx-xxx.mktorest.com/rest/v1/list/{{listId}}/leads.json?access_token={{access token retrieved from previous step}}&nextPageToken={{next_page_token}}

and re-run above until next_page_token is empty.

When tested in a list of about 500 leads, it retrieves all data from a list.

But when tested in a list containing about 13000 leads, it does not retrieve full list, always about 30 to 40 less.

Anyone had similar experience?

3 REPLIES 3
SanfordWhiteman
Level 10 - Community Moderator

Re: REST API to get multiple lead by list id

When tested in a list of about 500 leads, it retrieves all data from a list.

But when tested in a list containing about 13000 leads, it does not retrieve full list, always about 30 to 40 less.

What differentiates those 30 or 40?

Also, are you also stopping on an empty page?

Anonymous
Not applicable

Re: REST API to get multiple lead by list id

For a list of 172, it retrieves 172

For a list of 401, it retrieves 400

For a list of 658, it retrieves 656

For a list of 660, it retrieves 658

For a list of 10912, it retrieves 10876

For a list of 13705, it retrieves 13660

Here's the loop code

Do Until IsEmpty(Range("H1").Value)

getLeadfromList = "https://xxx-xxx-xxx.mktorest.com/rest/v1/list/{{listId}}/leads.json?access_token=" & accessToken & "&fields=" & Sheets("Sheet1").Range("A13").Value & "&nextPageToken={{next_page_token}}

With xmlhttp

    .Open "GET", getLeadfromList, False, user, Password

    .Send

    Set JSON = ParseJson(xmlhttp.responseText)

End With

i = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row

For Each Item In JSON("result")

Range("B" & i).Value = Item("id")

Range("C" & i).Value = Item("firstName")

Range("D" & i).Value = Item("lastName")

Range("E" & i).Value = Item("email")

Range("F" & i).Value = Item("phone")

Range("G" & i).Value = Item("updatedAt")

i = i + 1

Next

Range("H1").Value = JSON("nextPageToken")

Loop

SanfordWhiteman
Level 10 - Community Moderator

Re: REST API to get multiple lead by list id

I would hone in on the 400 vs. 401 and check the outlier.

If you add that outlier to their own list (where they're the only member) are they retrievable?

Can't say I've seen such an issue.