Compare two columns A in two sheets and generate unique values in sheet 1 using Google Apps Script

0
votes

I have two sheets with a dozen of columns and thousands of rows. Each sheet has a column A that contains a unique ID. I want to compare two sheets using only the column A as a point of comparison and generate a third sheet that contains entire rows with the unique ID that is in Sheet 1 but not in Sheet 2.

Here is a visualisation:

Sheet 1
+---+--------+-----+-----+
|   |   A    |  B  |  C  |
+---+--------+-----+-----+
| 1 | 1111   | xxx | zzz |
| 2 | 2222   | yyy | zzz |
| 3 | 2222-1 | zzz | xxx |
| 4 | 3333   | xxx | yyy |
+---+--------+-----+-----+
Sheet 2
+---+------+-----+-----+
|   |  A   |  B  |  C  |
+---+------+-----+-----+
| 1 | 1111 | xxx | zzz |
| 2 | 2222 | yyy | zzz |
| 3 | 3333 | xxx | yyy |
+---+------+-----+-----+

The desired function would compare Sheet 1 and Sheet 2 using A as the basis and the function would point out that the cell sheet1[A2] is unique, it would copy the entire 2:2 row, and then it would paste it into a newly generated sheet. The content of B and C is irrelevant in comparison.

I wanted to create a loop that would go and compare each cell in column A for both sheets and if sheet1.A[n] != sheet2.A[n], but it would work only if both sheets had the same lengths.

In the case I've specified above, the loop I've created with the help of the user Cooper finds that row 3 is unique and after that everything will be unique because of a misalignment.

The sheets will always be of different lengths and the uniques may appear at different spots (i.e. 2222-1 may end up the next time I'm making a comparison at row 5).

function compareSheetDrop(input) {

var ss = SpreadsheetApp.getActiveSpreadsheet();

//establish first sheet and get its data range
var dropSheet = ss.getSheetByName("Drop (2)");
var dropRange = dropSheet.getRange(2, 1, dropSheet.getLastRow() - 1, dropSheet.getLastColumn());
var vA1 = dropRange.getValues();

//establish the sheet with new data and get its data range
var compareSheet = ss.getSheets()[4];
var compareRange = compareSheet.getRange(2, 1, compareSheet.getLastRow() - 1, compareSheet.getLastColumn());
var vA2 = compareRange.getValues();

//establish the sheet with results
var resultSheet = ss.getSheetByName("ADQA");

for (var i = 0; i < vA1.length; i++) {

//i've tried to make the loop stop once it encounters a blank cell to avoid a type error, but doesn't work
    if (vA2[i][0] === '' || vA1[i][0] === '') {
        break;
        }

    else if (vA1[i][0] != vA2[i][0]) {
    Logger.log(vA1[i][0] + " & " + vA2[i][0]);
            resultSheet.appendRow(vA2[i]);
        }
    }
}

Even if my code doesn't provide results I want, there are also two problems with it: It loops until there's a TypeError: Cannot read property "0" from undefined. that I've tried to combat but to no avail.

Moreover, the appendRow method doesn't write data in already existing cells but inserts the row before the already existing ones - after running my test script 3 times the results sheet had thousands of columns and was barely operable.

Any ideas how I could modify my existing code to get what I want while avoiding the aforementioned issues? Or does it require a completely new approach? Any suggestions?

1
javascriptgoogle-apps-scriptgoogle-sheets

1 Answers

0
votes

Flow:

  • Use Array#map to get all of sheet1 col A in a array(say a).
  • Use Array#filter to filter sheet2 by the array above.
  • Use the filtered array directly in setValues()

Snippet:

var a1 = [[1,'x','z'],[2,'y','z'],[3,'m','z'],['2-1','x','z']];//sheet1ValuesArr
var a2 = [[1,'x','z'],[2,'y','z'],[3,'m','z']];//Sheet2ValuesArr
var a = a2.map(function(e){ return e[0]}); //Sheet2Col A : [1,2,3]
var filteredArr = a1.filter(function(e){return !~a.indexOf(e[0])});
console.log(filteredArr); //to use in setValues
resultSheet.getRange(1, 1, filteredArr.length, filteredArr[0].length).setValues(filteredArr)

References: