About Zapier Community
New to our Community? Get to know more about our space and how to make the most of your time here.

Code of Conduct Community FAQ's Zapier Common Questions What to Expect

Get Started With Zapier
New to Zapier? We have got you covered with our Zapier 101 Resources.

Zapier Essentials

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

Zapier Products
Learn more about Zapier's new products.

Zapier Tables Zapier Interfaces

Zapier Reading
All of our best learning resources can be found here.

Featured Articles Show & Tell AI Hub Zapier Tools Hub

Learning Groups
Excited to meet other builders? Check out our interest areas full of doers, dreamers, and in-betweeners.

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.

Best answer

#ERROR formula parse error when updating google sheet row and entering a CORRECT formula into a cell

  • 9 February 2024
  • 7 replies
  • 74 views
B

When a new row is added to the sheet, I want to update the row, so that a cell includes a formula. However, when zapier updates the sheet, the formula is inserted correctly but Google Sheets displays #ERROR in the cell.  It is saying there is a formula parse error. The formula syntax is correct though because all I have to do is click into the cell and hit enter again for the formula to work as expected.  Further more if I manually add the formula to the cell it works straight away.

 

This is the formula I’ve added in Zapier:
=DAYS(TODAY(),DATE(VALUE(MID("{{225758909__COL$A}}",7,4)),VALUE(MID("{{225758909__COL$A}}",4,2)),VALUE(MID("{{225758909__COL$A}}",1,2))) + TIME(VALUE(MID("{{225758909__COL$A}}",12,2)),VALUE(MID("{{225758909__COL$A}}",15,2)),0)

 

This is an example of how it turns out in google sheets:

=DAYS(TODAY(), DATE(VALUE(MID("09-02-2024 11:26 AM",7,4)), VALUE(MID("09-02-2024 11:26 AM",4,2)), VALUE(MID("09-02-2024 11:26 AM",1,2))) + TIME(VALUE(MID("09-02-2024 11:26 AM",12,2)), VALUE(MID("09-02-2024 11:26 AM",15,2)), 0))

 

I’ve tried using both = and + in zapier before the formula but the same result. Is this expected? Is there a workaround?

 

Zap Steps:

 

Zap Gsheet Cell
 

 

Output:
 

 

Again if i simply hit enter on the above cell without changing anything it works as intended

icon

Best answer by Bquestion 9 February 2024, 06:57

View original

7 replies

Troy Tessalone
Userlevel 7
Badge +14

Hi @Bquestion 
For us to have full context, post screenshots with how your Zap steps are outlined and configured.


Check your Zap Runs to see the DATA IN/OUT for each Zap step to help you troubleshoot.

https://zapier.com/app/history/

Have you tried asking ChatGPT for help with the formula?

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
B

@Troy Tessalone 

Hi Troy, I updated the first post with some screeshots.  Here is the Data in/Data out

Data In:

 

Data Out:

 

Again, the actual formula is correct because it works when I click on the cell and just hit enter.  I don’t change anything.  Yet something is causing it to not process properly the first time around?

Troy Tessalone
Userlevel 7
Badge +14

@Bquestion 

For us to have full context, post screenshots with how your Zap steps are outlined and configured.

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
B

@Troy Tessalone there is sensitive data.  What more context do you need specifically?  Everything is functioning correctly with the zap except for the fact that the cells initial input registers as an Error.

The steps are straightforward.  It simply checks that a row has been added, if so use the date in column A of the updated row to create a formula that will be inserted into column X of the row

Troy Tessalone
Userlevel 7
Badge +14

@Bquestion 

Wondering if the issue is with the + in the formula, perhaps that is not being encoded properly when sent to GSheets, but then when interacting with the formula in GSheets it’s handled correctly.

 

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
B

@Troy Tessalone 

Thanks for your help with this one I have found the issue. It turns out that the formula was indeed incorrect.  In my first post you can see the zapier formula is missing a bracket at the end.  It turns out Google Sheets is smart enough to add the bracket at the end when I hit enter on the cell.  D’oh

SamB
Rank
Userlevel 7
Badge +11

Thanks so much for following up here to share what the issue was @Bquestion! I’m so pleased you were able to fix the formula and get rid of that #Error error in Google Sheets - great work! 🙌
 
Sounds like you’re all set with things for now but if anything else comes up please do reach out to the Community again - we’re always happy to assist! 🙂

Reply


Terms & Conditions