Synced Data to Google Sheet causing other formulas to randomly change

Apps Third-Party Apps
1

I’m currently using a HubSpot Workflow to sync Deal record info into a Google Sheet. The integration appears to be working properly, however, I’m noticing certain formulas I’ve created on a separate tab are being randomly changed whenever HubSpot adds new rows during each sync.

Tab #1: “HubSpot Sync”

- The HubSpot Workflow is using this tab to sync Deal data into

- The workflow uses the Deal ID to identify if the Deal already exists on the Google Sheet. If so, then it will update the existing row. If not, it will create a new row.

- This all seems to be working as intended

- There are a lot of columns being mapped for each Row, so for the sake of example, I’ll keep it short:

-- Column A: Deal ID

-- Column B: Deal Name

-- Column C: Deal Stage

-- Column D : Amount

Tab #2: “Formulas”

- I have a series of formulas that are calculating Revenue Recognition info based on the Rows/Data being populated into the ‘HubSpot Sync’ tab

- I’ve noticed that some changes made to the ‘HubSpot Sync’ tab AFTER the Workflow was launched has sometimes caused the mapping settings within the Workflow to break. Because of this, I chose to create this 2nd tab (“Formulas”) so that I could avoid making any changes to the “HubSpot Sync” tab.

- The ‘Formulas’ tab is simply mirroring whatever is on the ‘HubSpot Sync’ tab

- The column Headers for A, B, C, D are the same as the Headers on the ‘HubSpot Sync’ tab (Deal ID [A], Deal Name [B], Deal Stage [C], Amount [D])

- The following formulas are in place:

-- Cell A2: =‘HubSpot Sync’!A2

-- Cell A3: =‘HubSpot Sync’!A3

-- Cell B2: ‘HubSpot Sync’!B2

-- Cell B3: ‘HubSpot Sync’!B3

-- Cell C2: ‘HubSpot Sync’!C2

-- Cell C3: ‘HubSpot Sync’!C3

-- etc. etc. etc.

The problem is that whenever new Rows of data are added to the ‘HubSpot Sync’ tab, the corresponding formulas on the ‘Formulas’ tab are changed to reference other/random cells. The only way I can fix this is to re-drag the formulas in each column on the ‘Formulas’ tab down to the bottom of each Column so that the formulas will reference the correct cells on the ‘HubSpot Sync’ tab again.

Example:

- Let’s say I have 5 rows of data that have been synced to the ‘HubSpot Sync’ tab

- The ‘Formulas’ tab is showing the same information within those 5 Rows of data

- Then the workflow syncs a new Deal to the Google Sheet (Row #6)

- When I look at the ‘Formulas’ tab and view Row #6, it’s empty. When I check the formulas for the four cells in Row #6, I SHOULD be seeing the following:

-- Cell A6: =‘HubSpot Sync’!A6

-- Cell B6: =‘HubSpot Sync’!B6

-- Cell C6: =‘HubSpot Sync’!C6

-- Cell D6: =‘HubSpot Sync’!D6

BUT instead, I’m seeing something random like:

-- Cell A6: =‘HubSpot Sync’!A14

-- Cell B6: =‘HubSpot Sync’!B14

-- Cell C6: =‘HubSpot Sync’!C14

-- Cell D6: =‘HubSpot Sync’!D14

So again, to fix this, I have to highlight Cells A5 through D5, and drag their formulas down to the cells below on Row #6

Does anyone know why new Rows added to the ‘HubSpot Sync’ tab are causing the existing formulas on the ‘Formula’ tab to reference other/random cells from the ‘HubSpot Sync’ tab?

Thanks

2

Looking further into the issue, I believe the following is causing the formulas to change:

- When HubSpot sends data to the ‘HubSpot Sync’ tab, it doesn’t just place the value on the next available Row, but instead it inserts a NEW row above the next available row. This is causing the field reference formulas on the ‘Formulas’ tab to adjust so that they continue referencing the same cell as before. If HubSpot was syncing adding 1 new row at a time, then I would see the formulas on the ‘Formulas’ tab referencing the the cell on the ‘HubSpot Sync’ tab just below the desired cell that I want it to reference. Since HubSpot often updates the spreadsheet with multiple Deals, this causes a new row to be inserted for each Row that it wants to populate, which is why I’m seeing the formulas on the ‘Formula’ tab referencing sometimes 5, 10, 15, etc. fields below what the desired field actually is.

Does anyone know how to create a version of the following formula so that it will always remain exactly as I originally typed it, regardless of when new Rows are added to the data they’re referencing?

= ‘HubSpot Sync’!A2

Can I somehow lock this formula so that even if a new row is inserted above row #2, it will still continue to reference Row #2 and not be automatically changed to reference Cell A in Row #3?

3

For anyone experiencing this same issue, I believe I found the resolution:

- On the ‘Formulas’ tab, I inserted a new column at the beginning (Column A), and named it “Row Number”

- Starting on cell A2, I typed “2”, on cell A3, I typed “3”

- I continued this down the entire column: A2: “2”, A3: “3”, A4: “4”, A5: “5”, etc. etc.

- I then updated the formulas in Columns B, C, D, etc. using the ‘INDEX’ formula

- Previous Formula: =‘HubSpot Sync’!A2

- New Formula: =INDEX(‘HubSpot Sync’!A:A,$A2)

- I then applied the formula all of the way down to the bottom of Column A

- Then I applied the formula all of the way to the right within Row 2

- Lastly, I applied the formula all of the way down to the bottom so that all Columns in the ‘Formula’ tab were contain the updated formula

Now, when a new row is created on the ‘HubSpot Sync’ tab as a result of HubSpot sending additional data to the Spreadsheet, the formulas within the ‘Formulas’ tab remain unchanged.

4

Thanks for sharing these updates with us, @NG4!