CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsduplicates
user12736831
user12736831

Reputation:

Get unique value and discarding duplicates from data using Google Apps Script

I receive data every day and I want to compare this new data with my previous data and get the rows that are not found in both datasets. I found the following script from Google Developer site:

function removeDuplicates() {
  let sheet = SpreadsheetApp.getActiveSheet();
  let data = sheet.getDataRange().getValues();
  let newData = [];
  for (let i in data) {
    let row = data[i];
    let duplicate = false;
    for (let j in newData) {
      if (row.join() == newData[j].join()) {
        duplicate = true;
      }
    }
    if (!duplicate) {
      newData.push(row);
    }
  }
  sheet.clearContents();
  sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
}

The working of this script is as follows:

Data before Script runs:

6009608648960   5.7385  25 (Duplicate to 3rd row)
6009685850638   5.7385  53 (Duplicate to 4th row)
6009608648960   5.7385  25 (Duplicate to 1st row)
6009685850638   5.7385  53 (Duplicate to 2nd row)
6009685850638   5.7385  55 (Unique row - Desired Output)

Data After Script runs:

6009608648960   5.7385  25
6009685850638   5.7385  53
6009685850638   5.7385  55

So it just removed one instance of duplicate rows instead of fully discarding the duplicate rows. The expected result should be:

Expected result:
6009685850638 5.7385 55

Can we modify the above script or use any formula so that it can get our required data? Any guidance would be much appreciated.

Upvotes: 1

Views: 174

Answers (1)

Tanaike
Tanaike

Reputation: 201338

In your situation, how about the following flow?

  1. Retrieve all values from the sheet.
  2. Create a Map object as the key of all column values. In this case, all rows are included.
  3. When the length of each array is not 1, it is considered that the values are duplicated. An array is created using this.
  4. Put the created array on the sheet.

When this flow is reflected in your script, how about the following modification?

Modified script:

function removeDuplicates() {
  let sheet = SpreadsheetApp.getActiveSheet();
  let data = sheet.getDataRange().getValues();

  // --- I modified the below script.
  let newData = [...data.reduce((m, r) => {
    var k = r.join("");
    return m.set(k, m.has(k) ? [...m.get(k), r] : [r]);
  }, new Map()).values()].filter(e => e.length == 1).flat();
  // ---

  sheet.clearContents();
  sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
}
  • When this script is run using your sample values, your expected values are obtained.

References:

Upvotes: 0

Related Questions