Combining/enriching Hubspot API data

💬 Queries and Resources
1

I'm trying to combine data from two Hubspot API calls and show the combined dataset in the UI in a table.

The first API call is to Hubspot's 'deals' endpoint and returns data about a specific deal -- including the 'line items' that are associated with the deal

{
  "id": "123456789",
  "properties": {
    "amount": "200000",
    "closedate": "2024-04-04T14:39:31.927Z",
    "createdate": "2024-02-19T15:39:32.414Z",
    "deal_currency_code": "GBP",
    "dealname": "Master Service Agreement",
    "dealstage": "13166030",
    "hs_lastmodifieddate": "2024-06-19T15:32:00.416Z",
    "hs_object_id": "123456789",
    "hubspot_owner_id": "16553728",
    "net_terms": "30",
    "pipeline": "13166025",
    "renewal_factor": "10"
  },
  "createdAt": "2024-02-19T15:39:32.414Z",
  "updatedAt": "2024-06-19T15:32:00.416Z",
  "archived": false,
  "associations": {
    "companies": {
      "results": [
        {
          "id": "20071824689",
          "type": "deal_to_company"
        },
        {
          "id": "20071824689",
          "type": "deal_to_company_unlabeled"
        }
      ]
    },
    "line items": {
      "results": [
        {
          "id": "9558991966",
          "type": "deal_to_line_item"
        },
        {
          "id": "9558991967",
          "type": "deal_to_line_item"
        },
        {
          "id": "9558991968",
          "type": "deal_to_line_item"
        },
        {
          "id": "9558991969",
          "type": "deal_to_line_item"
        },
        {
          "id": "9558991970",
          "type": "deal_to_line_item"
        },
        {
          "id": "9558991971",
          "type": "deal_to_line_item"
        },
        {
          "id": "9558991972",
          "type": "deal_to_line_item"
        },
        {
          "id": "9570796573",
          "type": "deal_to_line_item"
        },
        {
          "id": "9570800800",
          "type": "deal_to_line_item"
        },
        {
          "id": "9570800801",
          "type": "deal_to_line_item"
        }
      ]
    },
    "contacts": {
      "results": [
        {
          "id": "17873686199",
          "type": "deal_to_contact"
        },
        {
          "id": "17873686199",
          "type": "legal_signatory"
        }
      ]
    }
  }
}

The second API call is to Hubspot's 'line items' endpoint and returns data about a specific line item,

{
  "id": "9558991966",
  "properties": {
    "amount": "15000.00",
    "createdate": "2024-06-03T13:45:52.085Z",
    "hs_lastmodifieddate": "2024-06-05T11:58:49.073Z",
    "hs_object_id": "9558991966",
    "hs_product_id": "1409599372",
    "name": "12 Month Subscription",
    "quantity": "1",
    "recurringbillingfrequency": "annually"
  },
  "createdAt": "2024-06-03T13:45:52.085Z",
  "updatedAt": "2024-06-05T11:58:49.073Z",
  "archived": false
}

I have the line item data returned from the deals endpoint in a table,

image
image2174×534 29.6 KB

But what i'm aiming for us to call the line items endpoint for each table row in order to get the data to achieve something like this,

image
image2174×534 34.9 KB

I've spent all day yesterday trying to figure this out myself and got lost in the world of javascript queries, for loops, promises, maps, async etc so any help on this would be greatly appreciated.

Thanks!

2

Hi @closedplum,

What's your end goal with this? Do you want to have a table of deals, and then when you click on it, you populate a second table with line item details?

If that's the case, I'd probably approach it a little differently (though there are many ways to handle it!)

Using the REST API for a list of deals makes sense if all the properties you care about are directly on the deal. If they are in associated records, it does become a bit of a chore to pull them all together. So, if you're looking for a table of Deals, and then a table of Line Items when you select a Deal, I'd look into leveraging HubSpots GraphQL API to grab the details of the line items in a single call. With GraphQL you can query for the deal by ID, then pull out all the associated Line Items and the fields you want without multiple calls.

This query is an example of searching for a deal by ID and getting all the line items name and amount properties.

  CRM {
    deal(uniqueIdentifier: "hs_object_id", uniqueIdentifierValue: "yourDealId") {
      dealname
      associations {
        line_item_collection__primary {
          items {
            name
            amount
          }
        }
      }
    }
  }

The data is returned like this:

"data": {
    "CRM": {
      "deal": {
        "dealname": "The Deal name",
        "associations": {
          "line_item_collection__primary": {
            "items": [
              {
                "name": "AAA Fees",
                "amount": "5000.000"
              },
              {
                "name": "YYY Fees",
                "amount": "3120.000"
              },
              {
                "name": "BBB Fees",
                "amount": "100.000"
              },
              {
                "name": "MMM Fees",
                "amount": "1500.000"
              }
            ]
          }
        }
      }
    }
  }

So if your graphQL query triggers when something is selected on the deal table and uses the dealId as a variable, you could reference graphQLQuery.data.CRM.deal.line_item_collection_primary.item as the data source, and it would populate your table with name and amount columns and their respective data.

1 Like
3

Hi @MikeCB -- thanks for the GraphQL suggestion (first time i've heard of it) but unfortunately access to it is only available on the top HubSpot enterprise plans. :frowning_face

4

Hi @closedplum,

I didn't realize the GraphQL API was limited access, sorry about that!

Then I guess you will be stuck in a world of javascript, loops and promises! It's not that bad though. This is generally what you'd need to do.

Assuming you have access to the full data in your example above, you'll need a new HubSpot API Query fetchLineItem (looks like you already have it because you've shown data from a single line item in your sample data).
Set it up to look like this:
image

Then you'll need a JS query to get the full list of line item IDs and run them all through that HubSpot query.

const dealData = // whatever the source of your dealData sample is

// This loops over the line items and just returns an array of IDs
const lineItemIdArray = dealData.associations.line_items.results.map(li => li.id)
// lineItemIdArray = ["9558991966",  "9558991967" ...]

// Now you can call your fetchLineItem function and pass it the lineItemId
// Do this in an array of promises so they all run at the same time instead of sequentially
const liPromises = lineItemIdArray.map(id => {
  // This will call fetchLineItem with each ID
  return   fetchLineItem.trigger( {additionalScope: { lineItemId: id } } )
})

// Now they are all executing, need to wait for them all to finish
const rawData = await Promise.all(liPromises)

// Because properties is nested in the returned data, you can do this to flatten it all out so everything is available as a column
const finalData = rawData.map(data => {
  let {properties, ...rest} = data
  return {...properties, ...rest}
})

return finalData

// Output should look like
{
  "id": "9558991966",
  "amount": "15000.00",
  "createdate": "2024-06-03T13:45:52.085Z",
  "hs_lastmodifieddate": "2024-06-05T11:58:49.073Z",
  "hs_object_id": "9558991966",
  "hs_product_id": "1409599372",
  "name": "12 Month Subscription",
  "quantity": "1",
  "recurringbillingfrequency": "annually"
  "createdAt": "2024-06-03T13:45:52.085Z",
  "updatedAt": "2024-06-05T11:58:49.073Z",
  "archived": false
}

Now you can set your Line Item table to the value of that JS query and you should be good to go. You'll probably need to adjust the flow above based on your actual user interaction pattern (ie: when to trigger the query), but that's the gist of pulling it together via the REST API.

1 Like
5

if you need a one liner to put inside {{ }} or something i condensed the code from @MikeCB (sorry, its on paper, hopefully my handwriting isnt as bad as it looks to me :sweat_smile:)

20240627_165751
20240627_1657511920×2560 138 KB

ill edit w the translation when i get home just incase

2 Likes
6

Hey @closedplum,

Were you able to move forward with your app?