Entering Data in Semi-Protected Google Spreadsheets

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.

What about giving each teacher their own spreadsheet and having a separate master spreadsheet which references the individual ones?

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?

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 themfunction massCreateFromTemplate() { // The prefix added to the name of each spreadsheet var prefix = 'test_'; // The users and their IDs (e.g. homeroom numbers) var users = { '[email protected]': 101, '[email protected]': 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); }}

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.

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!