How to create a dynamic Google Form for entering tutoring logs

October 28, 2015 11:34 AM

Our students attend tutoring before and after school, and we need documentation of their attendance. We currently use a paper-based system and would like to move to a system based in Google Forms.

Example form and data.

We have two issues:

1. When a student enters her ID number, we would like for her name to be shown somewhere on the form so that she can verify that she entered her ID number correctly.

2. Also, we would like a way to match two lines on the responses sheet together and subtract the difference to find the time elapsed during tutoring. I know how to subtract times when all of the data is on one line in the form. The two separate lines is the part that's confusing me.

posted by allison00 to Technology (2 answers total)

Best answer: For the ID issue, I would have them enter their name and have it map to an ID in the back with a vlookup.

For the other issue, I was just looking at it and you could use a pivot table to get the time values into one line with the student ID - for "rows" use the ID column and for values use the time column summarized by min and the time column again summarized by max. You can then add some columns next to the pivot table to do the math and identity mapping.
posted by bleep at 1:02 PM on October 28, 2015

Also wanted to mention I wouldn't get too deep into google sheets hacking craziness - if what you wind up with is not relatively straightforward, documented, and simple to explain then it WILL break and mess up all your stuff. I'm saying this from experience.
posted by bleep at 1:06 PM on October 28, 2015 [1 favorite]