Sunday, December 30, 2012

yogi_Facilitate A Student To Enter StudentID Via Form And Read The Score in A Published Spreadsheet


                                          Google Spreadsheet   Post  #947
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Dec 30, 2012
user AndrewHill (thread) and Uwe Waldmann said:(https://productforums.google.com/forum/#!mydiscussions/docs/D5ekeKRi9PI)
Hi Yogi!
I've just come accross this thread as I was having the same problem like Andrew. Your solution sounds good, but I'm not sure if I got it right. Could you maybe describe the two steps a bit more precisely?
That would be great!
Cheers,
Uwe
---
Thanks for providing me more details. I have actually already tried to follow your recommendations. To make it a bit easier, I have created a form which is similar to mine (but in English):https://docs.google.com/spreadsheet/ccc?key=0Ag80lLrCqkKrdEpBbHpRSk1iclhiNjNkdkM4N29UeHc#gid=0

1) Is it possible to embed a "dynamic spreadsheet" (one in which the participant can still insert the ID) into a website? I tried to embed my spreadhseet but was only able to embed a "static" (one in which nothing can be changed).

2) In this form you will also see a problem, I have posted under another thread (https://productforums.google.com/forum/#!msg/docs/KQkISccbH2k/9RdX9g_E_OoJ) This might also have affected Andrew. After I have seen what you posted under other threads, you might also be able to give an advice on that.

3) For some reason the vlookup-function in sheet2 also didn't work, it did in my original sheet...

Final question for my interest: Do you think it's easier to give feedback via e-mail? Are there possibilities to write nice e-mails (design) via scripts or is that pretty complicated? 

Thank you already for your help! You are doing a great job!
Uwe
---
So far I have been able to answer some of my questions myself by checking out some forums and Yogi's blog.

The solution to point no. 2 is an arrayformula like this: =arrayformula(if(C2:C="agree";3;if(C2:C="partly agree";2;if(C2:C="disagree";1;0)))
I have been able to claculate means with a formula like this: =arrayformula(if(row(A:B)="test";0;(H2:H+J2:J)/2))    (I still wonder if there isn't an easier way)
The vlookup-function finally also works: =vlookup($C$4,Sheet1!$B$2:$O$100,11,FALSE)

There are two things which still don't work: 
1) the calculation of standard deviations with an arrayformula (no idea how a solution coluld look like)
2) I still wonder if it's possible to embed a "dynamic" sheet in a website in which the user can enter his ID but change nothing else...

Would be great to get some help on that.
-------------------------------------------------------------------------------------------------------
Uwe has got a whole slew of questions ... he has got his whole project wrapped in here ... normally one can only address a specific technical question via Google Docs help forum, but let me give it a shot

this is the sheet where the student student data is logged in ... then I have computed columns to the right shown with light brown colored background ... this is the main sheet from which the student score would be drawn and published ... this sheet is the work-horse but this will not need to be published.

here is the Form via which a student will s8ubmit his/her ID: once a student has submitted her/his ID, the student can then see her/his score via the following