How to use Vlookup within an OnEdit function

Question

So I am trying to create an attendance system. Basically what I want is for people to be able to type in their ID number in a sheet called attendance. Upon entering the information in that column, the adjacent column (column 2) will populate with their name and the next column (the third column) will populate with the time of entry. The time of entry is calculated using new Date(). However I planned on having it retrieve the name from a master list located on a sheet named "list". The "list" sheet has the ID's in column A and the names in Column B. So what I am trying to do is have the google script emulate Vlookup by looking at the recently edited cell, searching that value in "list" column A then return the corresponding "list" column B value in the adjacent column to the cell that was just edited. I want to be able to do this several times throughout the "attendance" sheet. So basically, each day we take attendance will occupy three columns within the "attendance" sheet.

function onEdit(e) {
var sheet = e.source.getActiveSheet();
if (sheet.getName() === "Attendance") {
    var r = e.source.getActiveRange();
    if (r.getRow() != 1 && r.getColumn()%3 != 0 && r.getColumn()%3 != 2) {
    sheet.getRange(r.getRow(),r.getColumn()+2).setValue(new Date());
    var popsheet = SpreadsheetApp.getActiveSpreadsheet.GetSheetByName("list")
    var data=popsheet.getRange("A1:D100").getValues();
    for(i=0;i<data.length;++i){
    if (data[i][0]==r.GetValue()){
    sheet.getRange(r.getRow(),r.getColumn()+1).setValue(data[i][1]);
    }
    }
}

The date populates just fine but the name does not populate. I have not received any error messages. I tried changing everything written after var popsheet = ... to only sheet.getRange(r.getRow(),r.getColumn()+1.setValue("test") and it does in fact return "test" in the adjacent column of the cell edited. So that leads me to believe that the issue is with my attempt to emulate Vlookup.

For one, you seem to have a missing closing parenthesis before your last .setValue call. Also, since data[][] has a starting index of 0, you'll want to try data[][1] to get column B's data; see my answer below for more of the same. — Aleister Tanek Javas Mraz
I notice one of your worksheet's name is "LIST", and you are calling .getSheetByName("list"). Could it be case-sensitivity ? — Aleister Tanek Javas Mraz
ive tried it just about every way and nothing has worked. I changed it back to all lowercase just in case though — Sebastian
Happy to help further, but you should learn how to use Logger.log() if you don't already know. After your third "if" statement, you can Logger.log("Data: " + data[i][1]") to see if you are getting the correct values. You can also Logger.log("All data: " + data) to see the entire set. That should help get you closer if you are not already doing this. — Aleister Tanek Javas Mraz

Cooper Cooper · Accepted Answer · 2019-08-08T18:27:14

Try this:

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  if(sheet.getName()=="Attendance") {
    if (e.range.rowStart > 1 && e.range.columnStart % 3 == 1) {
      var sh=e.source.getSheetByName("list");
      e.source.toast(sh.getName());
      var data=sh.getRange(2,1,sh.getLastRow(),4).getValues();
      for(i=0;i<data.length;i++){
        if (data[i][0]==e.value){
          e.range.offset(0,1).setValue(data[i][2]);
          e.range.offset(0,2).setValue(Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "MM/dd/yyyy HH:mm:ss"));
        }
      }
    }
  }
}

This will clear next two columns on same row if name is not found.

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  if(sheet.getName()=="Attendance") {
    if (e.range.rowStart > 1 && e.range.columnStart % 3 == 1) {
      var sh=e.source.getSheetByName("list");
      e.source.toast(sh.getName());
      var data=sh.getRange(2,1,sh.getLastRow(),4).getValues();
      var found=false;
      for(i=0;i<data.length;i++){
        if (data[i][0]==e.value){
          e.range.offset(0,1).setValue(data[i][2]);
          e.range.offset(0,2).setValue(Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "MM/dd/yyyy HH:mm:ss"));
          found=true;
        }
      }
      if(!found) {
        e.range.offset(0,1).setValue('');
        e.range.offset(0,2).setValue('');
      }
    }
  }
}

How to use Vlookup within an OnEdit function

2 Answers