Google Apps Script: reading from private spreadsheet

Question

I have a gradebook web app script which looks at a logged in student's email address, finds the email in the gradebook, and then displays the student's grades based on the column the email is in. The only problem is this only works if the spreadhseet is made public. How can I keep the spreadhseet private and still make this script work? I know that if I choose "Anyone with the link" it is unlikely someone will find the spreadsheet, but I'd prefer it to stay private. In addition, from the "Deploy as Web App" interface, the app must be executed as the user, not myself. Any ideas?

var ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheet/ccc?key=ID');
var sh1 = ss.getSheetByName('StudentGrades');
var logsheet = ss.getSheetByName('logsheet');
var data = sh1.getDataRange().getValues();
var user = Session.getEffectiveUser()
Logger.log(user)


function doGet() {
       var app = UiApp.createApplication();
       if(!getcol(user)){
          var warn = app.createTextBox().setWidth('500').setValue("Your results are not available or you don't have permission to view these data");// if user is not in the list, warning + return
app.add(warn)
return app
          }
  var grid = app.createGrid(data.length, 2).setWidth('300px').setBorderWidth(1).setCellPadding(0).setCellSpacing(0).setStyleAttribute('borderCollapse','collapse').setId('grid');
       var text = app.createLabel(user).setWidth('300px');
       var col = getcol(user)
       grid.setWidget(0,1,text).setText(0, 0, 'Results for');
       grid.setStyleAttribute('textAlign','center')
       for(n=1;n<data.length;++n){
         grid.setText(n, 0, string(data[n][0]));
         grid.setText(n, 1, string(data[n][col]));
         grid.setStyleAttributes(n-1, 0, {'fontWeight':'bold','background':'#fff','border':'1px solid #000'});//left column css attributes
         grid.setStyleAttributes(n-1, 1, {'fontWeight':'bold','background':'#fff','border':'1px solid #000'});//right column css attributes
       }
       app.add(grid);
       return app
    }

function string(value){
Logger.log(typeof(value))
if (typeof(value)=='string'){return value};// if string then don't do anything
if (typeof(value)=='number'){return Utilities.formatString('%.1f / 20',value)};// if number ther format with 1 decimal
if (typeof(value)=='object'){return Utilities.formatDate(value, Session.getTimeZone(), "MM-dd")};//object >> date in this case, format month/day
return 'error'
}


function getcol(mail){
  if(data[0].toString().indexOf(mail.toString())!=-1){
  for(zz=1;zz<data[0].length;++zz){
    if(data[0][zz] == mail){var colindex=zz;break}
  }
   return colindex
  }
  return false
}

Serge insas Serge insas · Accepted Answer · 2013-04-21T09:32:50

As I was suggesting in another of your posts you could setup a 'manual login' that will allow you to keep the spreadsheet private while running the webapp as yourself.

That implies that you'll have to create a list of user/password keys/values and that each student will have to enter his user name and password (you'll have to send them that information by email along with the link to the webapp) in a front end login screen before they gain access to the results display part.

I guess the best place to hold that list would be in script properties in the form of key:value.

If you need more that this to implement that solution (and if you think you'll go that way) feel free to ask.

EDIT following your comment :

Ok, I understand it can be unpleasant :)

Just wondering, wouldn't it be easier to store these data in the script itself in a scriptDB that you could automatically update with the spreadsheet values ? The update would have to be executed by you (or by a timer trigger you create so that it runs on your account) and so the spreadsheet would remain private.

The students could then access the webapp with their accounts without accessing the spreadsheet.

Google Apps Script: reading from private spreadsheet

4 Answers