CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Sebastian
Sebastian

Reputation: 130

How to use Vlookup within an OnEdit function

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.

Upvotes: 0

Views: 355

Answers (2)

Cooper
Cooper

Reputation: 64032

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('');
      }
    }
  }
}

Upvotes: 2

Aleister Tanek Javas Mraz
Aleister Tanek Javas Mraz

Reputation: 517

I believe that changing this line:

sheet.getRange(r.getRow(),r.getColumn()+1.setValue(data[i][2]);

to this:

sheet.getRange(r.getRow(),r.getColumn()+1).setValue(data[i][1]);

should solve your issue.

data[i][2] corresponds to the i-th row and the 3rd column (column A), but I believe you want data[i][1], which would be column B.

Upvotes: 0

Related Questions