Find matching text in sheets

Question

At a charity, volunteers are issued i.d. cards with QR codes. To clock in, a volunteer scans their i.d. card. A script automatically inserts the person's name into column 1 of a Google Sheet and inserts a timestamp into column 2 of the same row, to indicate timeIn. Next, we want to automate clocking out.

When a person's i.d. card is scanned, I know how to insert the person's name into a temporary holding cell. Next, I need code to check whether that person has already signed in (i.e., compare contents of the holding cell to each cell in column 1 until a match or an empty cell is found). If a match is found, then a timestamp should be entered in column 3, in the row where the person signed in, to indicate timeOut. Then, data in the temporary holding cell should be cleared.

I am very new to scripts for google apps, and have not yet figured out how to compare the values in two cells or to code a loop.

Answer 1

You can do the following:

1. Retrieve all contents of column 1 with getRange() and getValues() and push them into an array
2. Verify with indexOf() either the value in the temporary holding cell matches an entry of the array and if so - retrieve the entry position index
3. Use setValue() to insert a timestamp into a cell of column 3, row index

Here is a sample:

function myFunction() {
  var sheet=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var startRow=2;
  var lastRow=sheet.getLastRow();
  var numberRows=lastRow-startRow+1
  var Ids=sheet.getRange(startRow,1,numberRows,1).getValues();
  var temporary=sheet.getRange(2,4).getValue();//please modify according to the position of your temporary cell
  var array=[];
  var timestamp=Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd'T'HH:mm:ss'Z'");
  for(var i=0;i<numberRows;i++){
    array.push(Ids[i][0]);
  }
  var index=array.indexOf(temporary);
  if(index!=-1){
    sheet.getRange(index+startRow,3).setValue(timestamp);
  }
}

You might want to incorporate in a doGet() function of a Web App deployment that will run the code automatically when new data is inserted into the temporary cell.