Skip to main content
Best answer

Adding dynamic formulas to a Google Sheet cell

  • December 1, 2020
  • 5 replies
  • 3053 views

  • Beginner
  • 2 replies

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? 

Best answer by GetUWiredBest answer by GetUWired

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!

 

View original
Did this topic help you find an answer to your question?
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

Troy Tessalone
Forum|alt.badge.img+14

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

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


GetUWired
Forum|alt.badge.img+12
  • Zapier Expert
  • 1030 replies
  • Answer
  • December 1, 2020

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!

 


  • Author
  • Beginner
  • 2 replies
  • December 1, 2020

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.


  • Author
  • Beginner
  • 2 replies
  • December 2, 2020

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.


GetUWired
Forum|alt.badge.img+12
  • Zapier Expert
  • 1030 replies
  • December 2, 2020

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