Google Forms Quiz Response Sheet

Question

I have added a couple of columns to the Google Sheet Responses for a quiz created using Google Forms. When creating the response sheet (created in the "Responses" tab of the quiz, it automatically creates a column labeled "Score" that tells the number of correct responses. For example, If a student misses 2 questions on an exam with 25 questions the column will show "23/25". I added a column that takes that, and creates a percentage grade. I then use the new "Percentage Grade", in an additional column I have created that states "PASSED" only if the student made 100% on the quiz. NOTE: This is a safety test whereas they MUST make 100% to pass, and can take the quiz multiple times.

I am currently highlighting and dragging the formulas into the newly submitted responses in order to obtain the "Percentage Grade", and "PASSED" information, but would like to see if Sheets can do this automatically.

My question is this, is it possible to make Google Sheets automatically calculate the "Percentage Grade", and "PASSED" columns as the students submit their quiz as it does with the default "Score" column?

Answer 1

You could try using an ARRAY FUNCTION I haven't tried it for this use case at the moment but i have used it in a situation which uses the .appendRow() function which I'm pretty sure the form response uses hence why your formula isn't present. Instead of dragging down your formula:

1. Save it as an ARRAY Formula by holding CTRL while pressing enter to input the formula into the cell on the first Row you need it
2. Then copy that cell with CTRL+P
3. Using your Arrow Keys press down once to highlight the cell below
4. Then hold CTRL+SHIFT and press the down Arrow key. (assuming you're in an empty column that should have highlighted the remaining rows of that column in that sheet. If not keep pressing down till you reach as far as you want to accept responses. NOTE: this is not a good scenario to have. The form response sheet should be left alone as much as possible to ensure consistent behavior....current instructions not included of course)
5. press CTRL+V or CTRL+V to paste in the remaining portion of the array Formula This should now calculate the added row.

If not I would use something like =COUNTUNIQUE(FORM_RESPONSES!H2:H) in a separate sheet which should return the number of unique values in the entire H column. Or you could open the Script editor in the tools menu and try your hand at some javascript. Which could then even write an email and autom fill a templated word doc for you based on the conditions you mentioned.

I highly recommend that option as automation rewards you with time, and coding is a lot of fun and easier than you may think.

Use developers.google.com/sheets to learn more about Apps Script and to find a scenario or tutorial for most levels of skill including beginner