Find matches for two columns on two sheets google script

0
votes

Im am trying to match 2 columns (A:B) in sheet1 with 2 columns in sheet2 (A:B) and if there is a match, copy contents of column C matching row in sheet1 to matching row in sheet2.

I've tried to adapt several scripts without success. The code below comes closest to my requirements, but with my limited knowledge of script I haven't been able to adapt it to my exact needs.

Sheet1
A       B               C
Week    Rotation    Working
Week1   11          In
Week1   5           In
Week1   4           In
Week1   3           In
Week1   3           Off
Week1   7           Off

Sheet2
A       B               C
Week    Rotation    Working
Week1   6
Week1   5
Week1   4
Week1   3
Week1   3
Week1   11              (In should be copied to here)

My code:

function MatchColumns(){
  // gets spreadsheet A and the range of data
  var sheetA 
=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Testa");
  var dataA = sheetA.getRange(2, 1, sheetA.getLastRow(), 
2).getValues();

   // gets spreadsheet B and the range of data
  var sheetB = 
 SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Test2");
  var dataB = sheetB.getRange(2, 1, sheetB.getLastRow(), 
 1).getValues();

  // Added
  var res = [];
  for (var b in dataB) {
    for (var a in dataA) {
       if (dataA[a][0] == dataB[b][0]) res.push([dataA[a][3]]);
     }
     if (b != res.length - 1) res.push([""]);
   }
   sheetB.getRange(2, 2, res.length, res[0].length).setValues(res);
2
google-apps-scriptgoogle-sheets
You could get the same result using formulas. For example, on Sheet1, you could combine the values in columns A and B in column D. And then look up that combined value from Sheet2. I'd use a separator like "Week1-11" so as to differentiate from "Week11-1". - ADW
Thankyou, very helpful, I'd got bogged down. Different perspective to achieve same result. - Andy Mac

2 Answers

0
votes

  1. Note that JavaScript is one of the many languages that use 0-base indexing. So res.push([dataA[a][3]]) is placing the 4th value from the row into the result array, i.e. Column D.

  2. Your dataA and dataB variables don't actually include the column C data, as you initialized them with only 2 columns of data. So dataA[a][2] and dataA[a][3] are both undefined.

  3. You probably don't want to collect these new values into an array via push, as this will lose the correlation between which row you matched in A & B, and which row you write into. To avoid losing existing information in column C, you need to read it from sheet 2 and assign to the specific index:

var destC = sheet2.getRange(2, 3, sheet2.getLastRow() - 1, 1).getValues();
/** Find matched rows */
   ...
  destC[b][0] = dataA[a][2];
 ...
// Lastly, write values
sheet2.getRange(2, 3, destC.length, 1).setValues(destC);
0
votes

Try using a nested for to iterate through the Values in Sheet1 and Sheet2 and compare them with 

function MatchColumns(){
  // gets spreadsheet A and the range of data
  var sheetA = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var dataA = sheetA.getRange(2, 1, sheetA.getLastRow() - 1, 2).getValues();

  // gets spreadsheet B and the range of data
  var sheetB = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
  var dataB = sheetB.getRange(2, 1, sheetB.getLastRow() - 1, 2).getValues();

  for (var x = 0; x < sheetA.getLastRow() - 1; x++){
    for (var y = 0; y < sheetB.getLastRow() - 1; y++){
      if (dataA[x][0] == dataB[y][0]){
        if (dataA[x][1] == dataB[y][1]){
          sheetB.getRange(y + 2, 3).setValue(sheetA.getRange(x + 2, 3).getValue());
        }
      }
    }    
  }
}

This copies the value of column C in Sheet1 to column C in Sheet2 if the corresponding cells in columns and B match.