Last row of a value that matches IN or OUT status

user_8470494 0
Viewed 85

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 enter image description here

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.

google-apps-script google-sheets web-applications javascript
2 Answers

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"; 
    }
    
  }
user_112252910

Upon checking, it seems that your condition checker for the status is written incorrectly. Instead of status = true, you should use status == true. This is because if you try to write it as the latter, it will always be considered true and your conditional statement will not be assessed as false and will not go to the else statement.

Also, you are missing a logger if the condition falls into the else statement. Is this expected? If not, you can try adding it.

user_146060380
Related