Combining different functions into one

Question

I have two separate scripts that work. The first checks a column in one(Master)worksheet against a column in a different(Dependent)worksheet. If it finds a match, it copies that row from the (Dependent) to the (Master) worksheet.

The second script compares two arrays(one from the Master and one from the Dependent) and changes the background on the Master if they are not the same.

Since both compare arrays, is there a way to combine these two functions?

I've gotten some help from these boards previously and have tried to combine the code on my own. However, my knowledge of scripting...specifically arrays is quite limited.

var MSsId='Master_Sheet_ID';
var mshName='Sheet1';
var DSsId='Dependent_Sheet_ID';
var dshName='Sheet2';

function findMatchesAndCopy() {
  var mss=SpreadsheetApp.openById(MSsId);
  var msh=mss.getSheetByName(mshName)
  var mrg=msh.getRange(3,1,msh.getLastRow()-2,5);
  var mvA=mrg.getValues();
  var dss=SpreadsheetApp.openById(DSsId);
  var dsh=dss.getSheetByName(dshName);
  var drg=dsh.getRange(3,1,dsh.getLastRow()-2,5);
  var dvA=drg.getValues();
  var mmA=mvA.map(function(r){return(r[0])});
  for(var i=0;i<dvA.length;i++) {
    var idx=mmA.indexOf(dvA[i][0]);
    if(idx>-1){
      mvA[idx]=dvA[i];
    }
  }
  mrg.setValues(mvA);                
}

// var bgs=mrg.getBackgrounds();                   This is the code
//  for(var i=0;i<bgs.length;i++) {                that checks the 
//    for(var j=0;j<bgs[i].length;j++) {           array background.
//      if(mvA[i][j]!=dvA[i][j]) {                 I can't figure out
//        bgs[i][j]='#ffff00';                     how to adapt and insert
//      }                                          this into the rest of 
//    }                                            the code
//  }
//  mrg.setBackgrounds(bgs);
//

In short, I'd like to have this script compare a Dependent sheet to the Master sheet...and if there are any differences, copy and highlight them on the Master

In your scripts, it seems that the script works with the following situation. The script 1 uses the rows by comparing the value of column "A". The script 2 (Is that the commented script?) uses the cells by comparing all cells. So can I ask you about your goal? Unfortunately, I couldn't understand about your goal from combine these two functions. I would like to correctly understand your goal and think of the solution. I apologize for my poor English skill. — Tanaike
– Tanaike, Commented Jul 31, 2019 at 0:25

Cooper · Accepted Answer · 2019-07-31 15:32:14Z

1

Combined:

var MSsId='Master_Sheet_ID';
var mshName='Sheet1';
var DSsId='Dependent_Sheet_ID';
var dshName='Sheet2';

function findMatchesAndCopy() {
  var matches=[];
  var differences=[];
  var mss=SpreadsheetApp.openById(MSsId);
  var msh=mss.getSheetByName(mshName)
  var mrg=msh.getRange(3,1,msh.getLastRow()-2,5);
  var mvA=mrg.getValues();
  var bgs=mrg.getBackgrounds();                  
  var dss=SpreadsheetApp.openById(DSsId);
  var dsh=dss.getSheetByName(dshName);
  var drg=dsh.getRange(3,1,dsh.getLastRow()-2,5);
  var dvA=drg.getValues();
  var mmA=mvA.map(function(r){return(r[0])});
  for(var i=0;i<dvA.length;i++) {//This is looping over the Dependant sheet
    var idx=mmA.indexOf(dvA[i][0]);
    if(idx>-1){
      mvA[idx]=dvA[i];
      matches.push({idx:idx,mv:mvA[idx],dv:dvA[i]});
    }
  }
  mrg.setValues(mvA);
  Logger.log('\nmatches: %s',matches);
  var bgs=mrg.getBackgrounds();                  
 for(var i=0;i<bgs.length;i++) {//this is looping over the Master Sheet               
   for(var j=0;j<bgs[i].length;j++) {          
     if(mvA[i] && dvA[i] && mvA[i][j]!=dvA[i][j]) {                
       bgs[i][j]='#ffff00'; 
       differences.push({r:i+2,c:j+1,mv:mvA[i][j],dv:dvA[i][j]});
     }
     if(mvA[i] && !dvA[i]) {
       bgs[i][j]='#ffff00'; 
       differences.push({r:i+2,c:j+1,mv:mvA[i][j],dv:'No Data'});
     }
   }                                           
 }
 mrg.setBackgrounds(bgs); 
 Logger.log('\ndifferences: %s',differences);
}

edited Jul 31, 2019 at 15:32

answered Jul 31, 2019 at 3:31

Cooper

64.2k7 gold badges29 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Gateway Intelligence Over a year ago

I am getting the Type Error: Cannot read property "0.0" from undefined on the line with "if(mvA[i][j]!=dvA[i][j]) {

Cooper Over a year ago

Most likely the problem was that you master data was larger than your dependent data. I added some additional check to avoid that problem. However, this code does not check for the situation where dependent data is larger than master data.

Gateway Intelligence Over a year ago

The Dependent should never be larger then the Master. I made one small edit to differentiate the backgrounds.(ffff00 to ff0000 on one of them). The result is that it highlighted the number of rows that varied, not the rows that varied.

Collectives™ on Stack Overflow

Combining different functions into one

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related