Updating Unknown Country/Timezones Weekly via external file

[Drew_Kulas]

Hi all! 

I have a list of zipcodes with city, state, and timezones.  These are also linked to the country(United States in this case) and I would like to run a workflow weekly to check for Contacts that are missing a value in either the Country filed or Timezone field OR both.  Is there a way to link or use my excel list inside a worklow?  I hope this is not to confusing.  

Thanks, 

Andrew

[karstenkoehler · Accepted solution]

Hi @Drew_Kulas,

This could be done with a custom code workflow action in Operations Hub Professional or Enterprise, yes, but would require coding: https://developers.hubspot.com/docs/reference/api/automation/custom-code-actions

If this is a weekly manual effort anyway, have you considered exporting your contacts, appending the new information using VLOOKUP or INDEXMATCH, then reimport?

Alternatively, you could also solve this with third party automation like Zapier as a recurring process: https://knowledge.hubspot.com/integrations/how-to-use-zapier-and-hubspot

Lastly, AI workflow actions could also help with this: https://knowledge.hubspot.com/workflows/use-ai-to-manage-data-in-workflows

Best regards 

[RubenBurdin · Accepted solution]

Hello @Drew_Kulas 

You can’t embed an Excel sheet directly inside a HubSpot workflow—the workflow tool has no “look-up table” step. You have three practical routes:

1. Keep it manual but quick (Starter & up)

1. Build an active list:
   Country is unknown OR Time zone is unknown.

2. Export that list each week, VLOOKUP the ZIPs against your Excel file, add the country/time-zone columns, and re-import as a “Contacts update.”
   When you re-import, HubSpot matches on email/record ID and fills only the columns you supply, so nothing else is overwritten. It’s two files and five minutes, but still manual.

2. Operations Hub Pro: custom-code step
Store the CSV somewhere public (HubSpot Files, S3, GitHub raw).
Create a scheduled workflow enrolled by the same “unknown” criteria and add a Custom Code action:

• fetch the CSV with axios

• build a JS object keyed by ZIP

• loop over event.inputFields, read zip, look up the match

• call /crm/v3/objects/contacts/batch/update with country + time-zone

Runs unattended every Monday at 2 AM, fully automated.

3. Turn the spreadsheet into a HubSpot object (no code, Ops Hub Pro/Ent)

1. Create a custom object called ZIP Info with properties ZIP, City, State, Country, Time zone.

2. Import your Excel once to populate 40 k rows.

3. Add a workflow:
   Trigger: Contact’s ZIP is known AND (Country unknown OR Time zone unknown).
   Action 1: “Find ZIP Info record where ZIP equals contact’s ZIP.”
   Action 2: “Copy Country from ZIP Info to Contact,” same for Time zone.
   Because the data lives inside HubSpot you never touch the spreadsheet again—new ZIPs get added as you encounter them.

You can pick #1 if you’re on free/Starter, #2 if you’re comfortable with a bit of JS, #3 if you want a fully point-and-click solution that scales.

Hope It helps.!

Ruben Burdin 

Real-Time Data Sync Between any CRM or Database | Founder @Stacksync (YC W24)

[SamCBarth_LAIRE · Accepted solution]

Hi @Drew_Kulas 

There is an app that will allow you to reference a lookup table from a Google sheet or an Excel sheet and use that information for reference

It sounds like you're just talking about creating rules around contacts if they don't have this information filled out that the workflow fills it in for them you can reference the IP information on the contact info to identify this but unless the contact is filled out of form that says I'm in this ZIP code or something you're not going to be able to map that information properly does that make sense?

And I think a report that shows contacts without certain properties I think would be enough versus a workflow that gets utilized

[RubenBurdin · Accepted solution]

Hello @Drew_Kulas 

You can’t embed an Excel sheet directly inside a HubSpot workflow—the workflow tool has no “look-up table” step. You have three practical routes:

1. Keep it manual but quick (Starter & up)

1. Build an active list:
   Country is unknown OR Time zone is unknown.

2. Export that list each week, VLOOKUP the ZIPs against your Excel file, add the country/time-zone columns, and re-import as a “Contacts update.”
   When you re-import, HubSpot matches on email/record ID and fills only the columns you supply, so nothing else is overwritten. It’s two files and five minutes, but still manual.

2. Operations Hub Pro: custom-code step
Store the CSV somewhere public (HubSpot Files, S3, GitHub raw).
Create a scheduled workflow enrolled by the same “unknown” criteria and add a Custom Code action:

• fetch the CSV with axios

• build a JS object keyed by ZIP

• loop over event.inputFields, read zip, look up the match

• call /crm/v3/objects/contacts/batch/update with country + time-zone

Runs unattended every Monday at 2 AM, fully automated.

3. Turn the spreadsheet into a HubSpot object (no code, Ops Hub Pro/Ent)

1. Create a custom object called ZIP Info with properties ZIP, City, State, Country, Time zone.

2. Import your Excel once to populate 40 k rows.

3. Add a workflow:
   Trigger: Contact’s ZIP is known AND (Country unknown OR Time zone unknown).
   Action 1: “Find ZIP Info record where ZIP equals contact’s ZIP.”
   Action 2: “Copy Country from ZIP Info to Contact,” same for Time zone.
   Because the data lives inside HubSpot you never touch the spreadsheet again—new ZIPs get added as you encounter them.

You can pick #1 if you’re on free/Starter, #2 if you’re comfortable with a bit of JS, #3 if you want a fully point-and-click solution that scales.

Hope It helps.!

Ruben Burdin 

Real-Time Data Sync Between any CRM or Database | Founder @Stacksync (YC W24)

[karstenkoehler · Accepted solution]

Hi @Drew_Kulas,

This could be done with a custom code workflow action in Operations Hub Professional or Enterprise, yes, but would require coding: https://developers.hubspot.com/docs/reference/api/automation/custom-code-actions

If this is a weekly manual effort anyway, have you considered exporting your contacts, appending the new information using VLOOKUP or INDEXMATCH, then reimport?

Alternatively, you could also solve this with third party automation like Zapier as a recurring process: https://knowledge.hubspot.com/integrations/how-to-use-zapier-and-hubspot

Lastly, AI workflow actions could also help with this: https://knowledge.hubspot.com/workflows/use-ai-to-manage-data-in-workflows

Best regards 

[Drew_Kulas]

The current method is exporting and using xlookup and then reimporting the appended like you mentioned.  I was hoping to be able to house the list of zipcodes in HubSpot and reference it through a custom-code-workflow.  

I guess alternatively, I could hardcode the list but that seems overkill and time consuming.

Thanks for the quick reply,

Andrew

[karstenkoehler]

@Drew_Kulas as I mentioned, a custom code workflow action or an external tool like Zapier could do the job 🙂