Best answer

Adding dynamic formulas to a Google Sheet cell

  • 1 December 2020
  • 5 replies
  • 2324 views

Userlevel 1

I am transferring data from one application to a Google Sheet that I’m preparing for an automated dashboard.

The first 7 fields being transferred are raw data.  The remaining fields are calculations that in the past I did manually in Excel, using excel functions, such as right, Left, Vlookup, and Year.

My first thought was to copy the formulas as they are shown in excel into the appropriate fields in Zapier, so that when a new row of data was created, the formulas would also be created.  The problem is that the cell references do not update to the new row reference.  It stays on row 2, instead of advancing to the new row.

Any suggestions on how to create a formula where the cell reference will change to reflect a new row? 

icon

Best answer by GetUWired 1 December 2020, 18:58

View original

This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

5 replies

Userlevel 7
Badge +14

[ADVANCED] Use ARRAYFORMULA in the GSheet, not in the Zap.

Article: https://support.google.com/docs/answer/3093275?hl=en

Userlevel 7
Badge +12

Hi @Loch 

 

I have actually just discovered a new tip that I believe will work for you. Was just getting ready to post in in the Tips and Tricks section when I stumbled on your question. I believe this will help you greatly! 

 

You will keep your create action that creates the new row but remove the formula columns from that step in Zapier. I’ve shown a screenshot of some test data.

Immediately after this step, add another step to Update a Row. Use the custom tab to insert the row that was created in the previous step for the row to be updated then when you type in your formulas you can insert that same row Id where you would usually reference it in your formula. Voila! I hope that helps!

 

Userlevel 1

GetWired

Thx, you have got me half way there.  The data is now in its proper place.  Now I think that the spreadsheet formulas that I normally use are having a hard time analyzing the data.

Userlevel 1

Update on the above process.  It worked as described.  Thanks again, GetWired.  The second step of the problem was with the formulas not accepting the data.  I was able to use the Formatter Action to modify the data into something that was acceptable to the formula.

Userlevel 7
Badge +12

@Loch Glad to hear everything is working and glad to have helped!