Issues Updating Spreadsheet using Workflow

[ChrisAlexander]

I'm working on creating a Deal Ledger for my company in Google Sheets that automatically updates as projects move through my sales pipeline. When I use the workflow to add a row, it appends the row to the very bottom of the spreadsheet instead of populating the first available row. It also exports the UserID as the numerical string value instead of the User's name. Dates are in Unix which I have to apply a formula to convert, which wouldn't be a problem except it adds a row outside of those that have the formula already applied to them. Anyone have fixes for these problems they're willing to share?

[h-recker]

If you're finding yourself pushing data to Sheets often, the Google Sheets options in workflows have always ended up being more of a hassle than a help.

Coefficient's 2-way sync on HubSpot's marketplace has been around for quite some time and can make this process a lot smoother. Point and click to get the data you need in Sheets, you can set it on a refresh schedule and take snapshots of data in time to populate your deal report in real-time. You can also leverage Slack automations to update your team on your deals by any condition.

[Akash_Vi_R]

Hey @ChrisAlexander - faced a similar problem while using Zapier in the past.

A simple workaround is to import data into sheet1, and IMPORTRANGE in a new sheet. In this new sheet, you can alter data based on your requirements (changing date formats, etc). 

Recently, I started using Superjoin. It's a Sheets add-on and works splendidly. Automatically changes the format from UNIX to the right format and imports data. 

[DianaGomez · Accepted solution]

Hi @ChrisAlexander, 

Thank you for reaching out to the Community!

When it comes to the dates property, we are aware of this issue, as mentioned in our knowledge base article here. By default, HubSpot sends internal property values to Google Sheets. For dates, HubSpot sends Unix timestamps in milliseconds. To convert these dates in Google Sheets, you can use the following formula in a separate column: =A1/1000/60/60/24 + DATE(1970,1,1). Be sure to replace A1 with the column containing the Unix timestamp.

As for the other properties, the internal values are being sent instead of the labels. Our workaround involves creating a Vlookup table that maps the internal values to the corresponding labels we see within HubSpot.

I hope this helps,

Diana