Skip to main content

Get Support
Have a question? Let's get you an answer!

How Do I...? Troubleshooting Code & Webhooks Developer Zone

Other Help Resources
Wait, there's more! Check out these Community sourced spaces for additional resources.

Zapier Help Center Hire an Expert

Learning Resources
All of our best learning resources can be found here.

Featured Articles Show & Tell Zapier's products AI Hub Zapier Tools Hub

Share your knowledge
Give back to the Community by sharing your automation wins and helping to answer questions.

Share your success See unanswered questions User Groups

Other Learning Resources
Some of our favorite additional ways to upskill your Zapier knowledge.

Zapier Learn Zapier Blog Zapier Webinars

Product Updates
Check out what is new and upcoming with Zapier with our regular product updates.

Product Updates

Early Access Program
Want to join Early Access? Learn more here.

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? 

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

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

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!

 

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.

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.

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

 

Terms & Conditions