HubSpot reports display timestamps based on the portal’s timezone. However, you might want to display timestamps that align with each rep’s local time, if your portal account operates across multiple regions.
To support this, I tested a custom-coded workflow action that dynamically adjusts timestamps per rep’s timezone, so each person sees reports in their local time!
Preparations
-
A dropdown or radio select to store the rep’s timezone
- Use IANA-format timezones for internal names.
You can find the list of IANA time zones at wikipedia
- Use IANA-format timezones for internal names.
-
A datetime property to save the adjusted timestamp
2. Register your access token as a secret in the custom code action to fetch your portal’s timezone.
- Here’s how to create a private app to generate access token
3. Properties to include:
- rep_location: Retrieves IANA timezones.
- original_datetime: Retrieves the date or date/time property.
4. Data Output:
- adjustedTS (datetime)
- To use that value in reports, please make sure to add an “Edit Record” action after your custom code, and save the localized timestamp to a property of your choice.
Codes for the action
const axios = require('axios');
exports.main = async (event, callback) => {
const hubspotApiKey = process.env.apiKey_PrivateApp; // Make sure to input correct Secret Name you chose in the action
let portalTimezone = null;
// STEP 1 - Get Portal's IANA Timezone
/*
Fetches the timezone setting from HubSpot's account settings.
Official doc: https://developers.hubspot.com/docs/guides/api/settings/account-information-api
*/
try {
const response = await axios.get('https://api.hubapi.com/account-info/v3/details', {
headers: {
'Authorization': `Bearer ${hubspotApiKey}`,
'Content-Type': 'application/json'
}
});
portalTimezone = response.data.timeZone;
console.log(`Portal Timezone: ${portalTimezone}`);
} catch (error) {
console.log("Error fetching portal timezone:", error.response ? error.response.data : error.message);
return;
}
// STEP 2 - Fetch UTC Offset from the portal's timezone for given timestamp
const originalTS = Number(event.inputFields["original_datetime"]);
const targetDate = new Date(originalTS);
const options = { timeZone: portalTimezone, timeZoneName: "longOffset" };
const timeString = targetDate.toLocaleString("en-US", options);
const utcOffsetMatch = timeString.match(/GMT([+-]\d+):\d+/);
const portalTimezoneOffset = utcOffsetMatch ? parseInt(utcOffsetMatch[1], 10) : null;
console.log(`UTC Offset at the given timestamp: ${portalTimezoneOffset}`);
// STEP 3 - Determine rep's local timezone
const repLocation = event.inputFields["rep_location"];
if (!repLocation) {
console.log("Missing rep_location.");
return;
}
const repOptions = { timeZone: repLocation, timeZoneName: "longOffset" };
const repTimeString = targetDate.toLocaleString("en-US", repOptions);
const repUtcOffsetMatch = repTimeString.match(/GMT([+-]\d+):\d+/);
const repTimezoneOffset = repUtcOffsetMatch ? Number(repUtcOffsetMatch[1]) : 0;
console.log(`Rep UTC Offset: ${repTimezoneOffset}`);
// STEP 4 - Calculates the adjusted timestamp by adding/removing difference between the representative's timezone and the portal's timezone.
if (isNaN(repTimezoneOffset) || isNaN(portalTimezoneOffset)) {
console.log("Timezone values are invalid!");
return;
}
const timezoneDifference = (repTimezoneOffset - portalTimezoneOffset) * 60 * 60 * 1000;
const adjustedTS = originalTS + timezoneDifference;
console.log("Adjusted Timestamp:", adjustedTS);
// STEP 5 - Return data so they can be used in later actions
callback({
outputFields: {
adjustedTS: adjustedTS
}
});
};
console.log("End of the action");