Entering Data in Semi-Protected Google Spreadsheets
November 14, 2013 1:13 PM   Subscribe

I work in a high school where we often want teachers to fill out a single Google spreadsheet with information about students in their homeroom class.

For the sake of simplicity, the school has 2000 students with 25 students in each class, so we have 80 teachers. I have a Google Spreadsheet with all 2000 students and their homeroom teacher. I want the homeroom teacher to have access to updating her 25 students on the spreadsheet without updating the other 1975 students in the school.

Any ideas? I'm hoping there's a script available in Google Apps that will help me do this, but my searching so far has given me things that are close...but not quite. I'm open to a different type of solution to this problem if you have a creative idea for me.
posted by allison00 to Education (7 answers total) 3 users marked this as a favorite
 
What about giving each teacher their own spreadsheet and having a separate master spreadsheet which references the individual ones?
posted by ocherdraco at 1:19 PM on November 14, 2013


Ocherdraco, I considered creating individual spreadsheets, but I don't know an automated way of creating them. There will be more than 100 of them, and I don't have the time to create each one and share it with each teacher. Is there a shortcut I'm missing?
posted by allison00 at 1:28 PM on November 14, 2013


OK, this could be way out of line, depending on what you're doing, but:

These records are pretty important, I'm thinking. Probably you should use a computing environment where you have predictable backups and more control -- Google could (probably won't, but could) decide to get rid of Google Docs tomorrow, or make all the content public.

Also, think about it this way: the time you and others put into making this system work (even in Google spreadsheets, that's significant), and that all teachers put into learning the system, working out the bugs -- plus any additional time that would be required to convert or learn a new system -- mean that whatever you put together now will _HAUNT_ you forever. It will be really, really difficult to change to a different system in the future.

And, for future stuff (field trip forms? parental homework tracking? disciplinary notices? you know better than I do), it will be extremely tempting to just use what you already know -- in this case, Google spreadsheets. And the privacy and ownership issues just get bigger in every case.

This would be a very easy database app to write. On your own web space. Even if you have some talented students, and get them a $100/year Pair.com account (I don't work for Pair.com), they could make something cool. I know it will be a hassle to supervise them, or a professional you hire -- but it will be much better in the long run.
posted by amtho at 1:45 PM on November 14, 2013 [1 favorite]


I assume you're using your institution's free Google Apps for Education instance, like thousands of k12 schools do. If so, it's likely to be the only authorized place for you to host your own user-created data for years to come, and it may be worth your while to learn Google Apps Script to program it. Google Apps Scripts are created and run from a spreadsheet's tools menu, but they can access Google Apps in general and do most of what a user can do, including creating folders and sharing individual spreadsheets in a folder with specific people. The language is essentially JavaScript with very minor oddities, so you can learn it from sources like Eloquent JavaScript or Codecademy. But if you're a complete beginner, then this probably isn't a practical solution for your immediate problem.
posted by Monsieur Caution at 3:21 PM on November 14, 2013


OK, if that's the best answer (LOL), then here's a script I knocked out in a few minutes that will create spreadsheets en masse based on an existing spreadsheet and then share the spreadsheets out. Throw it into the script editor, change the config values, save it, and run the massCreateFromTemplate function. You should test it some with non-essential data and configuration values, e.g. offhand I'm not sure whether it will spam your collaborators with notices that a spreadsheet has been shared with them.
// massCreateFromTemplate: creates a bunch of spreadsheet 
// copies (one for each user in a list) and shares them

function massCreateFromTemplate() {

  // The prefix added to the name of each spreadsheet
  var prefix = 'test_';

  // The users and their IDs (e.g. homeroom numbers)
  var users = {
    'user1@gmail.com': 101,
    'user2@gmail.com': 102,
  };

  // Name of template spreadsheet
  var template = 'Untitled spreadsheet';
  
  var folder = DocsList.createFolder(prefix + 'folder');
  
  for (var user in users) {
    
    var ssName = prefix + users[user];
    
    var file = DocsList.find(template)[0];
    var copy = file.makeCopy(ssName);
   
    copy.addToFolder(folder);
    copy.addEditor(user);
    
  }

}

posted by Monsieur Caution at 10:26 PM on November 14, 2013


You can have different protection for different worksheets within a spreadsheet (https://support.google.com/drive/answer/144687?hl=en), so you can have one master spreadsheet with one worksheet for each teacher with different protection for each and then you can use the cancatenate function to combine them into one master worksheet if you need that. Here's supposedly an example of how to do that:

=CONCATENATE(Sheet1!A:A,",",Sheet2!A:A,",",Sheet3!A:A,",",Sheet4!A:A,",",sheet5!(A:A)) This will concatenate A1,A1,A1,A1,A1 for sheets 1-5 on sheet 6. Drag it down to concatenate by cells (A2 then A3). You don't need to define the ranges A:A but you can if you want.

So this would take some work, but once it's done it would solve your problem.
posted by Dansaman at 10:55 PM on November 14, 2013


I just found out that for this particular situation, only about 15 teachers will have to fill out the spreadsheet, so I'm going to use Dansaman's solution of creating one tab per teacher then using concatenate.

Starting Monday, I'm going to learn how to use Google Apps Script so that when we have an all-school need for this application, I'll have it ready. Thanks Monsieur Caution! Your script will be a huge help for me! It's been about 10 years since I've done any coding, but I'm ready to give it a go!
posted by allison00 at 9:09 AM on November 15, 2013


« Older Roadtrippin' in basically winter. Where to?   |   Should I still be looking for a new job? Newer »
This thread is closed to new comments.