Apps Script that reads email of user and returns information from spreadsheet

Question

I'm trying to make a script to put on my google site for my students so that they can look up their login details for mymaths from home. I have a spreadsheet with all their emails which match up to their login details but when they access the page I want the to be able to just see their row. I am using Session.getActiveUser().getEmail() for this and all of my code is below but it is not working!

Any help would be greatly appreciated.

var userID = Session.getActiveUser().getEmail();
var userLogin;
var userPass;
var userName;
var text;

//Function to lookup login and password from email and add to variables
function lookup() {
   var spreadsheetKey = '1UftMLEgJPof3533X1Dp2IRqLIJa-70y8m-xnbyfj8ZA';
   var sheet = SpreadsheetApp.openById(spreadsheetKey);
   var end = SpreadsheetApp.getActiveSheet().getLastRow();
   var range = sheet.getRange(1, 0, end, 4);
   var values = range.getValues();
   for (i = 0; i < values.length; ++i) {
     if (values[i][0] == userID) {
        text = 'Here are your login details for MyMaths. Now get on with your homework!'
        userName = string(values[i][1]);
        userLogin = string(values[i][2]);
        userPass = string(values[i][3]);
     }
     else {
        text = 'Sorry, but there is an error. You will need to check in your book or ask your teacher for your MyMaths login details.'
     }
   }
}

//Function to create Userinterface on site page
function doGet() {
  var app = UiApp.createApplication();
  var panel = app.createAbsolutePanel();
  var label = app.createLabel(text);
  var table = app.createGrid(2,3);
  table.setText(0, 0, 'Name');
  table.setText(0, 1, 'Login');
  table.setText(0, 2, 'Password');
  table.setText(1, 0, userName);
  table.setText(1, 1, userLogin);
  table.setText(1, 2, userPass);
  panel.add(label);
  panel.add(table);
  app.add(panel);
  return app;
}

score 0 · Answer 1 · edited May 23 '17 at 11:49

The way you implemented this is not going to work. You defined a number of variables as "global" so that each function could use it but although this is true, the functions won't be able to modify them..., in other words : they cannot assign a value to these variables.

I would suggest to build this in a completely different way : a main doGet that would call a lookup function, this latter returning the appropriate values (as an array or an object).

The code goes like this, I didn't test it because I didn't have data to do so but it should work as expected.

btw, think about using some styles to make it look nicer :-) see this post from Anees Hameed showing a smart way to do it.

//Function to create Userinterface on site page
function doGet() {
  var userID = Session.getEffectiveUser().getEmail();
  var result = lookup(userID);
  var app = UiApp.createApplication();
  var panel = app.createAbsolutePanel();
  var label = app.createLabel(result.text);
  var table = app.createGrid(2,3);
  table.setText(0, 0, 'Name');
  table.setText(0, 1, 'Login');
  table.setText(0, 2, 'Password');
  table.setText(1, 0, result.userName);
  table.setText(1, 1, result.userLogin);
  table.setText(1, 2, result.userPass);
  panel.add(label);
  panel.add(table);
  app.add(panel);
  return app;
}

function lookup(userID) {
  var result = {};
  var spreadsheetKey = '1UftMLEgJPof3533X1Dp2IRqLIJa-70y8m-xnbyfj8ZA';
  var sheet = SpreadsheetApp.openById(spreadsheetKey);
  var end = SpreadsheetApp.getActiveSheet().getLastRow();
  var range = sheet.getRange(1, 0, end, 4);
  var values = range.getValues();
  for (i = 0; i < values.length; ++i) {
    if (values[i][0] == userID) {
      result['text'] = 'Here are your login details for MyMaths. Now get on with your homework!'
      result['userName'] = string(values[i][1]);
      result['userLogin'] = string(values[i][2]);
      result['userPass'] = string(values[i][3]);
    }
    else {
      result['text'] = 'Sorry, but there is an error. You will need to check in your book or ask your teacher for your MyMaths login details.'
      result['userName'] = '-';
      result['userLogin'] = '-';
      result['userPass'] = '-';
    }
  }
  return result;
}

Note that we can use a simpler notation for objects properties, instead of result['text']=... we can write result.text=... I just found it more clear but after all maybe it isn't :-) — Serge insas, Sep 07 '14 at 20:02

Apps Script that reads email of user and returns information from spreadsheet

1 Answers1