Wednesday, January 4, 2012

yogia_Recreating Form from Form Responses Sheet For Select Fields Per Specified Condition

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user ggamauf said:
with your help i created the following -> 
=arrayformula(if(row(A:A)=1,"Click on Link in corresponding row to recreate Form as was originally submitted",if(len(A:A),hyperlink("https://spreadsheets.google.com/spreadsheet/viewform?formkey=dF9MaXlkV2RQS1F5NFcxWDFBZmZwM3c6MQ&entry_0="&b1:b&"&entry_1="&c1:c&"&entry_2="&d1:d&"&entry_3="&e1:e&"&entry_4="&f1:f&"&entry_5="&g1:g&"&entry_6="&h1:h&"","recreate form"),iferror(1/0))))
now i need to say (eg): 
only if field b is filled display fields c and d
or only if field d is filled display fields a, b and c (always WITH contents)
what's the formaility for doing this?
can i read this somewhere?
i don't even know: is this javascript?
-------------------------------------------
we are using Google spreasheet formulas
following is the solution to the problem for the first part of user's question ... solution to the second part can be adapted from the solution to the first part of the question


Please note:
1) that per the specified condition only those records in which the field for Question 1 is not blank have been prepped for recreating the Form as originally submitted
2) and for select records per 1) answers only for Questions 1,2, and 3 will be filled in the Form

here is the Form that will be seen if the hyperlink recreate form for record 3 is clicked:


4 comments:

  1. yogi,
    i need even more sophistication ;-(
    in above example:
    empty questions 4 to 7 MUST N'T b displayed.
    i'm using this for something called 'sytemic cobsebsualition' (-> http://sk-prinzip.net/).
    the point is that users must be guided through it very strictly.

    ReplyDelete
  2. alternatively (to my comment above) i could do with preventing input into some fields, depending on the content of others.

    ReplyDelete
  3. Hi Gerald:

    Google Form is very simple -- so with the type of formulas we are discussing you can only do so much and then again the approach gets pretty convoluted.

    With the type of control you want from the Form output, I suggest you explore using Google Apps Script and visit Google Apps Script Help forum as well.

    Good Luck With Your Project.

    Cheers!
    Yogi
    Cloud Computing -- Google Docs Way
    yogi--anand-consulting.blogspot.com

    ReplyDelete