Integrating phone calls, emails, and notes from Hubspot to Netsuite.
Details:
- Initiate an integration with OAuth 2.0: to do the initial authentication and to get the redirect url for creating refresh token
Example Authorization URL:
Sending a user to this URL will ask the user to approve access to contacts and workflows:
If they grant access, the user would be redirected to this URL:
https://www.example.com/?code=xxxx
If there are any problems with the authorization, you’ll get the error parameters instead of the code:
2. Get OAuth 2.0 access and refresh tokens: generate the refresh token first time
POST URL:
https://api.hubapi.com/oauth/v1/token
Headers:
Content-Type: application/x-www-form-urlencoded;charset=utf-8
Data:
grant_type=authorization_code&client_id=xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx&client_secret=yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy&redirect_uri=https://www.example.com/&code=zzzzzzzz-zzzz-zzzz-zzzz-zzzzzzzzzzzz
If successful, you will receive a JSON response with the tokens:
{
“access_token”: “xxxx”,
“refresh_token”: “yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy”,
“expires_in”: 21600
}
If there are any problems with the request, you’ll receive a 400 response with an error message.
{
“status”: “EXAMPLE_ERROR_CODE”,
“message”: “A human readable error message”,
“correlationId”: “da1e1d2f-fa6b-472a-be7b-7ab9b9605d59”,
“requestId”: “ecc0b50659814f7ca37f5d49cdf9cbd3”
}
3. Refresh OAuth 2.0 access token: to create new access token by using previously created refresh token
POST URL:
https://api.hubapi.com/oauth/v1/token
Headers:
Content-Type: application/x-www-form-urlencoded;charset=utf-8
Data:
grant_type=refresh_token&client_id=xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx&client_secret=yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy&refresh_token=zzzzzzzz-zzzz-zzzz-zzzz-zzzzzzzzzzzz
If successful, you will receive a JSON response with a new access_token:
{
“access_token”: “xxxx”,
“refresh_token”: “zzzzzzzz-zzzz-zzzz-zzzz-zzzzzzzzzzzz”,
“expires_in”: 21600
}
If there are any problems with the request, you’ll receive a 400 response with an error message.
{
“status”: “EXAMPLE_ERROR_CODE”,
“message”: “A human readable error message”,
“correlationId”: “da1e1d2f-fa6b-472a-be7b-7ab9b9605d59”,
“requestId”: “ecc0b50659814f7ca37f5d49cdf9cbd3”
}
4. Get recent engagements : to get the activities
Example GET URL:
https://api.hubapi.com/engagements/v1/engagements/recent/modified?hapikey=demo&count=2&since=1483246800000
NOTE:
In order to fetch data starting from the time of the previous Schedule script to till the time of the current Schedule script, we have included parameter “since” in API call to fetch data of 2-hour duration starting from the previous schedule script.
/**
*@NApiVersion 2.x
*@NScriptType ScheduledScript
*/
/*******************************************************************************
* BTN-995 Sync HubSpot data changes to NetSuite
* *************************************************************************
*
* Created Date: 02-03-2020
*
* Revised date:
*
* Author: Jobin & Jismi IT Services LLP
* Script Description: Script for integrating netsuite to HUBSPOT and to sync HubSpot data changes to NetSuite.
This script will work every day.
*****************************************************************************
**/
const ERROR_STACK = [];
var ERROR = [];
define(['N/https', 'N/file', 'N/log', 'N/record', 'N/search', 'N/format', 'N/runtime', "N/task"],
function (https, file, log, record, search, format, runtime, task) {
function errorarr(HubId, type, err) {// format error module
errorObj = {};
errorObj["hubspotId"] = HubId;
errorObj["type"] = type;
errorObj["error"] = err;
log.debug("errorObj", errorObj);
return errorObj;
}
function rescheduleScriptandReturn(offset, hasMore) { //function to reschedule the script.
try {
log.debug("reschedule");
//offset is the last range of the just finished search.
//hasMore Determines if there are more data to integrate
var mrTask = task.create({
taskType: task.TaskType.SCHEDULED_SCRIPT,
scriptId: "customscript_jj_ss_sync_hubspot_btn_995",
deploymentId: "customdeploy_jj_ss_sync_hubspot_btn_995",
params: {
custscript_offset: offset,
custscript_hasmore: hasMore,
}
});
var scriptTaskId = mrTask.submit();
log.debug("scriptTaskId", scriptTaskId);
} catch (err) {
log.debug({
title: "ERROR",
details: err
});
log.debug({
title: 'error on rescheduleScriptandReturn',
details: err
});
}
}
function parseAndFormatDateString(date) {// format date
var rawDateString = date;
var parsedDate = format.parse({
value: rawDateString,
type: format.Type.DATE
});
return parsedDate;
}
function formatAMPM(timestamp) {// format time
var d = new Date(timestamp);
var time = format.format({ value: d, type: format.Type.DATETIME, timezone: format.Timezone.AMERICA_LOS_ANGELES });
return time.slice(-10);
}
function applyTryCatch(DATA_OBJ, NAME) {//Common Try-Catch function
function tryCatch(myfunction, key) {
return function () {
try {
return myfunction.apply(this, arguments);
} catch (e) {
log.error("error in " + key, e);
ERROR_STACK.push(e);
return false;
}
};
}
for (var key in DATA_OBJ) {
if (typeof DATA_OBJ[key] === "function") {
DATA_OBJ[key] = tryCatch(DATA_OBJ[key], NAME + "." + key);
}
}
}
var findId = {
userNote: function (eng_id) {
var noteSearchObj = search.create({
type: "note",
filters:
[
["custrecord_jj_engagement_id", "is", "" + eng_id]
],
columns:
[
search.createColumn({ name: "custrecord_jj_engagement_id", label: "Engagement Id" })
]
});
var searchResultCount = noteSearchObj.runPaged().count;
//log.debug("noteSearchObj result count",searchResultCount);
var engagementId;
noteSearchObj.run().each(function (result) {
// .run().each has a limit of 4,000 results
engagementId = result.getValue({
name: 'custrecord_jj_engagement_id'
});
});
log.debug("engagementId_n", engagementId);
return engagementId;
},
phoneCall: function (eng_id) {
var phonecallSearchObj = search.create({
type: "phonecall",
filters:
[
["custevent_jj_engagement_id", "is", "" + eng_id]
],
columns:
[
search.createColumn({ name: "custevent_jj_engagement_id", label: "Engagement Id" })
]
});
var searchResultCount = phonecallSearchObj.runPaged().count;
// log.debug("phonecallSearchObj result count",searchResultCount);
var engagementId;
phonecallSearchObj.run().each(function (result) {
// .run().each has a limit of 4,000 results
engagementId = result.getValue({
name: 'custevent_jj_engagement_id'
});
});
log.debug("engagementId_c", engagementId);
return engagementId;
},
ContactId: function (Id) {// search for contacts
log.debug("test_con", Id);
var contactSearchObj = search.create({
type: "contact",
filters:
[
["custentity_rb_hubspot_id", "is", "" + Id]
],
columns:
[
search.createColumn({
name: "internalid",
sort: search.Sort.ASC,
label: "Internal ID"
})
]
});
var searchResultCount = contactSearchObj.runPaged().count;
//log.debug("contactSearchObj result count_con", searchResultCount);
var internalid;
contactSearchObj.run().each(function (result) {
// .run().each has a limit of 4,000 results
internalid = result.getValue({
name: 'internalid'
});
});
log.debug("internalid", internalid);
return internalid;
},
customerId: function (Id) {// search for customer
log.debug("test_cus 11", Id);
var customerSearchObj = search.create({
type: "customer",
filters:
[
// ["custentity_rb_hubspot_id","is","2949417657"]
["custentity_rb_hubspot_id", "is", "" + Id]
],
columns:
[
search.createColumn({ name: "internalid", label: "Internal ID" })
]
});
var searchResultCount = customerSearchObj.runPaged().count;
//log.debug("customerSearchObj result count_cus 11", searchResultCount);
var internalid;
customerSearchObj.run().each(function (result) {
// .run().each has a limit of 4,000 results
internalid = result.getValue({
name: 'internalid'
});
log.debug("internalid_cus", internalid);
});
return internalid;
}
};
applyTryCatch(findId, "findId");
var formatData = {
formatCall: function (arr) {// create call object for creating record
var dataArray = [];
var contact = arr.associations.contactIds;
log.debug("contact", contact);
if (contact.length != 0) {
for (var i = 0; i < contact.length && contact.length != 0; i++) {
var dataObj = {};
var timestamp = arr.engagement.createdAt;
//log.debug("timestamp_Call", timestamp);
var date_ob = new Date(timestamp);
// year as 4 digits (YYYY)
var year = date_ob.getFullYear();
// month as 2 digits (MM)
var month = (date_ob.getMonth() + 1);
// date as 2 digits (DD)
var date = date_ob.getDate();
dataObj["startdate"] = parseAndFormatDateString(month + '/' + date + '/' + year);
// dataObj["startdate"] = "03/02/2020";
//log.debug("startdate", parseAndFormatDateString(month + '/' + date + '/' + year));
dataObj["starttime"] = formatAMPM(timestamp);
// dataObj["starttime"] =new date(timestamp);
dataObj["endtime"] = formatAMPM(timestamp);
var company = arr.associations.companyIds[0];
var cont = arr.associations.contactIds[i];
var customer = findId.customerId(company);
dataObj["company"] = customer;
dataObj["contact"] = findId.ContactId(cont);
dataObj["title"] = arr.engagement.bodyPreview;
dataObj["custevent_jj_engagement_id"] = JSON.stringify(arr.engagement.id);
dataObj["status"] = "COMPLETE";
dataObj["message"] = arr.engagement.bodyPreview;
//log.debug("arr.associations.companyIds[0]", arr.associations.companyIds[0]);
dataArray.push([dataObj, arr.associations.companyIds[0], customer]);
}
} else {
var dataObj = {};
log.debug("arr.associations.companyIds[0]c", arr.associations.companyIds[0]);
var timestamp = arr.engagement.createdAt;
log.debug("timestamp_call", timestamp);
var date_ob = new Date(timestamp);
// year as 4 digits (YYYY)
var year = date_ob.getFullYear();
// month as 2 digits (MM)
var month = (date_ob.getMonth() + 1);
// date as 2 digits (DD)
var date = date_ob.getDate();
// dateString = theDate.toGMTString();
dataObj["startdate"] = parseAndFormatDateString(month + '/' + date + '/' + year);
//dataObj["startdate"] = "03/02/2020";
//log.debug("startdate", parseAndFormatDateString(month + '/' + date + '/' + year));
dataObj["starttime"] = formatAMPM(timestamp);
// dataObj["starttime"] =new date(timestamp);
//log.debug("starttime", formatAMPM(timestamp));
dataObj["endtime"] = formatAMPM(timestamp);
var company = arr.associations.companyIds[0];
var cont = arr.associations.contactIds[0];
var customer = findId.customerId(company);
dataObj["company"] = customer;
if (arr.associations.contactIds[0]) {
dataObj["contact"] = findId.ContactId(cont);
}
dataObj["title"] = arr.engagement.bodyPreview;
dataObj["custevent_jj_engagement_id"] = JSON.stringify(arr.engagement.id);
dataObj["status"] = "COMPLETE";
dataObj["message"] = arr.engagement.bodyPreview;
dataArray.push([dataObj, arr.associations.companyIds[0], customer]);
log.debug("dataArray", dataArray);
}
return dataArray;
},
formatNote: function (arr) {// create note object for creating record
var dataObj = {};
log.debug("arr.associations.companyIds[0]n", arr.associations.companyIds[0]);
var company = arr.associations.companyIds[0];
log.debug("findId.customerId(company)", findId.customerId(company));
var customer = findId.customerId(company);
dataObj["entity"] = customer;
dataObj["notetype"] = "7";
dataObj["direction"] = "2";
var timestamp = arr.engagement.createdAt;
var date_ob = new Date(timestamp);
// year as 4 digits (YYYY)
var year = date_ob.getFullYear();
// month as 2 digits (MM)
var month = (date_ob.getMonth() + 1);
// date as 2 digits (DD)
var date = date_ob.getDate();
dataObj["notedate"] = parseAndFormatDateString(month + '/' + date + '/' + year);
//dataObj["notedate"] ="03/02/2020";
//log.debug("notedate", parseAndFormatDateString(month + '/' + date + '/' + year));
dataObj["title"] = arr.engagement.bodyPreview;
dataObj["time"] = formatAMPM(timestamp);
dataObj["note"] = arr.engagement.bodyPreview;
dataObj["custrecord_jj_engagement_id"] = JSON.stringify(arr.engagement.id);
return [dataObj, company, customer];
},
formatEmail: function (arr) {// create email object for creating record
var dataObj = {};
log.debug("arr.associations.companyIds[0]e", arr.associations.companyIds[0]);
log.debug("engage",arr.engagement.id);
var company = arr.associations.companyIds[0];
var customer = findId.customerId(company);
log.debug("customer", customer);
dataObj["entity"] = customer;
dataObj["notetype"] = "3";
if (arr.engagement.type = "INCOMING_EMAIL") {
dataObj["direction"] = "1";
} else if (arr.engagement.type = "EMAIL") {
dataObj["direction"] = "2";
}
var timestamp = arr.engagement.timestamp;
var date_ob = new Date(timestamp);
// year as 4 digits (YYYY)
var year = date_ob.getFullYear();
// month as 2 digits (MM)
var month = (date_ob.getMonth() + 1);
// date as 2 digits (DD)
var date = date_ob.getDate();
dataObj["notedate"] = parseAndFormatDateString(month + '/' + date + '/' + year);
//dataObj["notedate"] = "03/02/2020";
//log.debug("notedate", parseAndFormatDateString(month + '/' + date + '/' + year));
dataObj["time"] = formatAMPM(timestamp);
// dataObj["time"] =new date(timestamp);
dataObj["title"] = arr.metadata.subject;
if (arr.metadata.text) {
dataObj["note"] = arr.metadata.text;
} else {
dataObj["note"] = arr.engagement.bodyPreview;
}
dataObj["custrecord_jj_engagement_id"] = JSON.stringify(arr.engagement.id);
return [dataObj, company, customer];
}
};
applyTryCatch(formatData, "formatData");
var createRecord = {
createCall: function (arrObj) {// to create Phone call record
var obj;
log.debug("arrObj", arrObj);
for (var i = 0; i < arrObj.length; i++) {
try {
obj = arrObj[i];
if (obj[2]) {
var objRecord = record.create({
type: record.Type.PHONE_CALL,
// isDynamic: true,
});
log.debug("obj[0]_call", obj[0]);
for (key in obj[0]) {
if (obj[0].hasOwnProperty(key)) {
//log.debug("key_c",obj[0][key]);
if (key == "starttime" || key == "endtime") {
objRecord.setText({
fieldId: key,
text: obj[0][key],
ignoreFieldChange: true
});
} else {
objRecord.setValue({
fieldId: key,
value: obj[0][key]
});
}
}
}
var recordId = objRecord.save({
enableSourcing: true,
ignoreMandatoryFields: true
});
log.debug("id", recordId);
}
else {
ERROR.push(errorarr(obj[1], type, "customer does not exist in Netsuite"));
}
}
catch (e) {
ERROR.push(errorarr(obj[1], "Phone Call", [e.name, e.message]));
log.debug("createCall_error", [e.name, e.message]);
}
}
},
createNote: function (obj, type) {// to create email and note in user note
try {
if (obj[2]) {
var objRecord = record.create({
type: record.Type.NOTE,
// isDynamic: true,
});
log.debug("obj[0]", obj[0]);
for (key in obj[0]) {
if (obj[0].hasOwnProperty(key)) {
//log.debug("key_n",obj[0][key]);
if (key == "time") {
objRecord.setText({
fieldId: key,
text: obj[0][key],
ignoreFieldChange: true
});
} else {
objRecord.setValue({
fieldId: key,
value: obj[0][key]
});
}
}
}
var recordId = objRecord.save({
enableSourcing: true,
ignoreMandatoryFields: true
});
log.debug("id", recordId);
}
else {
ERROR.push(errorarr(obj[1], type, "customer does not exist in Netsuite"));
}
} catch (e) {
ERROR.push(errorarr(obj[1], type, [e.name, e.message]));
log.debug("createNote_error", [e.name, e.message]);
}
}
};
applyTryCatch(createRecord, "createRecord");
var main = {
format: function (result) {// redirect to different function with respect to its type
for (var i = 0; i < result.length; i++) {
var eng_id = result[i].engagement.id;
if (result[i].engagement.type === "CALL") {
// log.debug("call");
var phone_call = findId.phoneCall(eng_id);
if (!(phone_call)) {
createRecord.createCall(formatData.formatCall(result[i]));
}
} else if (result[i].engagement.type === "NOTE") {
var note = findId.userNote(eng_id);
if (!(note)) {
createRecord.createNote(formatData.formatNote(result[i]), "NOTE");
}
} else if (result[i].engagement.type === "EMAIL" || result[i].engagement.type === "INCOMING_EMAIL") {
var note = findId.userNote(eng_id);
if (!(note)) {
createRecord.createNote(formatData.formatEmail(result[i]), "EMAIL");
}
} else {
continue;
}
}
},
getData: function (ofset, token, time) {//to create token and refresh token and get result
var offset = ofset;
var timestamp =JSON.stringify(time);
var header = [];
header['Content-Type'] = 'application/json';
header['Accept'] = 'application/json';
header['authorization'] = 'Bearer ' + token;
var response = https.get({
url: 'https://api.hubapi.com/engagements/v1/engagements/recent/modified?since=' + timestamp + '&offset=' + offset + '&count=100',
headers: header
});
//log.debug("response", response.body);
var get_response = JSON.parse(response.body);
//log.debug("get_response",get_response);
var result_array = get_response.results;
var total = get_response.total;
log.debug("total", total);
var offset = get_response.offset;
log.debug("offset", offset);
var hasMore = get_response.hasMore;
//log.debug("result_array",result_array);
return [result_array, hasMore, offset, total];
},
add: function () {// function to add error to file
// var a=fileObj.getContents();
// a=a+ERROR;
// for(var i=0;i<ERROR.length;i++){
// log.debug("ERROR[i]",ERROR[i]);
if (ERROR.length != 0) {
var fileObj = file.load({
id: 4277972
});
fileObj.appendLine({
value: JSON.stringify(ERROR)
});
fileObj.folder = 99664;
fileObj.save();
}
},
execute: function () {// entry point of Schedule script
var fileObj = file.load({
id: 4277971
});
var tokenObj = fileObj.getContents();
tokenObj = JSON.parse(tokenObj);
var refresh_token = tokenObj.refresh_token;
log.debug("refresh_token", refresh_token);
var tokenHeader = [];
tokenHeader['Content-Type'] = 'application/x-www-form-urlencoded';
tokenHeader['Accept-Charset'] = 'utf-8';
tokenHeader['Accept'] = 'application/json';
var tokenResponse = https.post({
url: 'https://api.hubapi.com/oauth/v1/token',
body: "grant_type=refresh_token&client_id=7a257942-f2d8-4aff-87a2-07f8a49af564&client_secret=a5146c00-d453-4394-92ab-dcdce9bd9b01&refresh_token=" + refresh_token,
headers: tokenHeader
});
var tokenBody = JSON.parse(tokenResponse.body);
var token = tokenBody.access_token;
var date = new Date();
log.debug("time", date.getTime());
var time = Math.round(date.getTime() / 1000 - 9000);
var timestamp=Number(time)*1000
//timestamp = Number(timestamp) - 3600;
log.debug("timestamp", timestamp);
//var total;
var pages = 0;
// get the current script
var scriptObj = runtime.getCurrentScript();
////get the script parameter startRange
var offset = scriptObj.getParameter({
name: 'custscript_offset'
});
log.debug("reoffset", offset);
var hasMore = scriptObj.getParameter({
name: 'custscript_hasmore'
});
if (offset == '' || offset == null || offset < 100 || offset == undefined) {
var offset = 0;
var hasMore = true;
}
while (hasMore) {// page through the result
//log.debug("test");
var result = main.getData(offset, token, timestamp);
if (result) {
main.format(result[0]);
}
total = result[3];
hasMore = result[1];
//log.debug("hasMore", hasMore);
offset = result[2];
//log.debug("offset", offset);
pages++;
if (hasMore && pages > 4) {
log.debug("reschedule");
rescheduleScriptandReturn(offset, hasMore);
break;
}
}
//log.debug("test2");
main.add();
return true;
}
};
applyTryCatch(main, "main");
return main;
});