Offset pagination not working for HubSpot API

[NeelamMacwan]

Hi all,

this is a bit of urgent request.

I'm trying to integrate HubSpot Contact API in Matillion, I can sample the API query component, but the pagination is surely not successful.

I get records only from first page. I have tried almost everything in rsd file, not sure to to increment the page number here.

Can anyone help with this please?

This is the code:

<api:script xmlns:api="http://apiscript.com/ns?v1" xmlns:xs="http://www.w3.org/2001/XMLSchema">

<!-- See Column Definitions to specify column behavior and use XPaths to extract column values from JSON. -->

<api:info title="a" desc="Generated schema file." xmlns:other="http://apiscript.com/ns?v1">

<!-- You can modify the name, type, and column size here. -->

<attr name="archived" xs:type="boolean" readonly="false" other:xPath="archived" />

<attr name="createdAt" xs:type="datetime" readonly="false" other:xPath="createdAt" />

<attr name="id" xs:type="string" readonly="false" other:xPath="id" />

<attr name="properties_createdate" xs:type="datetime" readonly="false" other:xPath="properties/createdate" />

<attr name="properties_email" xs:type="string" readonly="false" other:xPath="properties/email" />

<attr name="properties_firstname" xs:type="string" readonly="false" other:xPath="properties/firstname" />

<attr name="properties_hs_object_id" xs:type="string" readonly="false" other:xPath="properties/hs_object_id" />

<attr name="properties_lastmodifieddate" xs:type="datetime" readonly="false" other:xPath="properties/lastmodifieddate" />

<attr name="properties_lastname" xs:type="string" readonly="false" other:xPath="properties/lastname" />

<attr name="updatedAt" xs:type="datetime" readonly="false" other:xPath="updatedAt" />

<input name="rows@next" desc="Identifier for the next page of results. Do not set this value manually." />

<input name="totalCount" desc="Records total count" />

</api:info>

<api:set attr="BackwardsCompatibilityMode" value="true" />

<api:set attr="EnablePaging" value="TRUE" />

<api:set attr="elementmappath#" value="/paging/next" />

<api:set attr="elementmapname#" value="totalCount" />

<api:set attr="limit" value="100" />

<api:check attr="_input.rows@next">

<api:set attr="uri" value="https://api.hubapi.com/crm/v3/objects/contacts/search?offset=[_input.rows@next]&amp;limit=[limit]" /> -- I replaced limit with 10, but did not work

<api:else>

<api:set attr="_input.rows@next" value="0" />

<api:set attr="uri" value="https://api.hubapi.com/crm/v3/objects/contacts/search?offset=[_input.rows@next]&amp;limit=[limit]" /> -- I replaced limit with 10, but did not work

</api:else>

</api:check>

<!-- Column XPaths are relative to a RepeatElement that splits the JSON into rows. -->

<api:set attr="RepeatElement" value="/results" />

<!-- Uri parameters set up -->

<api:set attr="paramname#1" value="propertyName" />

<api:set attr="paramvalue#1" value="[_connection.propertyName]" />

<api:set attr="paramname#2" value="operator" />

<api:set attr="paramvalue#2" value="[_connection.operator]" />

<api:set attr="paramname#3" value="value" />

<api:set attr="paramvalue#3" value="[_connection.value]" />

<!-- The GET method corresponds to SELECT. Here you can override the default processing of the SELECT statement. The results of processing are pushed to the schema's output. See SELECT Execution for more information. -->

<api:script method="GET">

<api:set attr="method" value="POST"/> <!-- HTTP request type -->

<api:set attr="data">

{

"filters": \[

{

"propertyName": "",

"operator": "EQ",

"value": true

}

\]

}

</api:set>

<api:set attr="ContentType" value="application/json" />

<api:set attr="nextOffset" value="[_input.rows@next | add([limit])]" /> -- I replaced limit with 10, but did not work

<api:check attr="totalCount">

<api:check value="[totalCount | isgreater([nextOffset])]">

<api:set attr="rows@next" value="[nextOffset]" />

</api:check>

</api:check>

<api:call op="jsonproviderGet">

<api:push/>

</api:call>

</api:script>

</api:script>

[Joe-Community-Manager]

Hi @NeelamMacwan​

I spoke with a member of our team about this and I was informed that the endpoint you are trying to page doesn’t use offset paging, it uses cursor-based paging.

You will need to use limit and paging.next.after to page through the results.

I hope that helps, please do let me know how you get on.

Kind regards, Joe

[NeelamMacwan]

Hi @JoeCommunityManager​ ,

Thank you for checking the question.

I have tried using cursor-based pagination as I realised later that's the correct way for this api. However, the api call somehow reads only first page, does not move to next one.

This is the pagination details:

This is rsd file:

<api:script xmlns:api=“http://apiscript.com/ns?v1” xmlns:xs=“http://www.w3.org/2001/XMLSchema”>

<!-- See Column Definitions to specify column behavior and use XPaths to extract column values from JSON. –>

<api:info title=“a” desc=“Generated schema file.” xmlns:other=“http://apiscript.com/ns?v1”>

<!-- You can modify the name, type, and column size here. –>

<attr name=“archived” xs:type=“boolean” readonly=“false” other:xPath=“archived” />

<attr name=“createdAt” xs:type=“datetime” readonly=“false” other:xPath=“createdAt” />

<attr name=“id” xs:type=“string” readonly=“false” other:xPath=“id” />

<attr name=“properties_createdate” xs:type=“datetime” readonly=“false” other:xPath=“properties/createdate” />

<attr name=“properties_email” xs:type=“string” readonly=“false” other:xPath=“properties/email” />

<attr name=“properties_firstname” xs:type=“string” readonly=“false” other:xPath=“properties/firstname” />

<attr name=“properties_hs_object_id” xs:type=“string” readonly=“false” other:xPath=“properties/hs_object_id” />

<attr name=“properties_lastmodifieddate” xs:type=“datetime” readonly=“false” other:xPath=“properties/lastmodifieddate” />

<attr name=“properties_lastname” xs:type=“string” readonly=“false” other:xPath=“properties/lastname” />

<attr name=“updatedAt” xs:type=“datetime” readonly=“false” other:xPath=“updatedAt” />

<input name=“rows@next” desc=“Identifier for the next page of results. Do not set this value manually.” />

</api:info>

<api:set attr=“BackwardsCompatibilityMode” value=“true” />

<api:set attr=“EnablePaging” value=“TRUE” />

<api:set attr=“elementmappath#” value=“/paging/next/after” />

<api:set attr=“elementmapname#” value=“rows@next” />

<api:check attr=“_input.rows@next”>

<api:set attr=“uri” value=“https://api.hubapi.com/crm/v3/objects/contacts/search?limit=10&amp;amp;after=[_input.rows@next]” />

<api:else>

<api:set attr=“uri” value=“https://api.hubapi.com/crm/v3/objects/contacts/search?limit=10” />

</api:else>

</api:check>

<!-- Column XPaths are relative to a RepeatElement that splits the JSON into rows. –>

<api:set attr=“RepeatElement” value=“/results” />

<!-- Uri parameters set up –>

<api:set attr=“paramname#1” value=“propertyName” />

<api:set attr=“paramvalue#1” value=“[_connection.propertyName]” />

<api:set attr=“paramname#2” value=“operator” />

<api:set attr=“paramvalue#2” value=“[_connection.operator]” />

<api:set attr=“paramname#3” value=“value” />

<api:set attr=“paramvalue#3” value=“[_connection.value]” />

<!-- The GET method corresponds to SELECT. Here you can override the default processing of the SELECT statement. The results of processing are pushed to the schema’s output. See SELECT Execution for more information. –>

<api:script method=“GET”>

<api:set attr=“method” value=“POST”/> <!-- HTTP request type –>

<api:set attr=“data”>

{

“filters”: [

{

“propertyName”: “hs_email_optout”,

“operator”: “EQ”,

“value”: true

}

]

}

</api:set>

<api:set attr=“ContentType” value=“application/json” />

<api:call op=“jsonproviderGet”>

<api:push/>

</api:call>

</api:script>

</api:script>

Thank you,

Neelam

[NeelamMacwan]

Hi @JoeCommunityManager​ ,

Thanks for suggestion.

Unfortunately, I cannot use a custom connector as we are not using Productivity cloud yet.

We found a solution to get it working without paging, though with a limitation, but it works for us now.

I added a limit property in the body of API call and we got the result we need.

Like this,

{

"filters": \[

{

"propertyName": "hs_email_optout",

"operator": "EQ",

"value": true

}

\],

"limit": 100

}

Thanks once again for looking into this.

Regards,

Neelam

[Joe-Community-Manager]

Hmm sorry about that @NeelamMacwan​

Can you try creating this with the Custom Connector? Documentation here should resolve that for you.

Please keep me posted.

Kind regards, Joe

[Joe-Community-Manager]

Thank you for sharing your work around @NeelamMacwan​

I have added a really handy blog regarding custom connector for METL also, this should shed some light on that for you also 😀

Kind regards, Joe

[NeelamMacwan]

Thank you, @JoeCommunityManager​