Paste values from one Google Sheet to another and remove duplicates based on ID column

Question

I have a similar situation to the one described on this question: two worksheets, with input data coming into the Feed sheet using the importxml function and a Data sheet where new rows get copied thanks to a script set to run daily.

However, the current script is creating daily duplicates. As such, I would like to adapt the answer provided on the question above so that the script checks the IDs on column F and only copies the rows with new IDs.

How should I update the section below that creates a hash to one that looks for the IDs on column F instead? Also my rows are consistent, so is it correct to assume I can just remove the relevant code lines towards the end?

The sample Google Sheet is available here.

function appendUniqueRows() {
  var ss = SpreadsheetApp.getActive();
  var sourceSheet = ss.getSheetByName('Get Data');
  var destSheet = ss.getSheetByName('Final Data');

  var sourceData = sourceSheet.getDataRange().getValues();
  var destData = destSheet.getDataRange().getValues();

  // Check whether destination sheet is empty
  if (destData.length === 1 && "" === destData[0].join('')) {
    // Empty, so ignore the phantom row
    destData = [];
  }

  // Generate hash for comparisons
  var destHash = {};
  destData.forEach(function(row) {
    destHash[row.join('')] = true; // could be anything
  });

  // Concatentate source rows to dest rows if they satisfy a uniqueness filter
  var mergedData = destData.concat(sourceData.filter(function (row) {
    var hashedRow = row.join('');
    if (!destHash.hasOwnProperty(hashedRow)) {
      // This row is unique
      destHash[hashedRow] = true;   // Add to hash for future comparisons
      return true;                  // filter -> true
    }
    return false;                   // not unique, filter -> false
  }));

  // Check whether two data sets were the same width
  var sourceWidth = (sourceData.length > 0) ? sourceData[0].length : 0;
  var destWidth = (destData.length > 0) ? destData[0].length : 0;
  if (sourceWidth !== destWidth) {
    // Pad out all columns for the new row
    var mergedWidth = Math.max(sourceWidth,destWidth);
    for (var row=0; row<mergedData.length; row++) {
      for (var col=mergedData[row].length; col<mergedWidth; col++)
        mergedData[row].push('');
    }
  }

  // Write merged data to destination sheet
  destSheet.getRange(1, 1, mergedData.length, mergedData[0].length)
           .setValues(mergedData);
}

I'm a novice in this world of Google Apps scripts, so do please let me know if I'm missing any crucial information. Thanks in advance for the help.

Answer 1

• You want to copy the values from "Feed" sheet to "Data" sheet.
• When the values are copied, you want to copy only new values which are not included in "Data" sheet.
• You want to choose the new values using the values of column "F".

If my understanding for your question is correct, how about this modification? In this modification, I modified the script in your shared spreadsheet.

Modification points:

• In your script, all values of "Feed" sheet are copied to "Data" sheet. So in order to choose only new values, I used the following flow.
  1. Retrieve the values from column "F". This is used for choosing the new values.
  2. Retrieve the new values using the values from column "F".
  3. Put the new values to "Data" sheet.

The script which reflected above flow is as follows.

Modified script:

This is your script in the shared spreadsheet. Please modify this script to below one.

function Copy() {
  var sss = SpreadsheetApp.openById('#####'); // this is your Spreadsheet key
  var ss = sss.getSheetByName('Feed'); // this is the name of your source Sheet tab
  var range = ss.getRange('A3:H52'); //assign the range you want to copy
  var data = range.getValues();
  var tss = SpreadsheetApp.openById('#####'); //replace with destination ID
  var ts = tss.getSheetByName('Data'); //replace with destination Sheet tab name
  ts.getRange(ts.getLastRow()+1, 1,50,8).setValues(data);// 49 value refers to number of rows, 8 to columns
}

function Copy() {
  var sss = SpreadsheetApp.openById('#####'); // this is your Spreadsheet key
  var ss = sss.getSheetByName('Feed'); // this is the name of your source Sheet tab
  var range = ss.getRange('A3:H52'); //assign the range you want to copy
  var data = range.getValues();
  var tss = SpreadsheetApp.openById('#####'); //replace with destination ID
  var ts = tss.getSheetByName('Data'); //replace with destination Sheet tab name

  // Below script was added.
  var values = ts.getRange("F3:F").getValues().filter(String);
  var copiedValues = data.filter(function(e) {return !values.some(function(f){return f[0] == e[5]}) && e.filter(String).length > 0});
  ts.getRange(ts.getLastRow() + 1, 1, copiedValues.length, copiedValues[0].length).setValues(copiedValues);
}