Skip to main content

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

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! 🙂


@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


@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 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


@Bquestion 

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


@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?


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?


Reply