Google Sheets script - find matching tuples and copy data between sheets

Question

I have 2 sheets in the same spreadsheet, call them sheet1 and sheet2. In each sheet, every row describes some hardware component and its properties. The point of sheet2 is to eventually replace the outdated sheet1.

Simple example, (real sheets are hundreds of lines long):

sheet1:
componentId | prop1 | prop2 | prop3 | isvalid
---------------------------------------------
 1          | x1    | y1    | z1    | yes
 2          | x1    | y2    | z3    | yes
 3          | x2    | y1    | z1    | yes

sheet2:
componentId | quantity | prop1 | prop2 | prop3 | prop4 | isvalid
----------------------------------------------------------------
 15          | 4        | x1    | y1    | z1    |  w1   | TBD
 23          | 25       | x3    | y3    | z2    |  w1   | TBD
 33          | 3        | x1    | y2    | z3    |  w2   | TBD

The final column "isValid" in sheet1 has been manually populated. What I would like to do is write a script that iterates through sheet1, producing a tuple of the property values, and then looks for matching property value tuples in sheet2. If there is a match, I would like to copy the "isValid" field from sheet1 to the "isValid" field in sheet2.

What I have so far is the following, but I am experiencing a error "The coordinates or dimensions of the range are invalid" - see comment in code below showing where error is. And, the entire thing feels really hacky. Was hoping someone could maybe point me in a better direction? Thanks in advance.

function arraysEqual(a, b) {
  if (a === b) return true;
  if (a == null || b == null) return false;
  if (a.length != b.length) return false;

  for (var i = 0; i < a.length; ++i) {
    if (a[i] !== b[i]) return false;
  }
  return true;
}

function copySheetBasedOnRowTuples(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName('sheet 1 name');
  var sheet2 = ss.getSheetByName('sheet 2 name');

  s2data = sheet2.getDataRange().getValues()
  s1data = sheet1.getDataRange().getValues()
  for( i in s1data ){
     sheet1Tuple = [ s1data[i][1], s1data[i][2], s1data[i][3] ] 

     // Now go through sheet2 looking for this tuple,
     // and if we find it, copy the data in sheet1 column 4
     // to sheet2 column 6 for the rows that matched (i and j)
     for ( j in s2data){
        sheet2Tuple = [ s2data[j][2], s2data[j][3], s2data[j][4] ]
        if ( arraysEqual(sheet1Tuple, sheet2Tuple) ){
          // ERROR HAPPENS HERE
          sheet2.getRange(j, 6).setValue( sheet1.getRange( i, 4 ).getValue() )
        }
     }
  }
}

Tanaike Tanaike · Accepted Answer · 2018-01-18T00:59:09

The reason of error is the start number between array and range. The index of array starts from 0. The row and column of getRange() start from 1. So how about this modification?

From :

sheet2.getRange(j, 6).setValue( sheet1.getRange( i, 4 ).getValue() )

To :

sheet2.getRange(j+1, 7).setValue( sheet1.getRange( i+1, 5 ).getValue() )

If this was not useful for you, please tell me. I would like to modify.

Google Sheets script - find matching tuples and copy data between sheets

1 Answers

From :

To :