CF7 & CF7 Google Sheets Connector plugin indirect cell reference issue

  • Resolved sillycar_wp

    (@sillycar_wp)


    7 years, 6 months ago

    Hi, 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:
    [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)”]
    However, when I send a form with this as my hidden variable, the target cell is blank.
    If I type the formula directly into a cell, it works, so I think the formula is ok, but something is causing it not to send to the cell or is messing with the formatting or something.

    If anyone has any suggestions on how to get these indirect references to send properly to my google sheet, that would be awesome!

    Thanks in advance for your time!

Viewing 2 replies - 1 through 2 (of 2 total)
  • Thread Starter sillycar_wp

    (@sillycar_wp)

    So, I did find a couple of solutions (In case it’s helpful to anyone else).
    The first was to not send the formulas as hidden variables, but to use ARRAYFORMULAS in the top row of a column.

    so, rather than send [hidden payment-amount default:”=If(X12>0,(65+25*Y12),0)”]
    I put this in the header of that column.
    =ARRAYFORMULA(IF(ROW(A1:A)=ROW(A1),”payment-amount”,IF(ISBLANK(X1:X),,If(X1:X>0,(65+25*Y1:Y),0))))
    [hidden payment-amount default:”=If(indirect(“X” & row())>0,(65+25*indirect(“Y” & row())),0)”]

    That worked fine, but I had another formula that used COUNTBLANK which does not work with an ARRAYFORMULA.

    At this point, I realized that I had an issue with formatting not being retained on new rows submitted by Contact Form 7.

    So then, in finding a solution to that, I took care of the COUNTBLANK formula issue too.
    https://productforums.google.com/forum/#!topic/docs/dlW6U6cHuKw
    This solution was to add an additional sheet with array formulas to pull each column over from the original sheet that CF7 submits to. In doing so, you can format a full sheet of cells that will constantly update when the original sheet is populated with new data. This also allowed me to put the COUNTBLANK formula in the top cell of a row with direct cell references and then paste it to the rest of the column so that when new rows are populated, they will use that formula that’s already there.

    Hope this helps someone else in the future!

    Thread Starter sillycar_wp

    (@sillycar_wp)

    I forgot to post the link where Karl_S at stack overflow helped me with the first part of this solution.

    https://stackoverflow.com/questions/45703372/google-sheets-cf7-google-sheets-connector-indirect-cell-reference-issue/45713735?noredirect=1#comment78444075_45713735

Viewing 2 replies - 1 through 2 (of 2 total)
  • The topic ‘CF7 & CF7 Google Sheets Connector plugin indirect cell reference issue’ is closed to new replies.