Hey everyone! I’m having a hard time with the CF7 Google Sheets Connector plugin. I’m trying to send a hidden formula to a cell in my Google sheet, but it’s not working as expected.
Here’s what I’ve got:
[hidden payment-amount default:"=If(indirect("X" & row())>0,(65+25*indirect("Y" & row())),0)"]
This formula works fine when I type it directly into a cell in Google Sheets. But when I send it through my Contact Form 7, the target cell ends up blank.
I need to use indirect cell references because I want the values from the current row in columns X and Y. The simple version without indirect works fine:
[hidden payment-amount default:"=If(X12>0,(65+25*Y12),0)"]
But that’s not dynamic enough for what I need. Any ideas on how to make this work? Maybe there’s a different way to write the formula that would play nice with the CF7 Google Sheets Connector?
I’ve already checked the plugin’s support forum, but no luck there. Any help would be awesome!
I’ve encountered this issue as well. The problem likely stems from how CF7 Google Sheets Connector handles complex formulas. A workaround I’ve found effective is to use Google Sheets’ ARRAYFORMULA function. Try modifying your sheet to include this formula in the first row of your target column:
=ARRAYFORMULA(IF(X2:X>0, 65+25*Y2:Y, 0))
This eliminates the need for row-specific formulas in CF7. Instead, you can simply send the raw data to columns X and Y, and let the sheet handle the calculations. It’s more efficient and avoids the compatibility issues you’re facing with indirect references.
I’ve dealt with similar CF7 and Google Sheets integration hiccups before. One thing that’s worked for me is breaking down the formula into smaller parts and using separate hidden fields. Try something like this:
[hidden row-reference default:“=row()”]
[hidden x-value default:“=indirect("X" & [row-reference])”]
[hidden y-value default:“=indirect("Y" & [row-reference])”]
[hidden payment-amount default:“=If([x-value]>0,(65+25*[y-value]),0)”]
This approach simplifies each step and might help the plugin process the formula correctly. It’s a bit more verbose, but it’s easier to troubleshoot if something goes wrong. Plus, you can always hide these intermediate fields in your form if needed.
If that doesn’t work, you might want to consider using Google Apps Script to handle the calculations on the sheet side instead of relying on CF7 to send complex formulas. It’s a bit more work upfront but can be more reliable in the long run.
hey charlie, i’ve run into similar issues before. have u tried escaping the quotes in ur formula? like this:
[hidden payment-amount default:"=If(indirect(\"X\" & row())>0,(65+25*indirect(\"Y\" & row())),0)"]
sometimes the plugin gets confused with nested quotes. give that a shot and lemme know if it helps!