topic Re: Code to standardize state/region in Data Hub

Code to standardize state/region

KyleJepson — Mon, 10 May 2021 18:51:43 GMT

This code takes two-letter abbreviations from the State/Region property and outputs the full name. Works for all U.S. states and territories and Canadian providences. If you want it to work in other places, you'll have to expand the list of abbreviations yourself 😛

This doesn't actually update the record. To do that, you'll need to add a workflow action that copies the output value from the code into the State/Region contact property. (Or add an additional API call to the code to update the property.)

TO MAKE THIS WORK FOR COMPANIES: Change contacts to companies in hubspotClient.crm.contacts.basicApi.getById.

TO MAKE THIS WORK THE OTHER WAY AROUND: Would take a few extra steps. This code takes the value from State/Region, uses .toUpperCase to make it ALL CAPS, and then searches the list of abbreviations (which are also ALL CAPS) to look for matches. There isn't a standard method like .toUpperCase that puts things in Title Case, and the .has method for maps is case sensitive. Luckily, the Data Format action (also included in Operations Hub) does all kinds of things with capitalization, so you could do it that way. I think maybe the easiest thing to do is to reverse the list of abbreviations and make the state names ALL UPPERCASE like the abbreviation are. Then use the code basically like it is. The output of this code could be pulled into a Data Format action to fix the capitalization, and then copy the output of the Data Format action into the property.

OTHER NOTES: This works great if all your State/Region values are two-letter abbreviations or properly spelled/capitalized full names. If you have people using old fashioned abbreviations (Ill. for Illinois, Mass. for Massachusetts, Minn. for Minnesota) or if you have typoes (Texsa for Texas), this won't help with that. In a future world, when custom-coded actions can be used in branching logic, it would be worthwhile to have a code that takes State/Region and, if the value isn't found in the array below, it creates a task and assigns it to the contact owner to have them fix the problem.

['AL', 'Alabama', 'AK', 'Alaska', 'AS', 'American Samoa', 'AZ', 'Arizona', 'AR', 'Arkansas', 'AA', 'Armed Forces Americas', 'AE', 'Armed Forces Europe', 'AP', 'Armed Forces Pacific', 'CA', 'California', 'CO', 'Colorado', 'CT', 'Connecticut', 'DE', 'Delaware', 'DC', 'District Of Columbia', 'FL', 'Florida', 'GA', 'Georgia', 'GU', 'Guam', 'HI', 'Hawaii', 'ID', 'Idaho', 'IL', 'Illinois', 'IN', 'Indiana', 'IA', 'Iowa', 'KS', 'Kansas', 'KY', 'Kentucky', 'LA', 'Louisiana', 'ME', 'Maine', 'MH', 'Marshall Islands', 'MD', 'Maryland', 'MA', 'Massachusetts', 'MI', 'Michigan', 'MN', 'Minnesota', 'MS', 'Mississippi', 'MO', 'Missouri', 'MT', 'Montana', 'NE', 'Nebraska', 'NV', 'Nevada', 'NH', 'New Hampshire', 'NJ', 'New Jersey', 'NM', 'New Mexico', 'NY', 'New York', 'NC', 'North Carolina', 'ND', 'North Dakota', 'NP', 'Northern Mariana Islands', 'OH', 'Ohio', 'OK', 'Oklahoma', 'OR', 'Oregon', 'PA', 'Pennsylvania', 'PR', 'Puerto Rico', 'RI', 'Rhode Island', 'SC', 'South Carolina', 'SD', 'South Dakota', 'TN', 'Tennessee', 'TX', 'Texas', 'VI', 'US Virgin Islands', 'UT', 'Utah', 'VT', 'Vermont', 'VA', 'Virginia', 'WA', 'Washington', 'WV', 'West Virginia', 'WI', 'Wisconsin', 'WY', 'Wyoming', 'AB', 'Alberta', 'BC', 'British Columbia', 'MB', 'Manitoba', 'NB', 'New Brunswick', 'NF', 'Newfoundland', 'NT', 'Northwest Territory', 'NS', 'Nova Scotia', 'NU', 'Nunavut', 'ON', 'Ontario', 'PE', 'Prince Edward Island', 'QC', 'Quebec', 'SK', 'Saskatchewan', 'YT', 'Yukon']

Re: Code to standardize state/region

callie — Mon, 07 Mar 2022 17:07:00 GMT

This is great, @KyleJepson ! All about that data cleanliness 👏

I'm using this custom code to update Companies (I did change contacts to companies in hubspotClient.crm.contacts.basicApi.getById), but am having trouble with the output. The code successfully executes but continues to fail when the state string value is meant to copy over. Any thoughts?

Re: Code to standardize state/region

KyleJepson — Mon, 07 Mar 2022 18:27:21 GMT

Hmmmm, that's very strange. When you click into the error, does it give you any details?

Re: Code to standardize state/region

callie — Mon, 07 Mar 2022 18:29:58 GMT

Unfortunately, just the simple "Error: Unable to update property" =/

Re: Code to standardize state/region

KyleJepson — Mon, 07 Mar 2022 18:40:36 GMT

Man. I've never seen this before. Without being able to dig into the workflow itself and poke around, I'm probably not going to be able to fix it. At this point, your best bet is probably to reach out to our Support team and see if they can get to the bottom of it. Sorry not to be more help!

Re: Code to standardize state/region

callie — Mon, 07 Mar 2022 18:41:27 GMT

No sweat, appreciate the initial snippet to start with nonetheless. Thanks, Kyle!

Re: Code to standardize state/region

nicole388 — Thu, 28 Jul 2022 23:09:01 GMT

Hi Callie, were you able to resolve the error? I'm getting the same thing.

This is the log:

WARNING: The logs for this function have exceeded the 4KB limit. ... sWritten)]: 178, [Symbol(connect-options)]: [Object] }, _consuming: false, _dumped: false, req: ClientRequest { _events: [Object: null prototype], _eventsCount: 5, _maxListeners: undefined, outputData: [], outputSize: 0, writable: true, _last: true, chunkedEncoding: false, shouldKeepAlive: false, _defaultKeepAlive: true, useChunkedEncodingByDefault: false, sendDate: false, _removedConnection: false, _removedContLen: false, _removedTE: false, _contentLength: 0, _hasBody: true, _trailer: '', finished: true, _headerSent: true, socket: [TLSSocket], connection: [TLSSocket], _header: 'GET /crm/v3/objects/contacts/164868?properties=state HTTP/1.1\r\n' + 'User-Agent: hubspot-api-client-nodejs; 3.4.1\r\n' + 'Accept: application/json\r\n' + 'host: api.hubapi.com\r\n' + 'Connection: close\r\n' + '\r\n', _keepAliveTimeout: 0, _onPendingData: [Function: noopPendingOutput], agent: [Agent], socketPath: undefined, method: 'GET', insecureHTTPParser: undefined, path: '/crm/v3/objects/contacts/164868?properties=state', _ended: true, res: [Circular], aborted: false, timeoutCb: null, upgradeOrConnect: false, parser: null, maxHeadersCount: null, reusedSocket: false, host: 'api.hubapi.com', protocol: 'https:', [Symbol(kCapture)]: false, [Symbol(kNeedDrain)]: false, [Symbol(corked)]: 0, [Symbol(kOutHeaders)]: [Object: null prototype] }, request: Request { _events: [Object: null prototype], _eventsCount: 5, _maxListeners: undefined, method: 'GET', headers: [Object], uri: [Url], useQuerystring: true, callback: [Function], readable: true, writable: true, explicitMethod: true, _qs: [Querystring], _auth: [Auth], _oauth: [OAuth], _multipart: [Multipart], _redirect: [Redirect], _tunnel: [Tunnel], setHeader: [Function], hasHeader: [Function], getHeader: [Function], removeHeader: [Function], localAddress: undefined, pool: {}, dests: [], __isRequestRequest: true, _callback: [Function], proxy: null, tunnel: true, setHost: true, originalCookieHeader: undefined, _disableCookies: true, _jar: undefined, port: 443, host: 'api.hubapi.com', url: [Url], path: '/crm/v3/objects/contacts/164868?properties=state', _json: true, httpModule: [Object], agentClass: [Function: Agent], agent: [Agent], _started: true, href: 'https://api.hubapi.com/crm/v3/objects/contacts/164868?properties=state', req: [ClientRequest], ntick: true, response: [Circular], originalHost: 'api.hubapi.com', originalHostHeaderName: 'host', responseContent: [Circular], _destdata: true, _ended: true, _callbackCalled: true, [Symbol(kCapture)]: false }, toJSON: [Function: responseToJSON], caseless: Caseless { dict: [Object] }, 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: '5ccca1ce-6f6f-4857-b754-e9e3f61afd66', category: 'INVALID_AUTHENTICATION' }, [Symbol(kCapture)]: false }, body: SimplePublicObject { id: undefined, properties: undefined, createdAt: undefined, updatedAt: undefined, associations: undefined, archived: undefined, archivedAt: undefined }, statusCode: 401 } Memory: 105/128 MB Runtime: 1369.83 ms

Re: Code to standardize state/region

callie — Fri, 29 Jul 2022 00:41:32 GMT

We did! The code is below! Keep in mind, this utilizes a Private App token instead of the API key, as the API key will be sunset next quarter 🙂

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

let abbreviations = new Map();
abbreviations.set('ALABAMA', 'AL');
abbreviations.set('ALASKA', 'AK');
abbreviations.set('ARIZONA', 'AZ');
abbreviations.set('ARKANSAS', 'AR');
abbreviations.set('CALIFORNIA', 'CA');
abbreviations.set('COLORADO', 'CO');
abbreviations.set('CONNECTICUT', 'CT');
abbreviations.set('DELAWARE', 'DE');
abbreviations.set('DISTRICT OF COLUMBIA', 'DC');
abbreviations.set('FLORIDA', 'FL');
abbreviations.set('GEORGIA', 'GA');
abbreviations.set('HAWAII', 'HI');
abbreviations.set('IDAHO', 'ID');
abbreviations.set('ILLINOIS', 'IL');
abbreviations.set('INDIANA', 'IN');
abbreviations.set('IOWA', 'IA');
abbreviations.set('KANSAS', 'KS');
abbreviations.set('KENTUCKY', 'KY');
abbreviations.set('LOUISIANA', 'LA');
abbreviations.set('MAINE', 'ME');
abbreviations.set('MARYLAND', 'MD');
abbreviations.set('MASSACHUSETTS', 'MA');
abbreviations.set('MICHIGAN', 'MI');
abbreviations.set('MINNESOTA', 'MN');
abbreviations.set('MISSISSIPPI', 'MS');
abbreviations.set('MISSOURI', 'MO');
abbreviations.set('MONTANA', 'MT');
abbreviations.set('NEBRASKA', 'NE');
abbreviations.set('NEVADA', 'NV');
abbreviations.set('NEW HAMPSHIRE', 'NH');
abbreviations.set('NEW JERSEY', 'NJ');
abbreviations.set('NEW MEXICO', 'NM');
abbreviations.set('NEW YORK', 'NY');
abbreviations.set('NORTH CAROLINA', 'NC');
abbreviations.set('NORTH DAKOTA', 'ND');
abbreviations.set('OHIO', 'OH');
abbreviations.set('OKLAHOMA', 'OK');
abbreviations.set('OREGON', 'OR');
abbreviations.set('PENNSYLVANIA', 'PA');
abbreviations.set('RHODE ISLAND', 'RI');
abbreviations.set('SOUTH CAROLINA', 'SC');
abbreviations.set('SOUTH DAKOTA', 'SD');
abbreviations.set('TENNESSEE', 'TN');
abbreviations.set('TEXAS', 'TX');
abbreviations.set('UTAH', 'UT');
abbreviations.set('VERMONT', 'VT');
abbreviations.set('VIRGINIA', 'VA');
abbreviations.set('WASHINGTON', 'WA');
abbreviations.set('WEST VIRGINIA', 'WV');
abbreviations.set('WISCONSIN', 'WI');
abbreviations.set('WYOMING', 'WY');

exports.main = (event, callback) => {
const hubspotClient = new hubspot.Client({
accessToken: process.env.HAPI_PrivateApp
});
hubspotClient.crm.companies.basicApi.getById(event.object.objectId, ["state"])
.then(results => {
let state = results.body.properties.state;
let test = state.toUpperCase();
let output;
if (abbreviations.has(test)) {
output = abbreviations.get(test);
} else {
output = state;
}
callback({
outputFields: {
output: output
}
});
})
.catch(err => {
console.error(err);
});
}