Apps Script: Find and delete duplicates before adding new rows

Question

The script below copies rows from multiple sheets and append them to a master spreadsheet. But it ends up producing way too many duplicates. I mean it copies and appends the content from the other sheets without the possibility of checking, if these rows and their respective content already exist in the master spreadsheet.

Here is the script:

function copyAndAddNewEntries() {
  // Get ID of each source spreadsheet and name of each source sheet
  var spreadsheets = [
    {ssId: 'SOURCE_SHEET_ID1', sheetNames: ["Sheet1", "Sheet2",,,]},
    {ssId: 'SOURCE_SHEET_ID2', sheetNames: ["Sheet1", "Sheet2",,,]},
    ,,
    ,
    
  ];
  // Copy the relevant rows/content from each of the spreadsheets and sheets listed above
  var {values, max} = spreadsheets.reduce((o, {ssId, sheetNames}) => {
    SpreadsheetApp.openById(ssId).getSheets().forEach(s => {
      if (sheetNames.includes(s.getSheetName())) {
        var [, ...v] = s.getDataRange().getValues();
        var temp = v.filter(e => e.join("") != "");
        if (temp.length > 0) {
          o.values = o.values.concat(temp);
          var len = temp.length;
          o.max = o.max < len ? len : o.max;
        }
      }
    });
    return o;
  }, {values: [], max: 0});
  values = values.map(r => {
    var len = r.length;
    return len < max ? r.concat(Array(max - len)) : r;
  });
  // Add the rows/content that were copied above to the MASTER WORKSHEET
  var targetSheet = SpreadsheetApp.openById('TARGET_SHEET_ID').getSheetByName('TARGET_SHEET_NAME');
  targetSheet.getRange(targetSheet.getLastRow() + 1, 1, values.length, values[0].length).setValues(values);
}

I am wondering how I can add in the script below from Google Apps Script official website (and which I have slightly modified) to check my data before the new rows are appended to the master spreadsheet. All script examples I´ve seen so far do the job afterwards (so, only after the new rows have been added). But I need the check to happen before or during the process of adding these rows.

function removeDuplicates() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName("NAME_OF_MASTER_SPREADSHEET");
  var data = sheet.getDataRange().getValues();
  // var sheet = SpreadsheetApp.getActiveSheet();
  var newData = [];
  for (var i in data) {
    var row = data[i];
    var duplicate = false;
    for (var 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);
}

Thank you so much in advance for your help :)

First question: do the source sheets contain the identical rows? If it is the case, the code works fine, there is no error. And in this case you have three options: (1) remove redundant rows from source sheets, (2) check all rows on destination sheet and copy only unique ones, (3) copy all rows and then remove duplicated from destination sheet. Option 3 can be performed with standard command Data > Remove duplicates — Yuri Khristich
– Yuri Khristich, Commented Oct 25, 2021 at 15:12
@Yuri Khristich - I totally agree with you: Checking all rows on destination sheet and copying only unique ones would be the best solution. And that´s exactly what I am trying to code right now with my pretty poor apps script knowledge. Till then, I have no choice, but use Data --> Remove duplicates (and this is pretty annoying, as I have 100 of spreadshees and an even higher number of sheets). Thanks :) — Bob Hardball
– Bob Hardball, Commented Oct 25, 2021 at 15:18

Yuri Khristich · Accepted Answer · 2021-11-02 12:29:47Z

2

So you have the 2D array values. And you have the Master sheet with data that can be grabbed as a 2D array as well (with the same number of columns, I suppose). You can left only unique rows in the values array this way:

var master = [
    ['a', 'b', 'c'],
    ['d', 'e', 'f'],
    ['g', 'h', 'i'],
    ['j', 'k', 'l'],
];

var values = [
    ['a', 'b', 'c'], // <-- this line will be ignored
    ['g', 'h', 'i'], // <-- this line will be ignored
    ['m', 'n', 'o'], // <-- this line is unique
    ['p', 'q', 'r'], // <-- this line is unique
];

var joined_master = master.map(x => x.join()); // [[a,b,c],[d,e,f]] --> ['abc','def']

var unique_values = values.filter(x => !joined_master.includes(x.join()));

console.log(unique_values); // output: [ ['m', 'n', 'o'], ['p', 'q', 'r'] ]

But if you have 100 of spreadshees and an even higher number of sheets there can be problem with time limit for the script.

Update

Suppose your script works fine. It gives you the array values after these lines:

values = values.map(r => {
    var len = r.length;
    return len < max ? r.concat(Array(max - len)) : r;
  });

And the next line gives you the sheet targetSheet:

var targetSheet = SpreadsheetApp.openById('TARGET_SHEET_ID').getSheetByName('TARGET_SHEET_NAME');

After this line you can get all rows from the target sheet this way:

var target_data = targetSheet.getRange(1,1,targetSheet.getLastRow(),values[0].length).getValues();

Now you can remove from the array values all the rows that are in the array target_data this way:

target_data = target_data.map(x => x.join());
values = values.filter(x => !target_data.includes(x.join()));

After that the array values will contain only unique rows.

Now you can continue your script as usual:

targetSheet.getRange(targetSheet.getLastRow() + 1, 1, values.length, values[0].length).setValues(values);

Technically it should work. But actually if you have too much sheets and rows the script can excess the time limit.

edited Nov 2, 2021 at 12:29

answered Oct 25, 2021 at 17:32

Yuri Khristich

14.6k3 gold badges11 silver badges25 bronze badges

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

5 Comments

Bob Hardball Over a year ago

thank you so much for trying to help me. But I am afraid I am not experienced enough to understand what you mean :(.

Yuri Khristich Over a year ago

It's just your code looks much more complicated than my snippet. So I thought you're able to use my help. If not, I don't know how I can help. I'll try to elaborate my answer as soon I as I get some free time. Let me know, if it's already doesn't matter.

Bob Hardball Over a year ago

I can certainly wait till you´ve elaborated your answer. Thank you so much again :)

Yuri Khristich Over a year ago

I've just updated my answer.

Bob Hardball Over a year ago

After almost 3 days spent deeply analyzing your answer and going through some additional apps script documentation, I was finally able to successfully adapt my script based on your solution. Thank you so much!!!!

Collectives™ on Stack Overflow

Apps Script: Find and delete duplicates before adding new rows

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related