CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsmatching
TucsonTerry
TucsonTerry

Reputation: 55

Matching cell values in different Google Sheets

Users input text in cell A1 of sheet1. I want to find an exact match in column G of sheet2, and get the row of that match. I'm new to GAPS and wrote a script to find matches all on one sheet. How do I modify it to look at sheet2? 

function rowOfMatch(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var inputName = sheet.getRange("A1").getValue();

  for(var i = 0; i<data.length;i++){
    if(data[i][6] == inputName){ //[6] to search column G
      Logger.log((i+1))
      return i+1;
    }
  }
}

Upvotes: 0

Views: 77

Answers (1)

Cooper
Cooper

Reputation: 64110

function runOne(){
  var ss=SpreadsheetApp.getActive();
  var sh1=ss.getSheetByName('Sheet1');
  var sh2=ss.getSheetByName('Sheet2');
  var rg1=sh1.getRange(1,1,sh1.getLastRow(),1);
  var rg2=sh2.getRange(1,7,sh2.getLastRow(),1);
  var v1=rg1.getValues();
  var v2=rg2.getValues().map(function(r){return r[0];});
  for(var i=0;i<v1.length;i++) {
    var row=v2.indexOf(v1[i][0])+1;
    if(row>0) {
      sh1.getRange(i+1,2).setValue(row);
    }else{
      sh1.getRange(i+1,2).setValue('Not Found');
    }
  }
}

Animation:

enter image description here

Upvotes: 1

Related Questions