CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Jme Sekaiichi
Jme Sekaiichi

Reputation: 51

Is there any way so that I can search value upwards?

I was wondering if anyone knows whether it is possible or not to search a value upwards instead of the usual up to down in a column.

The reason why I'm asking is because I'm currently preparing a visitor sheet which will be recorded into Google Sheets, that comes from Google Forms. Each visitor will be given an ID/Pass number which will be used to record their entry and exit time.

The problem now is that, if two people uses the same ID, it will change only the entry and exit time of the initial entry and not the latest one.

Therefore, I was wondering if anyone knows or is it possible so that the script can search upwards instead (so the newer entry will be found first) or if anyone knows a command so that it doesn't overwrite the previous entry (ignore an entry that already has a time recorded in the cell) that would be great.

I've already tried sorting it into a new Tab using QUERY and IMPORTRANGE but then I can't record the time onto the new tab.

var ss = SpreadsheetApp.openByUrl("sheet_url");
var sheet = ss.getSheetByName("Sheet_name");

function doGet(e){
  var action  = e.parameter.action;

  if(action == "in")
    return inTime(e);

  if(action == "out")
    return outTime(e);

}


function doPost(e){
  var action  = e.parameter.action;

  if(action == "in")
    return inTime(e);

  if(action == "out")
    return outTime(e);

}

function inTime(e){
  var id = e.parameter.id;
  var values = sheet.getRange(2,1,sheet.getLastRow(),1).getValues();

  for(var i = 0 ; i<values.length ; i++){
    if(values[i][0] == id){
      i=i+2;
      var in_time = Utilities.formatDate(new Date(), "GMT+8", "HH:mm:ss");
      sheet.getRange(i,8).setValue(in_time);
      return ContentService.createTextOutput("Check In Time "+in_time).setMimeType(ContentService.MimeType.TEXT);


  }
  return ContentService.createTextOutput("Id Not Found").setMimeType(ContentService.MimeType.TEXT);
}


function outTime(e){
  var id = e.parameter.id;
  var values = sheet.getRange(2,1,sheet.getLastRow(),1).getValues();

  for(var i = 0 ; i<values.length ; i++){
    if(values[i][0] == id){
      i=i+2;
      var out_time = Utilities.formatDate(new Date(), "GMT+8", "HH:mm:ss");
      sheet.getRange(i,9).setValue(out_time);
      return ContentService.createTextOutput("Check Out Time "+out_time).setMimeType(ContentService.MimeType.TEXT);
    }
  }
  return ContentService.createTextOutput("Id Not Found").setMimeType(ContentService.MimeType.TEXT);
}

Upvotes: 0

Views: 86

Answers (2)

ziganotschka
ziganotschka

Reputation: 26836

A good solution for your script would be to implement an auxiliary variable var k=values.length-i-1, inside the loop which allows you to loop backwards.

Your loops would then look like:

function inTime(e){
  var id = e.parameter.id;
  var values = sheet.getRange(2,1,sheet.getLastRow(),1).getValues();

  for(var i = 0 ; i<values.length ; i++){
    var k=values.length-i-1;
    if(values[k][0] == id){
      k=k+2;
      var in_time = Utilities.formatDate(new Date(), "GMT+8", "HH:mm:ss");
      sheet.getRange(k,8).setValue(in_time);
      return ContentService.createTextOutput("Check In Time "+in_time).setMimeType(ContentService.MimeType.TEXT);
      }
  }
}

Upvotes: 0

ADW
ADW

Reputation: 4247

In looking through the "values", you could go upwards/backwards to find the newer entries first (assuming newer are at the bottom):

  for (var i = (values.length - 1); i > -1; i--) {

To your other question, on how to check if a cell in column 8 is blank, you could try this: 

  if (values[i][0] == id && values[i][7] == "") {

Upvotes: 1

Related Questions