google-apps-scriptgoogle-sheetstimestamp

Google Apps Script - How to find matching text then insert timestamp in the same row in sheets?

Hello everyone so I organised an event where people use cards with QR codes to check in and out, and the code enters the name of the person so I used a script that I found online to automatically insert a timestamp next to it But the problem is I'd like to make the script check when the same person clocks out and automatically move his name in the same row with a new timestamp next to it on a different column than the first one.

Here's the code I use (Apologies if the title is misleading)

var s = SpreadsheetApp.getActiveSheet();

if( s.getName() == 'Sheet1' ) { 

var r = s.getActiveCell();

if( r.getColumn() == 1 ) { 

var nextCell = r.offset(0, 1);

if( nextCell.getValue() === '' ) 
nextCell.setValue(new Date());
}
if( r.getColumn() == 3 ) { 
  var nextCell = r.offset(0, 1);
if( nextCell.getValue() === '' ) 
nextCell.setValue(new Date());
}
if( r.getColumn() == 5 ) { 

var nextCell = r.offset(0, 1);

if( nextCell.getValue() === '' ) 
nextCell.setValue(new Date());
}
if( r.getColumn() == 7 ) { 

var nextCell = r.offset(0, 1);

if( nextCell.getValue() === '' ) 
nextCell.setValue(new Date());
}
}

I used the code above but I couldn't find a way to insert the same person clocking in and out on the same row, this is my first time on Google Apps Script so I apologise again if it's very easy.

I want to make it so that Kyle here when he appears for a second time, he gets moved automatically to the right besides the first time he checked in

Solution

  • The script captures names via a QR code and records check-in and check-out times for those names. Your problem is that the script is not testing for an existing name, testing whether that name has checked out, and updating the check-out time instead of the check-in time.

    Assumptions

    1. The current name is in the active cell
    2. If a name is found in a row above the current row, then check-in time is not blank.
    3. Names and check-in/out details are recorded in Sheet1
      • names in Column A
      • check-in time in Column B
      • check-out time in Column C
    function updateCheckinOutTime() {
  var s = SpreadsheetApp.getActiveSheet();
  // get variables for currentName
  var r = s.getActiveCell();

  if( s.getName() != 'Sheet1' || r.isBlank() === true ) { 
    // not Sheet1 or the cell is blank, do nothing
    // Logger.log("DEBUG: this is NOT sheet 1 or the cell is blank")
  }
  else{
    // this is sheet 1
    // Logger.log("DEBUG: this is sheet 1")

    var curDate = Utilities.formatDate(new Date(), SpreadsheetApp.getActive().getSpreadsheetTimeZone(), "dd/MM/yyyy HH:mm:ss")
    //Logger.log(curDate) // DEBUG
    
    // get variables for currentName
    var curName = r.getValue()
    var curCol = r.getColumn()
    var curRow = r.getRow()
    // Logger.log("DEBUG: active cell = "+r.getA1Notation()+", value:"+curName+", column = "+curCol+", row:"+curRow)

    // step#1 - look for the name in this column
  
    var namesHstryLR = s.getLastRow()
    // Logger.log("DEBUG: last row on names = "+namesHstryLR)
    // adjust -1 to ignore the current name
    var namesHstryRange = s.getRange(1,1,namesHstryLR-1)
    // Logger.log("DEBUG: last row for names history = "+namesHstryLR+", so names history range = "+namesHstryRange.getA1Notation())
    var namesHstry = namesHstryRange.getValues()
    // Logger.log(namesHstry) // DEBUG

    // convert namesHstry 2D array to 1D
    names = namesHstry.map(function(e){return e[0];})
    // Logger.log(names) // DEBUG
    // Logger.log(curName) // DEBUG
    // use lastIndexOf to find the last occurence of the current name in names history
    // if result = -1 then no match

    var result = names.lastIndexOf(curName);   
    // Logger.log("DEBUG: result:"+result+", current name = "+curName+", names:"+names)
  
    if (result != -1){
      // Found a match: assume that check-in time is not blank
      // indexof is zero based so +1 to get row number
      // Logger.log("DEBUG: found a match on row: "+(result+1))
      // check check-out time
      var hstryOutRange = s.getRange((result+1),3)

      // if checkout time is blank, then update checkout time.
      // if not blank, then this is a new checkin
      if(hstryOutRange.isBlank() === true){
        // if blank, then this is a user logging out, so 
        // log the time in the matched row and
        // delete activecell
        // Logger.log("DEBUG: Checkout time NOT found, cell "+hstryOutRange.getA1Notation()+" is blank")
        hstryOutRange.setValue(curDate)
        r.clear()
        // Logger.log("DEBUG: updated the checkout time and cleared the current cell")
      }
    }
    else{
      // if result = -1 (no match found) OR checkout is not blank
      // then this is a new checkin, update checkin date only.
      r.offset(0, 1).setValue(curDate)
    }
  }
}

    Comments

    The script is extensively commented and Logger statements are included to enable the user to test values at various stages of the script.

    The basic principles, based on the OP's script are:

    Sample

    snapshot