Form submission results in skipped rows in Google Sheets formulas

Question

I have a Google Form that populates a Google Sheet. The way it does that is not particular elegant (not a complaint - gift horse and all that... but it's the reason for this questions!), e.g. if there are three check-boxes in the form, Google dumps the text for each option into the relevant cell. I.e.:

__ "I care about delivery"
__ "I care about logistics"
__ "I care about nothing at all"

If the user selects the first and third check-box, the cell in the spreadsheet now reads:

"I care about delivery, I care about nothing at all"

I created another sheet in the same document, and wrote some basic evaluation rules such as

=if(iserror(FIND("delivery",'Form responses 1'!G5)),"No","Yes")

I populated the entire second sheet with these rules, and it works just as intended - until a new form entry is submitted. Form submission results in Google writing it correctly into the original "Form responses" sheet... but on my new sheet, the corresponding line vanishes.

In the example, if Line 5 was the last line filled in by the form, in the new sheet the next row makes reference to A6, B6 etc. After I submit the new form, the new sheet formulas change from the (populated) A5, B5... to A7, B7... - so the new data is not captured in my nicer representation.

Does anyone understand what is going on here? And, even better, would anyone be able to suggest a fix?

When Google Form responses are submitted, the resulting response row is inserted into the destination spreadsheet. Any predefined formulas maintain their original references - cell A6 became cell A7 when the 5th response was submitted. so your formulas referencing A6 now reference A7, and no cell could have referenced the current cell A6, since it didn't exist previously. The solution is to use a dynamic/open-ended formula / query of the response destination, and not hard-coded range references — tehhowch
Thank you tehhowch, that helped me get my head around what is going on. Some of the fields I copy straight across, and on some I do some substitutions. I could not make that work with the ArrayFormula, but a For-Loop might actually do the trick in a script behind the scenes. I'll be reporting back when I find a solid solution! — sheepsky

pnuts pnuts · Accepted Answer · 2018-10-02T13:11:46

When your Form is updated and a new row added your formula continues to reference G5 which is then a cell above (higher up than) the one you intend. To avoid having to chase the 'G5 cell' as it moves around the Form responses, apply an array version of your formula, say in Row1:

=ArrayFormula(if(iserror(FIND("delivery",'Form Responses 1'!G:G)),"No","Yes"))

Form submission results in skipped rows in Google Sheets formulas

1 Answers