1

My students all have a Google account, but use different emails (like john@hotmail.com).I tried to write a webapp that would get from my grade spreadsheet the row with the webapp's user's email address, so that each student would only see his or her grades. That webapp uses: Session.getActiveUser().getEmail()

This only works for users in the same domain as the script, I have to run the script as the webapp user (which is ok -- my students trust me that far!). Here is that restriction:

Returns a User object describing the current user. In limited-privilege executions (such as in response to onOpen or onEdit), we only return the identity of the active user if both the user and the script owner are part of the same domain. This is to protect the privacy of consumer users, who may not want their email address exposed.

My script works, but I have to put the table of grades directly in the script and update the webapp each time I update my spreadsheet. Yeck!

I can't share my grade spreadsheet because then they would see each other's grades. So now I am brainstorming other workarounds.

Here is one example:

  1. Share a second spreadsheet, copy the grades from my spreadsheet with an update function that encrypts the grades. The webapp sucks up the data from the shared spreadsheet and decrypts. Now a simple update of a spreadsheet is all that is needed -- the webapp doesn't need to be republished. (There are some simple encryption options, like base64encode with maybe a little scrambling...?)

  2. Find a way to automatically update the webapp and republish. (Don't know how to do that.)

  3. ????

Any suggestions?

PS: if 1 seems feasible, some suggestions for simple encryption code?

wgw
  • 601
  • 1
  • 8
  • 20

3 Answers3

2

There are some simple encryption options, like base64encode with maybe a little scrambling...?

Yes, the GAS has functions to encode/decode using the Base64 algorithm - Utilities.base64Encode, Utilities.base64Decode, but I think, it is not an option even with a little scrambling. Students are very clever. My opinion is to use a JavaScript implementation of a encryption algorithm, for instance the Blowfish. There are a number of its implementations on JavaScript (here and here). These implementations should work in the GAS environment without any changes or with small modifications. It is should be sufficient to copy and paste the source code to the GAS Editor.

megabyte1024
  • 8,482
  • 4
  • 30
  • 44
  • Thanks! Will do (students are generally smarter than I am about such things...) – wgw Oct 16 '12 at 16:27
  • Thanks! I think you are right -- students outsmart me all the time (thankfully). But looking at the code, encoding looks a bit daunting. Still, if the code goes in without much tweaking, that should do the trick. (Knowing nothing about cryptography, I do come back to inventing some simple scrambling algorithm, but I'm guessing anyone could get around it. Still, if I do come up with something, I will post it here...and in some cryptography forum, just out of curiosity.) – wgw Oct 16 '12 at 17:04
  • (oops, I got a little tangled in the commenting feature...) – wgw Oct 16 '12 at 17:05
1

I dont understand why you say "My script works, but I have to put the table of grades directly in the script and update the webapp each time I update my spreadsheet"

It is very easy to create a webapp that shows a part of a spreadsheet using a flextable for example. Each instance of the app will have a user related content automatically and you will only have to update your master spreadsheet to get what you want. I don't know why you want to encode data for this... I think the user identification through the google login should be enough, don't you ? Here is an example of such a webapp, the numbers you see are taken from a specific column in a master spreadsheet (for this public copy I set the app as running as "me" to avoid the authorization process but in real every user is identified and sees only his data).

The Spreadsheet itself and the code are viewable here, do not hesitate to come back if you need further information.

Serge insas
  • 45,904
  • 7
  • 105
  • 131
  • You say: " I set the app as running as "me" to avoid the authorization process but in real every user is identified and sees only his data". I tried to do that, but with no success. If your app can display the user email at the top, I would be very interested. So far I only see a pair of exclusive choices: the webapp runs as me and I can't see the users email or the webapp runs as the user and the app cannot read my spreadsheet. So far, I don't see a third option, which is why I put the spreadsheet information in the webapp itself. Thanks for the feedback! – wgw Oct 16 '12 at 16:51
  • You have to set the app as 'the user accessing the app' and the spreadsheet as ' anyone with the link can edit ' (in my case it must be read /write). This doesn't mean anyone can open the sheet itself since the ID remains unknown. Let me know if you want me to change my app parameter for you to test or if you do it yourself on a copy you can do of the as and the code. – Serge insas Oct 16 '12 at 19:04
  • sorry, typo there : I meant copy you can do of the SS and the code. – Serge insas Oct 16 '12 at 20:27
  • Interesting! I'll give it a try. The key that you mention is to have it accessible by anyone through that link .... That would be the solution I was looking for. I'll let you know how I have fared. Thanks! – wgw Oct 17 '12 at 04:53
  • Ok; that did the trick -- thanks so much for pointing it out to me. One little tweak for my case: the shared spreadsheet link need only be view only. Edit isn't necessary, which is a good thing, just in case by some miracle (close to the immaculate conception) a student finds the link to the sheet. Again, many, many thanks for your help and for sharing your work. – wgw Oct 18 '12 at 01:18
0

Is it an option to sync your sheet with a scriptDB? And then query the DB where user=loginID

Thomas
  • 569
  • 3
  • 10
  • I haven't used that scriptDB feature. I will look into it. Thanks for the suggestion! – wgw Oct 17 '12 at 04:54