Can't copy more than one value when comparing two columns in two sheets for differences in Google Apps Script

Question

I am creating a script in Google Apps Script that would compare two sheets in a Spreadsheet row-by-row in a specific column containing unique IDs. If A[i] in Sheet(1) is different from A[i] in Sheet(2), I want the entire row with that cell to be copied to a new sheet. The sheets are really large, several thousand rows.

So far I have gotten to a point when the script copies a value once and then continues with the loop with no further results. Also, it takes ages and I suspect this might be very sub optimal. It's super difficult for me to debug in Google Apps Script and I'm at wits end. Here's what I have so far and my comments.

function compareSheetDrop(input) {

    var ss = SpreadsheetApp.getActiveSpreadsheet();

    //create a new blank sheet where the output will be stored
    ss.insertSheet(5);

    /*create references to sheets used by the script. 
    dropSheet is the sheet used as a basis, 
    compareSheet is a sheet possibly containing new values, 
    resultSheet is the blank sheet that will store new values
     */
    var dropSheet = ss.getSheetByName("Drop (2)");
    var compareSheet = ss.getSheets()[4];
    var resultSheet = ss.getSheets()[5];

    /*loop over the entire dropSheet and look for differences. 
The sheets have header rows*/
    for (var i = 2; i <= dropSheet.getLastRow(); i++) {

        /*variable j will represent the row number 
        where the difference will be pasted to 
        resultsSheet.A[j] etc.*/
        var j = 1;

        /*establish references to the cells 
        that will be used for comparison 
        (i.e. A[i] in dropSheet, A[i] in compareRange)*/
        var dropRange = dropSheet.getRange(i, 1);
        var compareRange = compareSheet.getRange(i, 1);

        /*establish references to the row 
        that will be copied from compareSheet 
        and to the row that 
        will be the destination in resultsSheet*/
        var resultRange = resultSheet.getRange(j, 1);
        var entireRow = compareSheet.getRange(i, 1);

        /*check if the unique value of 
        cell dropSheet.A[i] is different from 
        compareSheet.A[i] and whether they are not blanks. 
        If they are different, 
        copy entire row from compareSheet to the resultsSheet*/
        if (dropRange.getValues()[0] !== compareRange.getValues()[0] && dropSheet.getRange(i, 1).isBlank() == false && compareSheet.getRange(i, 1).isBlank() == false) {
            j++;
            entireRow.copyTo(resultRange, SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
        }
    }
}

I expect the resultSheets to be full of rows from compareSheet that are not present in dropSheet, but instead of a row I get one cell A1 populated and then nothing happens while the script is still running. I suspect the problem might be in the loop itself and in the copyTo, but I can't find the cause.

Are the sheets sorted? Where do you want the information to be added in sheet 2? — J. G.
– J. G., Commented Jun 10, 2019 at 16:38

Cooper · Accepted Answer · 2019-06-10 16:50:13Z

0

Try this:

function copyRowWhenAsDontMatch() {
  var ss=SpreadsheetApp.getActive();
  var sh1=ss.getSheetByName('Sheet1');
  var rg1=sh1.getRange(2,1,sh1.getLastRow()-1,sh1.getLastColumn());
  var vA1=rg1.getValues();
  var sh2=ss.getSheetByName('Sheet2');
  var rg2=sh2.getRange(2,1,sh2.getLastRow()-1,sh2.getLastColumn());
  var vA2=rg2.getValues();
  var sh3=ss.getSheetByName('Sheet3');//rows that dont match in sheet1
  sh3.clear();
  var sh4=ss.getSheetByName('Sheet4');//rows that dont match in sheet2
  sh4.clear();
  for(var i=0;i<vA1.length;i++) {
    if(vA1[i][0]!=vA2[i][0]) {
      sh3.appendRow(vA1[i]);
      sh4.appendRow(vA2[i]);
    }
  }
}

edited Jun 10, 2019 at 16:50

answered Jun 10, 2019 at 16:40

Cooper

64.2k7 gold badges29 silver badges65 bronze badges

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

4 Comments

pedrochristobal Over a year ago

Thanks, it really works! I guess my mistake was not operating on ranges of values, just like Alberto suggested? I've modified the code a little bit, but I'm still getting TypeError: Cannot read property "0" from undefined. at the end of the loop. I thought that by breaking the loop if either vA1 or vA2 are empty would help, but it doesn't. Any suggestions? if (vA2[i][0] === '' || vA1[i][0] === '') { break; }

pedrochristobal Over a year ago

I've started seeing an issue with my idea - the script only works properly if we assume that SheetA and SheetB are the exactly same length, which they are not. If they aren't , there will be a shift at one point and then the script will start printing out everything afterwards, and I just need uniques. Therefore, looping through would have to be much more sophisticated? Like store vA1[i][0] and then loop it through entire vA2[i][0] for matches?

Cooper Over a year ago

I think I need to see your data to help me to understand what you are trying to accomplish. Please update your question with a public link to your spreadsheet. Please remove any private information first.

pedrochristobal Over a year ago

Well, I asked for something different that I wanted to and youve answered correctly to that question. I will open a new question where I will rephrase my problem, I'll use you and your code as a reference and provide relevant data. Thank you very much for your help!

AMolina · Accepted Answer · 2019-06-10 16:10:35Z

If you are only comparing the first columns of each sheet, you can use the getSheetValues(startRow, startColumn, numRows, numColumns) method to get the values without having to check each cell against each other. When you get the sheets, you should get them like this:

var dropSheet = ss.getSheetByName("Drop (2)").getSheetValues(1,1,x,1);
var compareSheet = ss.getSheets()[4].getSheetValues(1,1,x,1);

Where x is the number of rows you have on each sheet. this would get you two arrays of all the values on your sheet. Iterating through these to check for the different rows should be much easier, then you can use the index where you find them to get the range of the row in the resultSheet to edit it and insert the rows you want.

This question has a good example on how to get the row's range: Google script select an entire row in a Google Spreadsheet

Link to the spreadsheet documentation in case there's any doubt about the methods: https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#getsheets

Collectives™ on Stack Overflow

Can't copy more than one value when comparing two columns in two sheets for differences in Google Apps Script

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related