How to automatically share one row of a Google Sheet?
June 5, 2023 7:30 AM   Subscribe

I have about 200 students. Each student will be completing about 75 tasks. I want to have everyone's progress in one place (ideally, on a Google Sheet). I also want each student to be able to see their progress and no one else's (ideally, automatically integrated with Google Classroom). I do NOT want to have to update everything in multiple places. I would rather not do any action 200 times. Is this possible?

In case it's relevant:
- the tasks will be marked as either complete or not complete (no part marks)
- my school uses Google products, so they would be my preference. This part is not critical.

If you can help with only part of the problem, I'd appreciate that too.
posted by MangoNews to Computers & Internet (11 answers total) 3 users marked this as a favorite
 
Best answer: Could you assign each student a randomized number to designate them in place of a name or ID number and let everyone see without any identifying info? Then you need to provide each student with their signifier once at the start of term.
posted by Sweetchrysanthemum at 8:25 AM on June 5, 2023 [9 favorites]


you can not selectively share parts of a sheet in google. i could see two ways to sorta accomplish this in sheets:

- creating a document for each student (you own all of them) that you pull the data from into a single sheet. you would need to create, share and reference 200 separate documents.

- creating a generic student tracking document in sheets (you own a single master, students make their own copies to track their work), include a submit button that sends updated data via html form response to a master sheet you own. student changes wouldn't be visible to you unless they submit every time. you would create one document and have a bunch of logic in the response sheet to handle the (expected) duplicate submissions. i haven't fussed with this stuff in 5+ years, so my memory on the details is foggy.
posted by noloveforned at 8:33 AM on June 5, 2023 [2 favorites]


Best answer: Is there a reason you can't use the built-in Google Classroom Gradebook for this? You could create a grade category called "Task Completed" with a max 1 point score -- so each task would be marked as either 0 (not done) or 1 (done). You could also use the weighted categories option to eliminate this from your final grading or incorporate it at whatever weighting you'd like. Here's a pretty clear tutorial that shows how to do all of the above. Here's a tutorial that shows how students will see it.

Any other method I can think of would require you to set up 200 separate Google Sheets, with separate view permissions, to show a single line from your master spreadsheet. If you wanted to do that, you'd need to use the IMPORTRANGE function; you'd also want to make sure the students were set as Viewers (not Editors) so they couldn't pull other data from the spreadsheet.
posted by ourobouros at 9:45 AM on June 5, 2023 [4 favorites]


I'd not be confident in the security of this, but you could possibly build an apps script to pull data based on the logged in user
posted by idb at 10:14 AM on June 5, 2023 [1 favorite]


With Google based products, look at AppSheet.

https://about.appsheet.com/home/

Then use Security Views on it to restrict what they can see. https://support.google.com/appsheet/answer/10104977
posted by SegFaultCoreDump at 10:49 AM on June 5, 2023 [1 favorite]


I'm not familiar with google classroom, so Ourobouros' solution above might be the easiest.

The only quick way I can think of, assuming you want students to update their own tasks, you could create a google forms link to a spreadsheet, ticking "require sign in" "limit to 1 response" and "Allow response editing". The question could be 75 check boxes. Then people can use the forms link to add or update their status whenever they want, and only you can see the overall status in the sheet.
posted by samj at 11:58 AM on June 5, 2023 [1 favorite]


Yes, this is what the Google Classroom gradebook is for. You don't want any solution that risks a FERPA violation by students accidentally accessing each others' grades. That's why learning management systems were invented.
posted by hydropsyche at 1:41 PM on June 5, 2023 [2 favorites]


You could also create a Google form, they dump responses into a spreadsheet, and you can allow people to update their form answers. But the Classroom suggestion above may be better depending on your circumstances.
posted by rachelpapers at 1:52 PM on June 5, 2023 [2 favorites]


You could use mail merge and send a status email when you are done updating the spreadsheet after grading each assignment.
posted by rockindata at 5:46 PM on June 5, 2023 [2 favorites]


Best answer: OP can correct me if I’m wrong, but the description implies that OP will be updating student grades, not the students themselves. Students entering their own grades into the central course gradebook is generally not a thing. So the direction of information flow needed is going the opposite direction from what some comments are discussing: from central gradebook out to individual students, not from individual students to central gradebook.

LMS gradebooks are a real pain to set up for non-standard evaluation schemes such as it sounds like OP is using. When I used an alternative grading scheme this past semester, I ended up just having a separate spreadsheet for each student. But I had fewer than 20 students in the course. But it was still a logistical nightmare. I suspect either spending way more time than I would prefer in putting together a kludge to make the LMS gradebook do what I need (there maybe seems to be a way to assign labels to grade items and then do some maybe very basic logical programming in the LMS my university uses, which is a version of Moodle?) or else learning how to do mail merges that import spreadsheet data to send out weekly progress reports to students may be the only options. Following this thread with interest, however.
posted by eviemath at 6:11 PM on June 5, 2023 [1 favorite]


Response by poster: Thank you all for the varied ideas and responses!

Yes, I want to update the information myself, but I want students to have easy access.

There were two reasons I wasn't keen on just using Classroom's gradebook originally:

1. I had forgotten (or didn't know?) about the grid view. I really want to be able to look at all the information at once and update the grades for different students and different assignments without clicking a million places. The grid view solves that. So thanks for that reminder, Ourobouros!

2. I want to be able to organize and reorganize the data looking for trends and problem areas (nothing complicated... just "are all the trumpet players having problems with assignment #17"?). But I think a solution here might actually be to download the data FROM Classroom INTO Sheets, rather than the other way around. Even if it won't automatically update the spreadsheet, it's not much trouble to download it fresh every week or so to take look. I think it was eviemath's discussion of directionality that inspired that idea... thanks!

Luckily, the actual grade I will assign is essentially just "needs improvement", "good" or "great". I'll assign those based on how many tasks the students complete. So I don't need to get Classroom to do anything complicated there.

Again, thank you all! Your ideas and approaches all helped me consider different angles on the problem and I appreciate it!
posted by MangoNews at 6:22 PM on June 6, 2023 [3 favorites]


« Older Roller ball cartridges not working   |   Recommendations for when big dreams appear... Newer »

You are not logged in, either login or create an account to post comments