Updating CO records with excel/sheets data via Zapier or similar

[Bob2245]

So, we've got this use case where the client has a weekly generated spreadsheet exported from their finance system. We need to use this sheet to update records in a Custom Object in Hubspot. The sheet has no Hubspot Object ID, so the steps involved to import this normally would be: 

1. Export existing records from Hubspot, including CO record IDs
2. Paste Hubspot records on a second tab in the finance system export. 
3. Do a VLOOKUP to get the Hubspot record IDs onto the finance system export.
4. Paste the complete thing into its own spreadsheet to save and upload into Hubspot.

The client isn't particularly tech savvy, so asking them to do these steps is asking for a heap of trouble. There's no budget, scope or buy-in from the client to build an API connection between their finance system and Hubspot. So I'm looking for alternative solutions. 

My first hunch was creating a Google Sheets -> Hubspot connection via Zapier. Process would then be:

1. Client 'just' has to paste the finance system data into this sheet. 
2. Zapier triggers off of new rows
3. Zapier updates Hubspot records using a custom unique ID (not the Hubspot CO record ID), easy peasy.

However, Zapier doesn't support updating CO records sadly enough. 

So my question(s):

1. Does anyone have any other hacky/low tech workarounds for this?
2. Is there a Zapier low code/no code alternative that does support CO record updating through a custom unique ID?

TIA

[ScottHemmeter]

Hi, I realize this question is a bit old, but it's a classic problem. I wanted to inform you that we have a solution for you. We had this same type of challenge and decided to build a HubSpot app for it. It's called VLOOKUP for Workflow and it just launched on the Marketplace today. It allows you to reference a Google Sheet or upload your own CSV data and use that source as a lookup table in HubSpot. You can then use the result downstream in your workflow. It's simple, scalable, and affordable.

In your example, your finance team would put the data it wants you to have in a Google Sheet. You can setup that Sheet as a Source in VLOOKUP. Workflow could then use a value from your custom object record to look up a row in the sheet and bring back one of the values.

[Levi]

Hi @Bob2245 ,

My idea:
1. Trigger a Zap when a new row is created or updated in the Google Spreadsheet

2. Create or update a contact, company or deal (based on what data you want to store, this is a copy/clone of the properties you want to store on the CO)
3. Copy the data (properties) you imported from the spreadsheet via a workflow from contact, company or deal to the Custom Object. 

So you need to add all properties 2 times (on CO level, and on contact/company or deadl level) and then copy via a workflow. 

Is that an option? Please let me know! 

[Bob2245]

Hi Levi,

Thanks for the reply. I don't think this will work though:

1. The CO records have no contacts associated to them
2. Even if they did, there's no way inside Hubspot to do a lookup to update the correct CO record associated. That's specifically why I'd need Zapier in this case, as it allows for selecting a lookup value from the Google Sheet to find the CO record to update.

[Levi]

Hi @Bob2245 , 

Thanks! Does the CO have associations with other objects? Or do they just standalone in HubSpot without associations? Or where do you use the CO for? 

Maybe another option: first create a row in the Google spreadsheet with a HubSpot CO workflow (see below) with the CO ID (or just the unique data you need)? And then let the client paste the data in the right columns according to the object id's you imported via the workflow to Google Sheets.  More info about add data to google sheets via workflow here. Then you at least have the right spreadsheet to update the CO via a manual import.

Beside you can use the API to automate the OC import proces via "create / update schema" in this link. 

[Bob2245]

There's project constraints to think about unfortunately. There's no room in scope and planning for an API connection and we need to minimize client's manual work. Our current workaround is kind of like you say: we're just creating a spreadsheet template that has as much of the CO data already there, so they can relatively easily create an import file from it to upload and update with. I was just hoping to find something to eliminate their manual work almost entirely, without requiring API work.

[Levi]

Hi @Bob2245 , Unfortunately i think thats the only option  atm, most native apps like Zapier, Tray.io, Integromat only support the "Core" objects of HubSpot.  I think they will support this as time goes on because CO's are a bit  new at the moment.