Comparing two columns, same row, for first value that doesn't match

Question

I have two sheets, one is a mirror sheet, "Sheet2," that I use to store the values of the other sheet, "Sheet1." My goal is to have a function compare the two sheets for differences. The best way i could think of was by comparing column A from Sheet1 to column A from Sheet2. I found a few functions that compared 2 columns but it did it looking for values from one column and finding it in the other column. Or by returning all the values in those cells that had a matching value, regardless of what row it was in. But I don't want the values in the cells, necessarily. I want to find the first row where the two columns stop matching. I'm fairly new to Javascript so I still can't comprehend the whole for (var j = 0; j < range.length; j++) stuff.

But I'm sure I will need to know how to use it for this function I need. Here's what I tried using but instead of giving me row ranges, it gave me an array of values that were the same, if I changed it to if(lookup[i][0]!==range[j][0]){ it gave me all the possible combinations that weren't matching. This is from stackoverflow.com/questions/42044903

function findDifference() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s=ss.getSheetByName("Sheet1")
  var lr=s.getLastRow()
  var lookup = s.getRange(2,1,lr-1,2).getValues();
  var s1=ss.getSheetByName("Sheet2") 
  var lr1=s1.getLastRow()
  var range = s1.getRange(2,1,lr1-1,2).getValues();
  var lookupRange = [];
  for (var i = 0; i < lookup.length; i++) {
     for (var j = 0; j < range.length; j++) {
     var  test=lookup[i][0]
         if(lookup[i][0]!==range[j][0]){
           lookupRange.push([range[j][0],range[j][1],lookup[i][0],lookup[i][1],]);
     }}}
   s1.getRange(10,1,lookupRange.length,4).setValues(lookupRange); 
}

I feel like there's a very similar function for what I'm trying to do that already exists, but I can't seem to find it or come up with how it would work because I'm new and don't know all the tricks. Something like:

var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s1=ss.getSheetByName("Sheet1")
  var s2=ss.getSheetByName("Sheet2")
var ColumnA1 = s1.getRange('A:A').getValues()
var ColumnA2 = s2.getRange('A:A').getValues()
var Row = Function()
///Some function I can't think of using where
if(ColumnA1 + Row !== ColumnA2 + Row){
???.getRow()
}

Answer 1

The code that you had was "kinda' helpful but it did not solve your particular question. On the other hand, your if(ColumnA1 + Row !== ColumnA2 + Row){ wasn't really helpful either.

Regrettably you DO need to "comprehend the whole for (var j = 0; j < range.length; j++) stuff", though it isn't actually that complicated.

In the following answer, there are basically three elements.

• setup sheet1, and get the data
• setup sheet2, and get the data

• loop through the rows and compare the value on a given line from one sheet to the other.

• the for statement signifies the loop

• i is simply a counter variable
• i=0 means that the starting value is zero. In javascript arrays, zero always the first value set.
• i < Sheet1Data.length signifies how many time the loop will run. In this case, it will run while i is less then the number of lines in the array. Remember, i starts with zero, so "less than" the totoal number of lines will be fine.
• i++ means that each time the code loops, it increments i by one.. So, i starts with 0, then 1, 2, 3 and so on.

How to find the first row where the two columns stop matching

• View the Logs (View > Logs).

You can see on line 32 and 38 of the code Logger.log statements. These record the line number and whether the line values in each sheet match.

---

function so56195933() {
  // setup Spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // setup Sheet1
  var s1 = ss.getSheetByName("Sheet1")
  var s1LastRow = s1.getLastRow();
  //Logger.log("DEBUG: Sheet 1 Last row = "+s1LastRow);
  var Sheet1DataRange = s1.getRange(1,1,s1LastRow);
  var Sheet1Data = Sheet1DataRange.getValues();
  //Logger.log("DEBUG: Sheet 1 data range = "+Sheet1DataRange.getA1Notation());
  var Sheet1length = Sheet1Data.length;
  //Logger.log("DEBUG: Sheet1 length = "+Sheet1length);

  // setup Sheet2
  var s2=ss.getSheetByName("Sheet2") 
  var s2LastRow=s2.getLastRow();
  //Logger.log("DEBUG: Sheet 2 Last row = "+s2LastRow);
  var Sheet2DataRange = s2.getRange(1,1,s2LastRow);
  var Sheet2Data = Sheet2DataRange.getValues();
  //Logger.log("DEBUG: Sheet 2 data range = "+Sheet2DataRange.getA1Notation());
  var Sheet2length = Sheet2Data.length;
  //Logger.log("DEBUG: Sheet2 length = "+Sheet2length);

  // Loop through rows compare value per each sheet
  for (var i = 0; i < Sheet1Data.length; i++) {
    var s1data = Sheet1Data[i][0];
    var s2data = Sheet2Data[i][0];
    //Logger.log("DEBUG: Line: "+i+", s1data: "+s1data+" Vs s2data: "+s2data);
    if (s1data !=s2data){
      // sheets values don't balance
      Logger.log("Line: "+i+". Sheets are NOT equal. Sheet1 = "+s1data+", Sheet2 = "+s2data);
      return false;
    }
    else
    {
      // sheets values balance
      Logger.log("Line: "+i+". Sheets are equal, value: "+s1data);
    }
  }
}

---

This is my test data