topic Re: Please help with HubSpot API pagination settings in Integration, Extension & APIs

Please help with HubSpot API pagination settings

Anonymous — Tue, 20 Feb 2018 00:20:07 GMT

Hi,

I'm using Qlik Sense' REST API connector to pull data from HubSpot CRM. HubSpot documentation reads:

"Get all of the deals in a portal. Returns a paginated set of deals.

In addition to the list of deals, each request will also return two values, offset and hasMore. If hasMore is true, you'll need to make another request, using the offset to get the next page of deal records." (from Get all deals‌ )

Default records returned is 100.

I need help configuring the 'pagination type' settings in Qlik Sense. Please help.

Re: Please help with HubSpot API pagination settings

Anonymous — Tue, 20 Feb 2018 22:13:26 GMT

To clarify, here is the response I'm getting:

{deals":[{"portalId":1208841,"dealId":247301077,"isDeleted":false,"associations":null,"properties":{"dealname":{"value":"Sample Deal Name 1","timestamp":1516321349923,"source":"IMPORT","sourceId":"DealTasks:DealsImport","versions":[{"name":"dealname","value":"Sample Deal Name 1","timestamp":1516321349923,"sourceId":"DealTasks:DealsImport","source":"IMPORT","sourceVid":[]}]},"hubspot_owner_id":{"value":"5327004","timestamp":1516321349923,"source":"IMPORT","sourceId":"DealTasks:DealsImport","versions":[{"name":"hubspot_owner_id","value":"5327004","timestamp":1516321349923,"sourceId":"DealTasks:DealsImport","source":"IMPORT","sourceVid":[]}]}},"imports":[],"stateChanges":[]}],"hasMore":true,"offset":247301077}

offset returns a timestamp of the last request

hasMore: true indicates there are more records

Which pagination type in Qlik Sense REST API connector should I be using, and how should the parameters be configured?

Thank you.

Re: Please help with HubSpot API pagination settings

ggijben — Mon, 11 Jun 2018 08:55:13 GMT

Did you find the solution? Facing the same problem.

Re: Please help with HubSpot API pagination settings

gamaescalante — Fri, 13 Jul 2018 15:19:39 GMT

I'm looking to connect QlikSense & HubSpot data as well. Any progress made?

Re: Please help with HubSpot API pagination settings

ggijben — Fri, 13 Jul 2018 15:33:58 GMT

Yes, i succesfully managed to load all contacts. What exactly is your question?

Re: Please help with HubSpot API pagination settings

gamaescalante — Fri, 13 Jul 2018 15:36:42 GMT

I am looking to load contacts and timeline/note activity in HubSpot to Qliksense...but not sure what documentation to reference for the setup. Any assistance would be greatly appreciated. Currently we've had success connecting QlikSense with a SQL Server Database.

Re: Please help with HubSpot API pagination settings

neeraj712 — Tue, 11 Sep 2018 03:15:38 GMT

i am trying to pull all Email Events from Hubspot , at a time one page appears with offset and hasMore=true, is there are option through REST API connection to read all pages ?

How did you read all contacts in single API request ?

Re: Please help with HubSpot API pagination settings

neeraj712 — Wed, 19 Sep 2018 16:53:58 GMT

Hi Gerco ,

How did you manage to access all pages to get all contact through API call?

I have to pull all Email Events from Hubspot , at a time one page appears with offset token and hasMore=true, when i use pagination technique "Next token" and setting next token path to 'root/offset'. Data extracts is very slow and pull around 80k rows in 30 minutes.

Let me know steps you have performed to get all contact in singe request.

Re: Please help with HubSpot API pagination settings

aferrari7 — Thu, 20 Sep 2018 14:58:16 GMT

Hi Gerco,

Could you please share with us your code to paginate the .json?

Re: Please help with HubSpot API pagination settings

gamaescalante — Tue, 02 Oct 2018 20:50:09 GMT

Did you have any luck connecting?

Re: Please help with HubSpot API pagination settings

pankaj999 — Tue, 29 Jan 2019 13:51:25 GMT

Could you let us know how you managed to load all contacts in qliksense?

Re: Please help with HubSpot API pagination settings

adandreti — Mon, 22 Apr 2019 12:57:19 GMT

Hi all, I was able to pull all Hubspot deals with some adjustments to the beginning and bottom of the "insert script" sql.

Firstly it is important to note that offset does not return a timestamp of the last request it is more so an access token that needs to be stored and used in the next API call to get the next bunch of records. The process I used to fetch all deals was to set up my rest connector connection without any pagination options selected, from there I used the select data -> insert script options to drop the SQL and Qlik table loads into my data load editor. One thing I noticed was that the select data option wasn't pulling in the hasMore or offset fields so I wrapped the initial sql query with another select statement to grab these fields (see bolded code below for SQL additions). Once I had my offset and hasMore fields I wrapped the sql in a do while loop and created variables such as vOff, vUrl and vHas to change at the end of every API get request looping through each offset until the hasMore field=False. Below is my data load without the list of Qlik load tables.

let vURL='https://api.hubapi.com/deals/v1/deal/paged?hapikey=*****&includeAssociations=true&limit=250&properties=dealstage';
let vOffset='';
let vHas='True';

do while vHas='True';

RestConnectorMasterTable:
SQL SELECT
"hasMore",
"offset",
"__KEY_root",
(SELECT
"portalId",
"dealId",
"isDeleted",
"__KEY_deals",
"__FK_deals",
(SELECT
"__KEY_associations",
"__FK_associations",
(SELECT
"@Value",
"__FK_associatedVids"
FROM "associatedVids" FK "__FK_associatedVids" ArrayValueAlias "@Value"),
(SELECT
"@Value" AS "@Value_u0",
"__FK_associatedCompanyIds"
FROM "associatedCompanyIds" FK "__FK_associatedCompanyIds" ArrayValueAlias "@Value_u0"),
(SELECT
"@Value" AS "@Value_u1",
"__FK_associatedDealIds"
FROM "associatedDealIds" FK "__FK_associatedDealIds" ArrayValueAlias "@Value_u1"),
(SELECT
"@Value" AS "@Value_u2",
"__FK_associatedTicketIds"
FROM "associatedTicketIds" FK "__FK_associatedTicketIds" ArrayValueAlias "@Value_u2")
FROM "associations" PK "__KEY_associations" FK "__FK_associations"),
(SELECT
"__KEY_properties",
"__FK_properties",
(SELECT
"value" AS "value_u0",
"timestamp" AS "timestamp_u0",
"source" AS "source_u0",
"sourceId" AS "sourceId_u0",
"__KEY_dealstage",
"__FK_dealstage",
(SELECT
"name",
"value",
"timestamp",
"sourceId",
"source",
"__KEY_versions",
"__FK_versions",
(SELECT
"@Value" AS "@Value_u3",
"__FK_sourceVid"
FROM "sourceVid" FK "__FK_sourceVid" ArrayValueAlias "@Value_u3")
FROM "versions" PK "__KEY_versions" FK "__FK_versions")
FROM "dealstage" PK "__KEY_dealstage" FK "__FK_dealstage"),
(SELECT
"value" AS "value_u2",
"timestamp" AS "timestamp_u1",
"source" AS "source_u2",
"sourceId" AS "sourceId_u1",
"__KEY_hs_object_id",
"__FK_hs_object_id",
(SELECT
"name" AS "name_u0",
"value" AS "value_u1",
"source" AS "source_u1",
"__KEY_versions_u0",
"__FK_versions_u0",
(SELECT
"@Value" AS "@Value_u4",
"__FK_sourceVid_u0"
FROM "sourceVid" FK "__FK_sourceVid_u0" ArrayValueAlias "@Value_u4")
FROM "versions" PK "__KEY_versions_u0" FK "__FK_versions_u0")
FROM "hs_object_id" PK "__KEY_hs_object_id" FK "__FK_hs_object_id")
FROM "properties" PK "__KEY_properties" FK "__FK_properties"),
(SELECT
"@Value" AS "@Value_u5",
"__FK_imports"
FROM "imports" FK "__FK_imports" ArrayValueAlias "@Value_u5"),
(SELECT
"@Value" AS "@Value_u6",
"__FK_stateChanges"
FROM "stateChanges" FK "__FK_stateChanges" ArrayValueAlias "@Value_u6")
FROM "deals" PK "__KEY_deals" FK "__FK_deals")
From JSON (wrap on) "root" PK "__KEY_root"
WITH CONNECTION (url "$(vURL)&$(vOffset)" );

...

~~~Qlik Load Tables~~~~

...

Let vOff = Peek('offset', -1, 'root'); //store the latest offset value in a variable
let vOffset=''; //reset the vOffset for next batch
let vOffset='&offset=' & '$(vOff)'; //append the latest

Let vHas = Peek('hasMore', -1, 'root');

DROP TABLE RestConnectorMasterTable;
loop;

Re: Please help with HubSpot API pagination settings

fmarvnnt — Mon, 24 Feb 2020 10:34:07 GMT

Would you be so kind to share the solution?

Than you in advance,

Re: Please help with HubSpot API pagination settings

fmarvnnt — Mon, 24 Feb 2020 10:34:57 GMT

I imagin eyou find the solution. Would you be so kind to share us the solution?

Re: Please help with HubSpot API pagination settings

adandreti — Mon, 24 Feb 2020 18:28:25 GMT

Hello FM,

Did you try the proposed solution that I have posted above?

Re: Please help with HubSpot API pagination settings

xy-siang — Tue, 22 Sep 2020 09:15:40 GMT

Hi Lewiszman

Sorry to bother you.

I have never use the Qliksense API connector to pull data from HubSport CRM before. Hence, would mind sharing a detailed guide or any advice on how to setup the Qliksense API connector to pull data from HubSport CRM?

Then another thing is that do I need to know about Hubspot API for pulling data from HubSport CRM to Qliksense?

Appreciate if you can advice on the following above. Thank you.

Regards,

XY Siang