Google Sheets & CF7 Google Sheets Connector - Indirect Cell Reference Issue

1
votes

I've searched all over the place for something similar to this and have been unable to figure out my issue. I'm guessing it's something with the CF7 Google Sheets Connector plugin and not with the Contact Form 7 plugin, but I'm taking a shot that someone may be able to help here as I've gotten no response on the CF7 Google Sheets Connector plugins' support forum.

Here's the issue quickly. I need to send a hidden formula to a cell in my google sheet. When I send this from my CF7 form:

[hidden payment-amount default:"=If(X12>0,(65+25*Y12),0)"]

it works fine and my cell is populated with the results of the formula. The problem is that I need an indirect cell reference for the X12 and Y12 parts because I don't want the value of X12 and Y12. I want the value of the cell in column X and Y for the current row.

My research shows that this should work:

[hidden payment-amount default:"=If(indirect("X" & row())>0,(65+25*indirect("Y" & row())),0)"]

And if I type that formula directly into my cell, it works fine.

However, when I send a form from my website with this as my hidden variable, the target cell is blank. I think the formula is ok, but something is causing it not to send to the cell or is messing with the formatting or possibly indirect is a reserved word that can't be sent, but I was unable to find documentation listing that it is.

If anyone has any suggestions on how to get these indirect references to send properly to my Google sheet, or a different way to write this formula, that would be awesome!

1
wordpressgoogle-sheets

1 Answers

0
votes

I do not know if CF7 will have issue with this approach, but if it does not, I would use the formula below in row 1 of the column where you want the formula applied and then there is no need to apply a formula to each cell.

=ARRAYFORMULA(IF(ROW(A1:A)=ROW(A1),"My Header",IF(ISBLANK(X1:X),,If(X1:X>0,(65+25*Y1:Y),0))))

the ARRAYFORMULA() function let's us work on an array and in this case we are applying a couple formulas to the entire row. You should recognize your formula in there, so working out from that point

IF(ISBLANK(X1:X),,If(X1:X>0,(65+25*Y1:Y),0))

Here I am checking to see if there is a value in column X. If there is not, I return no value. Note that I did not put "" here as in Google Spreadsheets that actually returns a value and can potentially confuse other formulas. If there is a value (If ISBLANK returns FALSE) then I apply your formula. This is all wrapped in another IF() function:

IF(ROW(A1:A)=ROW(A1),"My Header",IF(ISBLANK(X1:X),,If(X1:X>0,(65+25*Y1:Y),0)))

All this does is says If we are in Row A, return "MY Header" to supply a header to the column, otherwise, do the above.