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

[SHarde]

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. 🙂

[Teun · Accepted solution]

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 });

[Teun]

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);

[dennisedson]

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

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

[SHarde]

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.

[Teun]

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.

[SHarde]

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

[Teun · Accepted solution]

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 });

[SHarde · Accepted solution]

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

[SHarde]

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

[dennisedson]

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 😀

[SHarde]

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:

1. Call an external API from HubSpot to import data.
2. Store the data retrieved from the API in HubSpot database.
3. 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

[dennisedson]

@Teun , perhaps you have some thoughts 😀