CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
jradata
jradata

Reputation: 59

Google Script to copy row of data when cell value match

I have been using some code to update values between two sheets, where the values in the first columns are equal transfer across the next matching rows for the first column. Now i need to modify the code to transfer the entire row for all columns data, like so (for potentially a lot of columns):

SHEET 1

ID  ID2 ID3 Transfer1   Transfer2   Transfer3
1   11  44  Foo         Loo          Klo
3   33  66  boo         Sho          Loo
4   44  77  Sho         Boo          Foo

SHEET 2 STARTS:

ID
1
4
3

SHEET 2 BECOMES:

ID  ID2 ID3 Transfer1   Transfer2   Transfer3
1   11  44  Foo         Loo          Klo
4   44  77  Sho         Boo          Foo
3   33  66  boo         Sho          Loo

I have been using the following code:

function updateEntrees() {
  var ss=SpreadsheetApp.openById('1IPmsw4a0_OXn7IPKFLkguBtkUG3wxE4zvtdi5uTr3ZM');
  var sh1=ss.getSheetByName('Sheet1');
  var rg1a=sh1.getRange(2,1,sh1.getLastRow()-1,1); 
  var vA1a=rg1a.getValues();
  var rg1b=sh1.getRange(2,2,sh1.getLastRow()-1,1); 
  var vA1b=rg1b.getValues();

  var sh2=ss.getSheetByName('Sheet2');
  var rg2a=sh2.getRange(2,1,sh2.getLastRow()-1,1); 
  var vA2a=rg2a.getValues();
  var rg2b=sh2.getRange(2,2,sh2.getLastRow()-1,1); 
  var vA2b=rg2b.getValues();
  for(var i=0;i<vA1a.length;i++) {
    for(var j=0;j<vA2a.length;j++) {
      if(vA1a[i][0]==vA2a[j][0]) {
        vA2b[j][0]=vA1b[i][0]
      }
    }
  }
  rg2b.setValues(vA2b);
}

Any help would be appreciated.

Upvotes: 1

Views: 1049

Answers (1)

Tanaike
Tanaike

Reputation: 201378

  • You want to achieve the situation from "Sheet1" to "Sheet2" as shown in the top of your question.

If my understanding is correct, how about this answer? Please think of this as just one of several answers.

Pattern 1:

If your script is modified, how about the following modification? The modification point is that it retrieves all columns using getLastColumn() and it is the same between the array length of vA1b and vA2b. And, when the value of column "A" is the same between "Sheet1" and "Sheet2", the row of vA1b is copied to the row of vA2b.

Modified script:

function updateEntrees() {
  var ss=SpreadsheetApp.openById('1IPmsw4a0_OXn7IPKFLkguBtkUG3wxE4zvtdi5uTr3ZM');
  var sh1=ss.getSheetByName('Sheet1');
  var rg1a=sh1.getRange(2,1,sh1.getLastRow()-1,1); 
  var vA1a=rg1a.getValues();
  var rg1b=sh1.getRange(2,2,sh1.getLastRow()-1,sh1.getLastColumn() - 1);  // Modified
  var vA1b=rg1b.getValues();

  var sh2=ss.getSheetByName('Sheet2');
  var rg2a=sh2.getRange(2,1,sh2.getLastRow()-1,1); 
  var vA2a=rg2a.getValues();
  var rg2b=sh2.getRange(2,2,sh2.getLastRow()-1,sh1.getLastColumn() - 1);  // Modified
  var vA2b=rg2b.getValues();
  for(var i=0;i<vA1a.length;i++) {
    for(var j=0;j<vA2a.length;j++) {
      if(vA1a[i][0]==vA2a[j][0]) {
        vA2b[j]=vA1b[i];  // Modified
      }
    }
  }
  rg2b.setValues(vA2b);
}

Pattern 2:

As other pattern, how about the following script?

Sample script:

function updateEntrees() {
  var ss = SpreadsheetApp.openById('1IPmsw4a0_OXn7IPKFLkguBtkUG3wxE4zvtdi5uTr3ZM');
  var sheet1 = ss.getSheetByName('Sheet1');
  var sheet2 = ss.getSheetByName('Sheet2');

  var lastCol1 = sheet1.getLastColumn();
  var value1 = sheet1.getRange(2, 1, sheet1.getLastRow() - 1, lastCol1).getValues();
  var obj = value1.reduce(function(o, e) {
    o[e[0]] = e;
    return o;
  }, {});
  var range2 = sheet2.getRange(2, 1, sheet2.getLastRow() - 1, lastCol1);
  var result = range2.getValues().map(function(row) {return row[0] in obj ? obj[row[0]] : row});
  range2.setValues(result);
}

References:

If I misunderstood your question and this was not the result you want, I apologize.

Answer for additional request:

At the reply comment, the following request was added.

It is possible to modify this so that if there a new value not present in sheet2 but present in sheet1 to insert the entire new row as a new line item?

For above additional request, the modified script is as follows.

Modified script:

function updateEntrees() {
  var ss = SpreadsheetApp.openById('1IPmsw4a0_OXn7IPKFLkguBtkUG3wxE4zvtdi5uTr3ZM');
  var sheet1 = ss.getSheetByName('Sheet1');
  var sheet2 = ss.getSheetByName('Sheet2');

  var lastCol1 = sheet1.getLastColumn();
  var value1 = sheet1.getRange(2, 1, sheet1.getLastRow() - 1, lastCol1).getValues();
  var obj = value1.reduce(function(o, e) {
    o[e[0]] = e;
    return o;
  }, {});
  var range2 = sheet2.getRange(2, 1, sheet2.getLastRow() - 1, lastCol1);
  var result = range2.getValues().map(function(row) {return row[0] in obj ? obj[row[0]] : row});

  ///// Following script was added
  var temp = value1.filter(function(row1) {return !result.some(function(row2) {return row1[0] == row2[0]})});
  if (temp.length > 0) {
    Array.prototype.push.apply(result, temp);
    range2 = range2.offset(0, 0, result.length, result[0].length);
  }
  /////

  range2.setValues(result);
}

Upvotes: 1

Related Questions