Google Sheet Scripts: How to compare 2 columns to remove duplicates

1
votes

There are 3 duplicate articles from the same domain in Sheet1: https://docs.google.com/spreadsheets/d/1pExqHJQubnSPDKczkF9HMA2QN1cxTYmzyewQdugDRYs/edit#gid=0

Goal: I'd like to remove articles that have the same title AND domain.

Sheet DesiredResult has the desired result.

I'd like to modify this filter script to compare article title(column a) & domain(column C), if they are the same, then remove:

function removeDuplicates() {
    const sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
    const data = sheet.getDataRange().getValues();

    var temp = {}; // Added
    var newData = data.filter(function(e) { // Added
        if (!temp[e[1]]) {
            temp[e[1]] = e[1];
            return true;
        }
        return false;
    });

    sheet.clearContents();
    sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
}

Right now it just looks at the link if they're the same. It's not using an if statement so I'm not sure how to add a comparison for 2 columns. Any help is appreciated, thanks!

Update: I've tried looking at !temp[e[1]] to see if there are values to compare in an if statement but it shows as undefined so I'm stuck where to add the second column comparison in this section.

Reference: Previous question, asked to compare 1 column - Google Sheet Scripts: How to compare only one column to remove duplicates

2
javascriptgoogle-apps-scriptgoogle-sheets
Not sure why this is too broad, can the person who voted that explain so I can improve in the future? - sojim2
There isn't any recognizable attempt made to modify the code to achieve your goal. You just copy pated previously provided code. - TheMaster
Fair, added an update section where I got stuck when I looked at temp[e[1]] - sojim2
You might want to practice this and the next 4 chapters there(especially Array#filter). - TheMaster

2 Answers

2
votes
  • You want to remove the duplicated rows by comparing the column "A" and "C".
    • In your shared spreadsheet, you want to modify from Sheet1 to DesiredResult.
  • You want to achieve this using Google Apps Script.

If my understanding is correct, how about this modification? In this modification, the method of removeDuplicates() is used. This was added at July 26, 2019. So your question had been posted at July 15, 2019, unfortunately, at that time, this method had not been added yet. Please think of this as just one of several answers.

Modified script:

const sheet = SpreadsheetApp.getActive().getSheetByName('Combined');
sheet.getDataRange().removeDuplicates([1, 3]);

Note:

  • When you test above modified script to the shared Spreadsheet, please modify the sheet name from Combined to Sheet1.

Reference:

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

Added:

If you want to modify your script, how about the following modification? Also please think of this just one of several answers.

From:

if (!temp[e[1]]) {
    temp[e[1]] = e[1];

To:

if (!temp[e[0] + e[2]]) {
    temp[e[0] + e[2]] = e[1];
0
votes
function removeDuplicates() {
  var sheet=SpreadsheetApp.getActive().getSheetByName('Sheet1');
  var data=sheet.getDataRange().getValues();
  var newData = data.filter(function(r) {
    return !(r[0]==r[2])//comparing column A with Column C
  }); 
  sheet.clearContents();
  sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
}

Test it on this Data:

1,a,a
2,b,a
3,c,a
4,a,a
5,b,a
6,c,6
7,a,7
8,b,8
9,c,9

Array.filter()