topic Re: Invoke an external GET API and store the data retrieved from API in HubSpot HubDB in APIs & Integrations

Invoke an external GET API and store the data retrieved from API in HubSpot HubDB

SHarde — Tue, 05 Jul 2022 18:13:17 GMT

Hello, I'm a newbie to HubSpot and need to implement a usecase wherein I need to pull some data from a third party API in HubSpot and store the same in the HubDB in some custom table.

Any leads on how this can be done would be really helpful to start with.

Thank you in advance. 🙂

Re: Invoke an external GET API and store the data retrieved from API in HubSpot HubDB

dennisedson — Mon, 11 Jul 2022 15:00:10 GMT

Hey @SHarde ! Welcome to the world of HubSpot!

I cannot speak to the third party data and how you will retrieve it, but on the HubSpot side, you will need to create a HubDB table and then update the table with your data. The information on the HubDB API is available here

@miljkovicmisa might be able to assist with more if you provide more details about the setup 😀

Re: Invoke an external GET API and store the data retrieved from API in HubSpot HubDB

SHarde — Thu, 14 Jul 2022 17:05:04 GMT

Hi @dennisedson ,

Thank you for your response!

Let me provide you some details on the use case I'm wokring on. Here are steps I need to implement:

Call an external API from HubSpot to import data.
Store the data retrieved from the API in HubSpot database.
Use the stored data to generate reports.

Approach taken for implementation:

Created a Workflow with Custom Code Action
Working on implementing above mentioned steps(#1 and #2) in Custom Code Action

Prerequisite for Workflow-Custom Code Action:

Public API available for importing data in HubSpot
- Test API to import data: https://jsonplaceholder.typicode.com/todos/1
- API response:

{
  "userId": 1,
  "id": 1,
  "title": "delectus aut autem",
  "completed": false
}

Create table(test_table) in HubDb to store the data in following columns:
- id
- userId
- title
- completed

Current status:

I'm able to invoke the test API from Workflow and import data in HubSpot
Implemented a code snippet to store data to HubDB as follows as per endpoints exposed here to add a new row to table: https://developers.hubspot.com/docs/api/cms/hubdb

Endpoint used:

Add a new row to a table

POST -- /cms/v3/hubdb/tables/{tableIdOrName}/rows

Code snippet:

const hubspot = require('@hubspot/api-client');

exports.main = async (event, callback) => {

const hubspotClient = new hubspot.Client({
apiKey: process.env.HAPIKEY
});

const values = {
"id": "11",
"userId": "14",
"title": "Test new row",
"completed": "false",
};

const HubDbTableRowV3Request = { path: "test_path", name: "test_title", values };
const tableIdOrName = "test_table";

try {
const apiResponse = await hubspotClient.cms.hubdb.rows.rowsApi.createTableRow(tableIdOrName, HubDbTableRowV3Request);
console.log(JSON.stringify(apiResponse.body, null, 2));
} catch (e) {
e.message === 'HTTP request failed' ? console.error(JSON.stringify(e.response, null, 2)) : console.error(e)
}
};

Facing following issue while storing the data:

2022-07-13T11:12:03.646Z	ERROR	TypeError: Cannot read property 'rowsApi' of undefined
    at Object.exports.main (/var/task/file.js:20:58)
    at Runtime.exports.hubspot_handler [as handler] (/var/task/hubspotHandler.js:6:21)
    at Runtime.handleOnce (/var/runtime/Runtime.js:66:25)

Please let me know your thoughts on the approach and resolution to the issue I'm facing while storing data to HubDb.

Any leads on this would be helpful to move ahead! 🙂

Thanks & Regards,

Swapnil

Re: Invoke an external GET API and store the data retrieved from API in HubSpot HubDB

SHarde — Thu, 14 Jul 2022 06:51:18 GMT

@miljkovicmisa -- Could you please help with the inline issue?

Re: Invoke an external GET API and store the data retrieved from API in HubSpot HubDB

dennisedson — Thu, 14 Jul 2022 13:26:28 GMT

@Teun , perhaps you have some thoughts 😀

Re: Invoke an external GET API and store the data retrieved from API in HubSpot HubDB

Teun — Thu, 14 Jul 2022 17:19:33 GMT

Hi @SHarde ,

This might be a bug with the HubSpot NodeJS library or a mistake in the docs, cause if I check the example of the docs, your code should be correct.

However, the error indicates that hubspotClient.cms.hubdb.rows is undefined, which is odd, cause this is the same in the docs. But if I check the github, I think it should be: hubspotClient.cms.hubdb.rowsApi.createTableRow(tableIdOrName, HubDbTableRowV3Request);

Re: Invoke an external GET API and store the data retrieved from API in HubSpot HubDB

SHarde — Thu, 14 Jul 2022 17:30:29 GMT

Hi @Teun ,

Thank you for your inputs. I'm able to get past the issue. But I'm facing another issue as follows:

API used: hubspotClient.cms.hubdb.rowsApi.createTableRow(tableIdOrName, HubDbTableRowV3Request);

Issue:

2022-07-14T17:22:27.961Z	ERROR	{
  "statusCode": 401,
  "body": {
    "status": "error",
    "message": "Authentication credentials not found. This API supports both API Key and OAuth 2.0 authentication and you can find more details at https://developers.hubspot.com/docs/methods/auth/oauth-overview",
    "correlationId": "ac67f190-a273-49dd-8839-df0d28fd7554",
    "category": "INVALID_AUTHENTICATION"
  },
  "headers": {
    "date": "Thu, 14 Jul 2022 17:22:27 GMT",
    "content-type": "application/json;charset=utf-8",
    "content-length": "316",
    "connection": "close",
    "cf-ray": "72abf0cc080559fd-IAD",
    "strict-transport-security": "max-age=31536000; includeSubDomains; preload",
    "vary": "Accept-Encoding",
    "cf-cache-status": "DYNAMIC",
    "access-control-allow-credentials": "true",
    "access-control-allow-headers": "Origin, X-Requested-With, Content-Type, Accept, Accept-Charset, Accept-Encoding",
    "access-control-allow-methods": "GET",
    "expect-ct": "max-age=604800, report-uri=\"https://report-uri.cloudflare.com/cdn-cgi/beacon/expect-ct\"",
    "x-hubspot-correlation-id": "ac67f190-a273-49dd-8839-df0d28fd7554",
    "x-trace": "2B05231134A56B035C6EA68FDFF584F7E366CB8AC0000000000000000000",
    "report-to": "{\"endpoints\":[{\"url\":\"https:\\/\\/a.nel.cloudflare.com\\/report\\/v3?s=bjYHRlpWhmWyODzqB9%2FIKXzvdDqlKOmjeqG0m1bowFsfpjf8CH9oeV4hukYn6sxpKdOJpk3qN1cz1xiQvVF4ZX%2F30Ryj59sRbPsSXB5IVAnif43xLJ8aYPoiCVd%2BF3mR\"}],\"group\":\"cf-nel\",\"max_age\":604800}",
    "nel": "{\"success_fraction\":0.01,\"report_to\":\"cf-nel\",\"max_age\":604800}",
    "server": "cloudflare",
    "alt-svc": "h3=\":443\"; ma=86400, h3-29=\":443\"; ma=86400"
  },
  "request": {
    "uri": {
      "protocol": "https:",
      "slashes": true,
      "auth": null,
      "host": "api.hubapi.com",
      "port": 443,
      "hostname": "api.hubapi.com",
      "hash": null,
      "search": null,
      "query": null,
      "pathname": "/cms/v3/hubdb/tables/test_table/rows",
      "path": "/cms/v3/hubdb/tables/test_table/rows",
      "href": "https://api.hubapi.com/cms/v3/hubdb/tables/test_table/rows"
    },
    "method": "POST",
    "headers": {
      "User-Agent": "hubspot-api-client-nodejs; 3.4.1",
      "Accept": "application/json",
      "content-type": "application/json",
      "content-length": 118
    }
  }
}

Please let me know if I'm missing on something or how can I provide the required authentication credentials here.

Re: Invoke an external GET API and store the data retrieved from API in HubSpot HubDB

dennisedson — Thu, 14 Jul 2022 17:30:07 GMT

@Teun , looks like you might be on to something there 👀

I also found this issue indicating the same. Will work to get this corrected.

Re: Invoke an external GET API and store the data retrieved from API in HubSpot HubDB

Teun — Thu, 14 Jul 2022 18:05:34 GMT

I made this mistake many many times.

Your custom coded action is probably missing the secret. Is HAPIKEY set as a secret in your coded action?

P.s. I would advise moving to private apps instead as API keys will be sunset in November this year.

Re: Invoke an external GET API and store the data retrieved from API in HubSpot HubDB

SHarde — Thu, 14 Jul 2022 18:12:19 GMT

Hi @Teun ,

Adding the HAPIKEY to the Secret worked perfectly for me. I'm able to POST the data to HubDB.

For the suggesstion on creating apps, could you please guide me on how it will help me implementing the usecase that I'm working on.

Thanks,

Swapnil

Re: Invoke an external GET API and store the data retrieved from API in HubSpot HubDB

Teun — Thu, 14 Jul 2022 18:40:02 GMT

Hi @SHarde ,

Awesome. This doc describes exactly how to migrate to a private app. Make sure to update your code with this line:

const hubspotClient = new hubspot.Client({ accessToken: YOUR_ACCESS_TOKEN });

Re: Invoke an external GET API and store the data retrieved from API in HubSpot HubDB

SHarde — Thu, 14 Jul 2022 19:02:44 GMT

Hi @Teun ,

I was able to locate the documentation of migrating to private app and tried out the approach suggested in there. I was able to get the Custom Code action working with the Access token instead of API KEY.

Pre-requisite: Create a private app with your deveopers HubSpot account

1. Go to Settings --> Integrations --> Private Apps

2. Create Private App

3. Provide the name to the App

4. Add the modules (for ex., hubdb, contacts, deals, etc.) as required

5. Create app

6. You should be able to find your access token in the app which you can add as Secret in Custom Code Action instead of API KEY

Here is the final code snippet which could help someone else as well:

const hubspot = require('@hubspot/api-client');

exports.main = async (event, callback) => {

const hubspotClient = new hubspot.Client({
accessToken: process.env.ACCESSTOKEN
});

// Make sure ACCESSTOKEN is added in SecretKey

const values = {

"id":"3",
"userid": "16",
"title": "Test new row",
"completed": "true",
};

const HubDbTableRowV3Request = { path: "test_path11", name: "test_title11", values };
const tableIdOrName = "test_table";

try {
const apiResponse = await hubspotClient.cms.hubdb.rowsApi.createTableRow(tableIdOrName, HubDbTableRowV3Request);
console.log(JSON.stringify(apiResponse.body, null, 2));
} catch (e) {
e.message === 'HTTP request failed' ? console.error(JSON.stringify(e.response, null, 2)) : console.error(e)
}
};

Thank you so much on working this with me and taking this to resolution. I really appreciate it. 🙂

Have a great day ahead.

Best Regards,
Swapnil

Re: Invoke an external GET API and store the data retrieved from API in HubSpot HubDB

GaryMedina — Mon, 30 Jan 2023 01:05:24 GMT

Huge help, Thank you!