Google Sheets App Script Mysterious Error

Question

I'm teaching a class and for my class I keep all of my student's marks on a google spreadsheet. On my website I would like to present information to students on an individual basis. I've created an app where it presents them with a password text box. They type in their password and then it retrieves information from the spreadsheet that is unique to them and presents it to them in a label. I've been trying to hack this all together, but it's just not working properly and I'm getting an error that I cannot diagnose. If I print out the information using Browser.msgBox() it outputs the info, but otherwise it generates an error. Why is this happening and what is the fix? Here's the code:

var pointsSheet = SpreadsheetApp.openById('1o8_f063j1jYZjFEnI_P7uAztpnEAvQ6mc3Z1_Owa69Y');

//creates and shows an app with a label and password text box
function doGet() {
  var app = UiApp.createApplication().setTitle('Incomplete Challenges');

  var mygrid = app.createGrid(1, 2);
  mygrid.setWidget(0, 0, app.createLabel('Password:'));
  mygrid.setWidget(0, 1, app.createPasswordTextBox().setName("text"));

  var mybutton = app.createButton('Submit');

  var submitHandler = app.createServerClickHandler('getResults');
  submitHandler.addCallbackElement(mygrid);
  mybutton.addClickHandler(submitHandler);

  var mypanel = app.createVerticalPanel();
  mypanel.add(mygrid);
  mypanel.add(mybutton);
  app.add(mypanel);

  SpreadsheetApp.getActiveSpreadsheet().show(app); 
  //return app; //UNCOMMENT WHEN DEPLOYING APP
}

//obtains data based on password entered by user and outputs their info
function getResults(eventInfo) {
  var app = UiApp.createApplication().setTitle('Incomplete Challenges');
  var password = eventInfo.parameter.text;

  var passwordCheckRange = pointsSheet.getRange("B34:C34").getValues();

  if (passwordCheckRange == null) {
    Browser.msgBox("Error: Range is null");
    return app;
  }

  var name;
  for(var i = 0; i < passwordCheckRange.length; i++) {
    if(passwordCheckRange[i][1] == password) {
      name = passwordCheckRange[i][0];
      break;
    }
  }

  var studentRecordRange = pointsSheet.getRange("B3:AY29").getValues();
  var headingRange = pointsSheet.getRange("B1:AY2").getValues();

  if (studentRecordRange == null) {
    Browser.msgBox("Error: Range is null");
    return app;
  }

  var requestedRecord;
  for(var i = 0; i < studentRecordRange.length; i++) {
    if(studentRecordRange[i][0] == name)
      requestedRecord = studentRecordRange[i];
  }

  var stringRecord = "";
  for(var i = headingRange[1].length-1; i >= 7; i--) {
    if (requestedRecord[i] == "")
      stringRecord += headingRange[1][i] + ": " + headingRange[0][i] + "XP" + "\\n";
  }

  var mygrid = app.createGrid(2, 1);
  mygrid.setWidget(0, 0, app.createLabel('INCOMPLETE CHALLENGES'));
  mygrid.setWidget(1, 0, app.createLabel(stringRecord));

  var mypanel = app.createVerticalPanel();
  mypanel.add(mygrid);
  app.add(mypanel);


  //Browser.msgBox(stringRecord);
  return app;
}

The error that I experience is: Error encountered: An unexpected error occurred.

As you can see it's very helpful.

Anees Hameed Anees Hameed · Accepted Answer · 2014-08-24T08:32:48

Line 28 it should be getActiveApplication() and not createApplication(). You cant create an application on another application. :)

Also I think line 63 it should be "<br>"; instead "\n"; along with line 68 it should be createHTML instead of createLabel

I also think that you have apply few styling css so that your app looks good. check on .setStyleAttributes in UiApp.

Google Sheets App Script Mysterious Error

2 Answers