Friday, April 29, 2011

yogia_Recreating Form from ResponseTable

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

I have shared this spreadsheet with a number of my colleagues on the Google Help Forum. Recently, I started to having difficulty sharing this with more viewers in response to their request. So I am adding this to my blog and also share it for any one with the link :
link to the Blog post ... http://yogi--anand-consulting.blogspot.com/2011/04/yogiarecreating-form-from-responsetable.html
link to the spreadsheet: https://spreadsheets.google.com/ccc?key=0AkHBcyclu11AdGZsUVpTQi1EOFZsNGVWaGhzZTM3WHc&hl=en&authkey=CJPsiPgH



Following is a view of the Blank Form that pops up when you click on cell F1 of ResponseTableWithRecreateForm


Following is a view of the Form as was Filled-In for ID 1003 that pops up when you click on cell H7 of ResponseTableWithRecreateForm


And in the following I present a Summary of Responses

43 comments:

  1. Hi Yogi, also if you put in a formula in ResponseTable!F1 then this recreates the form: =arrayformula(if(len(A1:A),hyperlink("https://spreadsheets.google.com/spreadsheet/viewform?formkey=dGUwamRVSHpiNDZZQzgtdlBaeV9TY2c6MA&entry_1="&B1:B&"&entry_2="&C1:C&"&entry_3="&D1:D&"&entry_4="&E1:E&"","form"),iferror(1/0)))

    ReplyDelete
  2. Hi David:

    Thanks ... Great Idea!
    I am going to put the formula in cells F2 and down to recreate the Form not only in substance but in Form as well.
    Once again Thanks for the great idea ... this will definitely be a great enhancement.

    Cheers!
    Yogi

    ReplyDelete
  3. Based on suggestion from Dave, I have added the following formula in cell F1 of sheet ResponseTable ...

    =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=dGUwamRVSHpiNDZZQzgtdlBaeV9TY2c6MA&entry_1="&B1:B&"&entry_2="&C1:C&"&entry_3="&D1:D&"&entry_4="&E1:E&"","recreate form"),iferror(1/0))))

    Now clicking on the link recreate form the Form for the corresponding row is displayed as was originally submitted.

    ReplyDelete
  4. Well, I actually added a sheet named ResponseTableWithRecreateForm and added formulas to access the Blank Form as well as the Form as was filled-in before submittal for each record ... I think this arrangement seems to suit better.

    ReplyDelete
  5. I had to recreate the Form because the original Form had gotten corrupted and what I had previously called sheet ResponseTable is now sheet called Form Responses; also I have integrated recreation of Form features within the sheet named Form Responses.

    If this change causes some confusion ... I am sorry about that.

    Cheers!
    Yogi

    ReplyDelete
  6. Yogi,
    I am a principal who is trying to recreate the form with information populated in it in order to print...although I see that you are using a formula to do so... I am unclear of how to extrapolate that in order to make it work for my form

    ReplyDelete
  7. Hi Dawn:

    If you care to share your spreadsheet and tell me what exactly you are trying to to do ... I will be glad to see if I can help.

    Cheers!
    Yogi

    ReplyDelete
  8. I too am trying to recreate what you did here, but I am confused about what I need to do... do I just need to paste your formula in a cell somewhere??

    ReplyDelete
  9. Hi Andrea:

    Please note the formulas I have used in column F of Form Responses sheet that create a hyperlink (for each row) which when clicked takes one to the submitted Form entry ... please take another look and see if it makes sense ... otherwise we can chat about it a little more so what I am saying is clear and makes sense.

    Cheers!
    Yogi

    ReplyDelete
  10. When I press submit in the new form, the data is entered as a new row in the spread sheet. Is that the intended functionality? If not, any idea why submit creates a new row for me?

    Ideally, submit modifies the cells that were the source for the data that populated the form.

    ReplyDelete
  11. Hi weecabin:

    You are correct in that every Form submittal is logged in a new row -- as of this time that is the intended functionality for the Google Form.

    If there were to be only one intended submittal from an individual, one can FILTER or QUERY the Form Responses sheet for the latest submittal by the individual -- will that work for you? ... and if you want to discuss this further please write back and then let us take it from there.

    Cheers!
    Yogi

    ReplyDelete
  12. Hi Yogi
    Thanks for the response to my question. Should I copy and paste the rule you created to serve my purposes? Or does it need to be edited somehow to suit the form I created (i.e. with a different # of columns)?
    Tx!
    Josh

    ReplyDelete
  13. Hi Josh:

    I had referred to my workaround solution if that is something that would work for you ... if so you can adapt the formulas that I have posted to suit your situation.

    I hope this helps. Please post back if you need to discuss this further.

    Cheers!
    Yogi
    Cloud Computing -- Google Docs Way

    ReplyDelete
  14. Is there a formula that will just list the entries without putting them in the original form? In other words can I have a link like you do that will create a document that contains the entries listed? I need to have a row put into a form that can be emailed to parents or printed.

    ReplyDelete
  15. Hi amayville:

    Yes ... did you look at the other sheets in my blog post where I pull the data from Responses Table for a given ID or last submitted ID.

    Let me know if this helps ... or give me a link to your spreadsheet telling me what you are intending to accomplish.

    Cheers!
    Yogi

    ReplyDelete
  16. Yogia

    great information. i can't believe this isn't a built in function! I'm having trouble getting the code to work for my page. Have you tried recreating a form that uses checkboxess or lists? i assume the entry type is different but i don't know how. thank you

    ReplyDelete
  17. I need to receive a copy of the form by email each time it is submitted so I can file it into the corresponding outlook folder. Can you help me do this? The info above is a little over my head. Or should I just use another form program?

    ReplyDelete
  18. Hi Angela:

    There is no direct way to get a copy of the Form as submitted. You may want to look into whether you can do this with Google Apps Script.
    However, if you are interested in the substance of what was submitted, you can easily extract that information from the so called Form Responses sheet ... and you may also arrange the data into a simulated Form instead of retrieving it as a row -- for an illustration see the sheet named FormForLatestSubmittalID in my blog post.

    Whether you should use another Form program ... it all depends on your project needs and preferences that will guide you whether you should choose a new program or continue to use Google Form with some extra workaround(s).

    Cheers!
    Yogi

    ReplyDelete
  19. Hi Yogi,
    Im trying to modify your formula here to fit my own spreadsheet, and Ive replaced the hyperlink and included the other responses that I want to re-enter into the form, but as of right now clicking on the 'recreate form' list just populates a blank form. I have a feeling Im using the wrong hyperlink but can't be sure. I would appreciate any help!

    =arrayformula(if(row(A:A)=1,"open printable form",if(len(A:A),hyperlink("https://docs.google.com/spreadsheet/viewform?formkey=dFBNX0xudkF2TmFBV09XTW92YmdNa3c6MQ#gid=0&entry_1="&B1:B&"&entry_2="&C1:C&"&entry_3="&D1:D&"&entry_4="&E1:E&"&entry_5="&F1:F&"&entry_6="&G1:G&"&entry_7="&H1:H&"&entry_8="&I1:I&"","recreate form"),iferror(1/0))))

    Thanks - nate

    ReplyDelete
    Replies
    1. I'm running into the same problem with my form. If you can help that would be awesome.

      Great spreadsheet! Thanks a lot.

      Delete
  20. Hi Yogi,
    Thanks for posting this! It is by far the most helpful solution I've found for printing Google forms as forms rather than spreadsheets. Our form is rather long so I am running into problems once I start to extend the formula for entry_10 and higher. Here is my formula. Is there a way to fix this? Thanks so much! - Joanna

    =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=dC1aQU96X0tBUzhWekMxSTZ3LWxQVXc6MQ&entry_1="&B1:B&"&entry_2="&C1:C&"&entry_3="&D1:D&"&entry_4="&E1:E&"&entry_5="&F1:F&"&entry_6="&G1:G&"&entry_7="&H1:H&"&entry_8="&I1:I&"&entry_9="&J1:J&"&entry_10="&K1:K&"&entry_11="&L1:L&"","recreate form"),iferror(1/0))))

    ReplyDelete
    Replies
    1. Never mind! This is not a problem. Must have had something else wrong in the formula. Thanks again for sharing this information!

      Delete
  21. Hi Yogi,

    For some reason, the first field gets skipped when recreating my form. Everything is moved down in the form by one. Do you have any insight as to what would cause that? I can share my doc with you temporarily if that would be helpful. Many thanks for your post on this - it's certainly something I hope they build in as a feature that would scale automatically as you add/delete fields.

    ReplyDelete
    Replies
    1. Hi Bruce. I ran into the same problem. What I did to work around it was create a second "print only" form with the same fields, but one additional blank field at the top. Then I used the Form ID for the "print only" form in the formula, but I pasted the formula into the actual/first spreadsheet. When the form goes live, I will ask people to fill out using the link from the actual/first form. The "print only" is only for printing purposes. I hope this helps. - Joanna

      Delete
    2. Still playing with my forms. I think that a section header will shift all of your information down. This could be causing the problem.

      Delete
  22. Hi Yogi,

    Thanks so much for linking me to your blog through the google docs forum. What you've done here is exactly what I am trying to accomplish. I have no clue, however, how to adapt your function formula to my form. If I share my spreadsheet with you, can you help me do this?

    Thanks,

    Jennifer

    ReplyDelete
  23. Hi @Yogia,

    Thank you for this post. My only problem is that when I copy your formula into my spreadsheet, it fills the submitted information into your sample spreadsheet instead of my own. When I tried to replace the link with the link to my live form, the formula no longer filled in the form with the submitted answers. Please advise.

    Thanks!

    ReplyDelete
  24. You Are Very welcome Sergeiy ... Now Let Us Keep Googling.

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

    ReplyDelete
  25. Hi Yogi,
    Thank you so much for posting this information! I think I'm just having the same issue as others. When I swap out your link for the link to my spreadsheet in the formula then the "recreate form" link just takes me to my blank form. Is there a step I'm missing? Here is the formula I have with the link to my google form in it.

    =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://docs.google.com/a/umich.edu/spreadsheet/viewform?formkey=dHJ0TVlPRHZPeU16LXdsSDlaVVg3amc6MQ#gid=0&entry_1="&B1:B&"&entry_2="&C1:C&"&entry_3="&D1:D&"&entry_4="&E1:E&"","recreate form"),iferror(1/0)))

    I'd be happy to share the document with you as well if that helps.

    Thanks again!
    Laura

    ReplyDelete
  26. Hi Laura:

    I have not looked into your formula ... however, it will help if you can share your spreadsheet.

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

    ReplyDelete
  27. Hi Jennifer:

    Sorry, I had missed your comment ... Yes, if you share your spreadsheet, I will be glad to have a look.

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

    ReplyDelete
  28. Hi ms June:

    Sorry, I had missed seeing your comment ... if you care to share your spreadsheet I can have a look where the problem might be.

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

    ReplyDelete
  29. I wish this was built in (for us dummies). All of this is so confusing. I need to see my form's individual responses (preferrably as a printable pdf). I'm great at following easy step-by-step instructions, but can't figure out where to start when reading what's been posted.

    I was proud of myself just for being to create a form in the first place *sigh*

    Here's my form: https://docs.google.com/spreadsheet/viewform?formkey=dFZ4dFYxYTU0N3RkbkxNOVdfN0NXWFE6MQ#gid=0

    ReplyDelete
  30. Hi Shelter Animal Allies of Louisiana:

    As of now (Dec-13-2012) there is no direct way of printing Form responses as submitted ... Google spreadsheet is continually being improved ... so you never know the feature to print Form responses as submitted might be added.

    But for now, my WorkAround should do -- if you need help using my WorkAround. please share your spreadsheet associated with the Form (that is where the responses submitted via the Form are logged) and tell me what exactly you are trying to accomplish and then let us take it from there.

    Make It A Great One.
    Cheers!
    Yogi
    Cloud Computing -- Google Docs Way
    yogi--anand-consulting.blogspot.com

    ReplyDelete
  31. How do i adapt your formula to my spread sheet.. this is very confusing

    ReplyDelete
  32. Hi Tory:

    I am sorry to hear that you are having difficulty using this concept ... I do understand that this is a convoluted approach and can be confusing.

    You can contact me via vcita.com/yogi.anand if you want me to work with you on your project.

    Make It A Great One.
    Cheers!
    Yogi
    Cloud Computing -- Google Docs Way
    yogi--anand-consulting.blogspot.com

    ReplyDelete
  33. Hi Yogi,
    I was so glad to find your how-to here.
    I used your "recreate form" link and it works great. It took me a while to match all the right entries with the corresponding 20-plus columns in the spreadsheet (multiple edits and moving questions up and down in the form does not help! ), but then I ran into the problem of not being able to re-populate radio buttons in a grid ?!

    Using the regular &entry_4="&D1:D&" does not work.
    Could you help ?
    Is there a different syntax that I need to use ?

    Sincerely,
    Joerg Henkel

    ReplyDelete
  34. Hi Joerg:

    The example I used in my blog posts did not consider the case of option (radio) buttons -- I can't say I will have a solution for you ... but if I do, I will let you know.

    Make It A Great One.
    Cheers!
    Yogi
    Cloud Computing -- Google Docs Way
    yogi--anand-consulting.blogspot.com

    ReplyDelete
    Replies
    1. I think I am experiencing the same issues as some of these other people. I am trying to get spreadsheet responses to be recreated into the existing form that was originally submitted so that these forms can be printed. It is my understanding that I can edit the script that you have created to include my url for the spreadsheet and then copy this into a cell in my table. Because it is not working it is my assumption that I am still doing something wrong. The spreadsheet I am working with can be found here: https://docs.google.com/a/wolfapps.wolfcreek.ab.ca/spreadsheet/ccc?key=0AqBNzh6t2j5udEhUbEdFT19vMjJhVHhWczhRWTltSnc&entry_1=Teacher&entry_2=electrons&entry_3=battery&entry_4=Minus+to+the+plus#gid=0

      Any help you could provide would be greatly appreciated. Thank you for your time, Richard

      Delete
  35. Hi Richard:

    Let me share couple of thoughts with you

    are you interested in extracting the information that was submitted via a Form

    1) substance wise
    or
    2) both substance and format wise (meaning recreating the form as it was submitted)

    if it is only the substance that you are interested in, it is a lot simpler -- and I have illustrated methodology for this in my blog post(s) as well

    or do you want to recreate the form 'as was submitted' the main theme of this post?

    so tell me your objective with a clear sample of what you want to accomplish
    and then let us take it from there.


    Make It A Great One
    Cheers!
    Yogi
    Cloud Computing -- Google Docs Way
    yogi--anand-consulting.blogspot.com

    ReplyDelete
  36. Hiya Yogi

    After several months of googling forms and scripts I was delighted to land here and discover your dedicated and inspiring work! Your solution is just what I was looking for, however have google now changed their approach? i.e. I get ../../KEY/viewform as opposed to being able to use ../../viewform?formkey=KEY. When editing a previously submitted form the string hides the content i.e. ../../KEY/viewform?edit=ChM2MTI1MzA4MzczNTI5MTEyODExELbV_KKNkrqjcw. Likely I am missing something, hope you can shed soem light, many thanks, Nic Lejeune

    ReplyDelete
  37. Hi Nic:

    Let me check it out and see what is happening.

    Make It A Great One
    Cheers!
    Yogi
    Cloud Computing -- Google Docs Way
    yogi--anand-consulting.blogspot.com

    ReplyDelete