Last row of a value that matches IN or OUT status

Question

I'm trying to write a code to show the user is IN or OUT on the google deployed web app.

The data is recorded in the Google sheet as seen in the screen shot

What Im trying to do is to show the status of an employee on the web-app is he IN or OUT based on the google sheet records.

Here is my code

//Status in/out

function statusIdication(){
  var user = Session.getActiveUser().getEmail();
  var employee = AdminDirectory.Users.get(user).name.fullName;
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();        
  var mainSheet = ss.getSheetByName("MAIN");
  var lastRow = mainSheet.getLastRow();
  var lastColoum = mainSheet.getLastColumn();
  
  
  for (var j = 1; j <= lastRow; j++){
    var mainSheet2 = ss.getSheetByName("MAIN").getRange(j,5).getValues();
    var mainSheet3 = ss.getSheetByName("MAIN").getRange(j,1).getValues();
    var status = (mainSheet2 =="IN" && mainSheet3 == employee) ; 
    if (status = true){
      var notification = "IN";
      Logger.log(notification);
    }
    else{
      var notifaction2 = "OUT"; 
    }
    
  }
  
  
}

Example - If last entry of Employee Peter = IN then return status as IN or else OUT.

I'm not sure where i went wrong, I'm getting an empty out put.

Answer 1

Issues:

As far as I can see you have the following two issues:

• In the if condition you are using an assignment operator = instead of equality operator ==. Because you assign the value true to status, the first if block statements gets executed everytime.

• The second issue has to do here:

  var mainSheet2 = ss.getSheetByName("MAIN").getRange(j,5).getValues();
  var mainSheet3 = ss.getSheetByName("MAIN").getRange(j,1).getValues();
  var status = (mainSheet2 =="IN" && mainSheet3 == employee) ; 
  

  You are using getValues but this returns a 2D array. You should use getValue() instead because you are referring to a single cell. As a result the comparison in the last line will work as well.

Note that you are using ss.getSheetByName("MAIN") three times. It is a good practice to use the variable you stored it, otherwise you make unnecessary calls.

Solution:

function statusIdication(){
  var user = Session.getActiveUser().getEmail();
  var employee = AdminDirectory.Users.get(user).name.fullName;
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();        
  var mainSheet = ss.getSheetByName("MAIN");
  var lastRow = mainSheet.getLastRow();
  var lastColoum = mainSheet.getLastColumn();
  
  
  for (var j = 1; j <= lastRow; j++){
    var mainSheet2 = mainSheet.getRange(j,5).getValue();
    var mainSheet3 = mainSheet.getRange(j,1).getValue();
    var status = (mainSheet2 =="IN" && mainSheet3 == employee) ; 
    if (status == true){
      var notification = "IN";
      Logger.log(notification);
    }
    else{
      var notifaction2 = "OUT"; 
    }
    
  }
  
  
}

Optimized solution:

Get the full data instead of iteratively calling getRange and getValue:

function statusIdication(){

 var user = Session.getActiveUser().getEmail();
 var employee = AdminDirectory.Users.get(user).name.fullName;
 var ss = SpreadsheetApp.getActiveSpreadsheet();        
 var mainSheet = ss.getSheetByName("MAIN");
 var data = mainSheet.getDataRange().getValues();
 
   for (var j = 0; j < data.length; j++){
    var row = data[j];
    var mainSheet2 = row[4];
    var mainSheet3 = row[0];
    var status = (mainSheet2 =="IN" && mainSheet3 == employee) ; 
    if (status == true){
      var notification = "IN";
      Logger.log(notification);
    }
    else{
      var notifaction2 = "OUT"; 
    }
    
  }