CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets-api
codecumber
codecumber

Reputation: 112

Update or Replace Row data in other sheet when unique id is found - Google Appscript

I need someone to modify this script that I found here to update or replace the row value of the sheet2 from sheet1 when a unique ID is found. This only works up to the 2nd column..I need something to update or replace rows in the sheet2 up to 10 columns or more. I have a sample spreadsheet. If you see on sheet2, some row values are missing or not yet updated, so I need those rows to be updated or replaced what's in sheet1.

Here is the spreadsheet:

https://docs.google.com/spreadsheets/d/100sjGCr0HSdJE1AERCJ9bGKiu3Vs2KxrvGIIjIrOjHw/edit?usp=sharing

Thanks for helping and sharing your knowledge.

function updateEntrees() {
  var ss=SpreadsheetApp.getActive();
  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);

Upvotes: 0

Views: 608

Answers (1)

Kishan
Kishan

Reputation: 1810

Try the following code, in the second line, change the value of "numColumns" as per your need:

function updateEntrees() {
  var numColumns = 9;
  var ss = SpreadsheetApp.getActive();
  
  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,numColumns);
  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,numColumns);
  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];
      }
    }
  }
  rg2b.setValues(vA2b);
};

Upvotes: 2

Related Questions