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
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
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)))
ReplyDeleteHi David:
ReplyDeleteThanks ... 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
Based on suggestion from Dave, I have added the following formula in cell F1 of sheet ResponseTable ...
ReplyDelete=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.
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.
ReplyDeleteI 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.
ReplyDeleteIf this change causes some confusion ... I am sorry about that.
Cheers!
Yogi
Yogi,
ReplyDeleteI 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
Hi Dawn:
ReplyDeleteIf 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
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??
ReplyDeleteHi Andrea:
ReplyDeletePlease 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
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?
ReplyDeleteIdeally, submit modifies the cells that were the source for the data that populated the form.
Hi weecabin:
ReplyDeleteYou 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
Hi Yogi
ReplyDeleteThanks 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
Hi Josh:
ReplyDeleteI 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
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.
ReplyDeleteHi amayville:
ReplyDeleteYes ... 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
Yogia
ReplyDeletegreat 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
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?
ReplyDeleteHi Angela:
ReplyDeleteThere 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
Hi Yogi,
ReplyDeleteIm 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
I'm running into the same problem with my form. If you can help that would be awesome.
DeleteGreat spreadsheet! Thanks a lot.
Hi Yogi,
ReplyDeleteThanks 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))))
Never mind! This is not a problem. Must have had something else wrong in the formula. Thanks again for sharing this information!
DeleteHi Yogi,
ReplyDeleteFor 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.
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
DeleteStill playing with my forms. I think that a section header will shift all of your information down. This could be causing the problem.
DeleteHi Yogi,
ReplyDeleteThanks 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
Hi @Yogia,
ReplyDeleteThank 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!
Ok! This works! Thanks a lot!
ReplyDeleteYou Are Very welcome Sergeiy ... Now Let Us Keep Googling.
ReplyDeleteCheers!
Yogi
Cloud Computing -- Google Docs Way
yogi--anand-consulting.blogspot.com
Hi Yogi,
ReplyDeleteThank 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
Hi Laura:
ReplyDeleteI 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
Hi Jennifer:
ReplyDeleteSorry, 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
Hi ms June:
ReplyDeleteSorry, 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
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.
ReplyDeleteI 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
Hi Shelter Animal Allies of Louisiana:
ReplyDeleteAs 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
How do i adapt your formula to my spread sheet.. this is very confusing
ReplyDeleteHi Tory:
ReplyDeleteI 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
Hi Yogi,
ReplyDeleteI 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
Hi Joerg:
ReplyDeleteThe 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
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
DeleteAny help you could provide would be greatly appreciated. Thank you for your time, Richard
Hi Richard:
ReplyDeleteLet 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
Hiya Yogi
ReplyDeleteAfter 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
Hi Nic:
ReplyDeleteLet 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