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?

[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