Library Sign in System

Library.gs

function onFormSubmit(){
  var form = FormApp.getActiveForm();
  var currentResponse = form.getResponses()[form.getResponses().length-1];
  var barCode = currentResponse.getItemResponses()[0].getResponse();
  try{
  var reason = currentResponse.getItemResponses()[1].getResponse();
  }
  catch(err){
    var reason = 'None Given';
    Logger.log('No Reason Given');
  }
  var result = ''
  var info = findInfoFromBarCode(barCode);
  if (info[1] == 'Unknown'){
    try{
    info[1] = currentResponse.getItemResponses()[2].getResponse();
    }
    catch(err){
      Logger.log('Unknown Student');
    }
  }
  var name = info[0];
  var postion = info[1];
  var date = new Date();
  var month = date.getMonth()+1;
  var day = date.getDate();
  var year = date.getFullYear();
  var hour = date.getHours();
  var myDay = month+'/'+day+'/'+year;
  var minute = date.getMinutes()+1;
  if (minute < 10){
    minute = '0'+minute;
  }
  var timestamp = hour+':'+minute;
  var sheetName = month+'-'+year;
  var spreadsheet = SpreadsheetApp.openById('1SNmUkStQF9Wihu-HT7FJPpOqQO-2pE2qgBS4bqJnvRc');
  try{
    var sheet = spreadsheet.getSheetByName(sheetName);
    var lastRow = sheet.getLastRow();
    Logger.log('Hit');
  }
  catch(err){
    var sheet = spreadsheet.insertSheet(sheetName);
    sheet.appendRow(['Name','Time-In','Time-Out','Grade / Role','Date','Reason','Result','Minutes in Station','In Now()','Out Now()'])
    Logger.log('New Sheet Inserted');
  } 
  var lastRow = sheet.getLastRow();
  Logger.log(lastRow);
  var range = sheet.getRange(1, 1, lastRow, 4).getValues();
  for (var i = range.length-1; i >-1; i = i-1){
    Logger.log(range[i][0]);
    if (name == range[i][0]){
      Logger.log('Found Name');
      var checkEntry = range[i][2].toString();
      if (checkEntry.length > 2){
        if (reason.length > 2 && result.length > 2){
          sheet.appendRow([name,timestamp,timestamp,postion,myDay,reason,result, 0 ,Date.now(), Date.now()]);
          return;
        }
        Logger.log('Duplicate');
        sheet.appendRow([name,timestamp,'',postion,myDay,reason,'','',Date.now()]);
        return
      }
      else{
        sheet.getRange(i+1, 3).setValue(timestamp);
        var msTime = sheet.getRange(i+1, 9).getValue();
        var newNow = Date.now();
        var msInLibrary = newNow - msTime
        var minInLibrary = Math.floor(msInLibrary / 60000);
        Logger.log(minInLibrary);
        sheet.getRange(i+1, 7).setValue(result);
        sheet.getRange(i+1, 8).setValue(minInLibrary);
        sheet.getRange(i+1,10).setValue(newNow);
        return
      }
    }
    
  }
  if (reason.length > 2 && result.length > 2){
          sheet.appendRow([name,timestamp,timestamp,postion,myDay,reason,result, 0 ,Date.now(), Date.now()]);
          return;
  }
  sheet.appendRow([name,timestamp,'',postion,myDay,reason,'','',Date.now()]);
  
}

function findInfoFromBarCode(barCode){
  var modCode = "*"+barCode+"*";
  var database = SpreadsheetApp.openById('1N5YoTJCcIWDS5Sg7AVoGfVJAlobZcgCV4N5rNkQeg8A');
  
  var subString = modCode.substring(1, 3);
  Logger.log('Substring is: '+subString);
  if (subString == '99'){
    var sheet = database.getSheetByName('parents');
  }
  else if (subString == '10'){
    var sheet = database.getSheetByName('students');
  }
  else{
  //Staff
  var sheet = database.getSheetByName('staff');
  }
  
  var lastRow = sheet.getLastRow();
  var dataRange = sheet.getRange(1, 1, lastRow, 6).getValues();
  for (var i = 1; i < lastRow; i++){
    if (dataRange[i][3] == modCode){
      var firstName = dataRange[i][1];
      var lastName = dataRange[i][2];
      var position = dataRange[i][4];
      Logger.log(firstName+' '+lastName+': '+position);
      return [firstName+' '+lastName, position];
    }
  }
  return [barCode, 'Unknown'];
}