yogi_From Form Responses Sheet Split Student Data And Grades For Assignment By Date Into Sheets By Teacher Name

                                         Google Spreadsheet   Post  #1500
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-27-2014
post by Christopher Salem (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/nB15rBNrn1Q)
Import Range & Query Function for Classroom Gradebook

Hello,

I'm an elementary teacher trying to simplify my life by automating some parts of my classroom management with Google Drive.

Currently, I have students submit some data (spelling test scores, homework answers, etc.) using a Google Form. This dumps all the data for me into a spreadsheet. Right now, I'm cutting and pasting this manually into my grade book. I would like to automatically import the data from the Form Responses sheet into my gradebook sheet. I believe I am trying to use the ImportRange and Query functions to do this. However, I cannot get this to work correctly.

Because a student might submit multiple times to a form (multiple homework assignments or test, etc.), I need to be able to get the function to pull data for a particular student, and then pivot the data into new columns (so each assignment is in a separate column).

Currently my formula looks like this:

=query(importrange("Sheet14!A1:E2000"), "select Col5 where Col3 contains '1' pivot Col5 ORDER BY Col1", 1)

Column 5 is the test score. Column 3 is the Student's ID Number (I was trying to use Student #1 in my test) and Column 1 is the Timestamp. The message I receive is error: Invalid query: Cannot use PIVOT when no aggregations are defined in SELECT.

I have tried a couple of different things to aggregate in my SELECT, but to no avail. When I remove the "pivot" entirely, it says Col5 does not exist in this table.

Second question, I would like to somehow alter the formula so that I do not need to retype the formula for every student, replacing their ID number, but rather have the function automatically fill for each unique student (Putting all of Student 1's data in Row 1, Student 2's in Row 2, etc.) so I can see my whole class in one sheet.

My understanding of how to do this is basic at best, but I'm a quick learner! Thanks for any help or let me know if I can give more information or clarify anything here.

Thanks!

---

Yogi,

Thanks for your reply!

I created a test spreadsheet that mimics what I am trying to do.

In the spreadsheet, there are four sheets. The first three are for each class that I have during the day. The fourth sheet is the Form Response sheet that is automatically populated when students submit their scores.

What I want to do is take the data in "Scores" column from the sheet "Form Responses 1" and automatically fill it into new columns in the appropriate homeroom grade sheet.

I want the spreadsheet to recognize a kid (based on their Student Number and their Teacher name) and then pull their score and put it into the correct spreadsheet. So, for the scores that were submitted on 11/21/2013, I want those to be in a new column that is labeled "11/21/2013". Or, if it is better, I can add a new column to the Form Response for the name of the assignment, and then they would be labeled from that.

For example, in the sheet "Salem Homeroom", cell D2 should read "10", because that is the score that the Student Ben earned from his 11/21/2013 assignment. Cell E2 should read "5" because that is what he earned from the assignment on 12/5/2013.

Thank you! Please let me know if this is not clear or if you need more information. The sheet I shared is a test sheet, so feel free to make any edits or changes to it.

Thanks,

Chris

------------------------------------------------------------------------------------------------------------------------------------------------------