3 Replies Latest reply on Aug 3, 2017 10:46 PM by Sanford Whiteman

    REST API to get multiple lead by list id

    Suresh Ramu

      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?

        • Re: REST API to get multiple lead by list id
          Sanford Whiteman
          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?

            • Re: REST API to get multiple lead by list id
              Suresh Ramu

              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