Google Sheet App Script - How to only setValues only unique values

• You want to put the values, which removed the duplicated links, to the target sheet (in this case, it's Combined sheet.).
• The duplicated links are checked from the target sheet and source sheets. In this case, the target sheet and source sheets are Combined, Sheet2 and Sheet3, respectively.
  • In your sample Spreadsheet, you want to put the following rows to the target sheet.
    • https://thehill.com/policy/national-security/department-of-homeland-security/460158-new-hampshire-border-patrol BorderSecurity
    • https://abcnews.go.com/International/climate-change-frontier-worlds-northernmost-town/story?id=65381362 ClimateChange
• You want to achieve this by modifying your Google Apps Script.

If my understanding is correct, how about this modification? Please think of this as just one of several answers.

Modified script:

Please modify your script as follows. In this modification, your function of updateSourceToTarget() is not used.

From:

for (var s in allsheets) {
    var sheet = allsheets[s];
    updateSourceToTarget(sourceID, sheet.getName(), targetID, 'Combined');
}

To:

// Retrieve values from the target sheet.
var targetSheet = ss.getSheetByName(sheetExclude[0]);
var targetValues = targetSheet.getRange("B2:C" + targetSheet.getLastRow()).getValues();

// Retrieve values from all source sheets. <--- Modified
var sourceValues = allsheets.reduce(function(ar, sheet) {
  var v = sheet.getRange(2, 9, sheet.getLastRow() - 1, 10).getValues().filter(function(r) {return r[0] && r[1]});
  if (v.length > 0) {
    v = v.filter(function(e) {return !ar.some(function(f) {return e[0] === f[0]})});
    Array.prototype.push.apply(ar, v);
  }
  return ar;
}, []);

// Remove the duplication values between the target sheet and all source sheets.
var dstValues = sourceValues.filter(function(e) {return !targetValues.some(function(f) {return e[0] === f[0]})});

// Add the result values to the target sheet.
if (dstValues.length > 0) {
  var destination = SpreadsheetApp.openById(targetID).getSheetByName(sheetExclude[0]);
  destination.getRange(destination.getLastRow() + 1, 2, dstValues.length, dstValues[0].length).setValues(dstValues);
}

The flow of this modified script is as follows.

1. Retrieve values from the target sheet.
2. Retrieve values from all source sheets.
3. Remove the duplication values between the target sheet and all source sheets.
4. Add the result values to the target sheet.

Note:

• When your shared Spreadsheet is used as the target (Combined) and source sheets (Sheet2 and Sheet3), the following rows are added to the target sheet.
  • https://thehill.com/policy/national-security/department-of-homeland-security/460158-new-hampshire-border-patrol BorderSecurity
  • https://abcnews.go.com/International/climate-change-frontier-worlds-northernmost-town/story?id=65381362 ClimateChange

References:

• reduce()
• filter()
• some()

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

Added:

In this additional script, a hash table is used for this situation, as mentioned by TheMaster's comment. For example, a sample can be also seen at this thread. In your situation, at first, all values are retrieved from all sheets including Combined sheet, and the hash table is created. By this, the duplicated values are removed. Then, the converted values to an array are put to the Spreadsheet.

Sample script:

Please modify your script as follows.

From:

allsheets.splice(sheetExcludeIndex, sheetExclude.length);

for (var s in allsheets) {
    var sheet = allsheets[s];
    updateSourceToTarget(sourceID, sheet.getName(), targetID, 'Combined');
}

To:

// allsheets.splice(sheetExcludeIndex, sheetExclude.length); // In this script, this line is not used.

// Retrieve values from the target sheet.
var targetSheet = ss.getSheetByName(sheetExclude[0]);
var targetValues = targetSheet.getRange("B2:C" + targetSheet.getLastRow()).getValues();

// Retrieve values from all source sheets.
// Remove the duplication values between the target sheet and all source sheets.
var sourceValues = allsheets.reduce(function(obj, sheet) {
  var v = sheet.getRange(2, 9, sheet.getLastRow() - 1, 10).getValues().filter(function(r) {return r[0] && r[1]});
  if (v.length > 0) v.forEach(function(e) {if (!(e[0] in obj)) obj[e[0]] = e[1]});
  return obj;
}, {});
var dstValues = Object.keys(sourceValues).map(function(e) {return [e, sourceValues[e]]});

// Add the result values to the target sheet.
if (dstValues.length > 0) {
  var destination = SpreadsheetApp.openById(targetID).getSheetByName(sheetExclude[0]);
  destination.getRange(2, 2, destination.getLastRow(), 2).clearContent();
  destination.getRange(2, 2, dstValues.length, dstValues[0].length).setValues(dstValues);
}

• The proposed 2 sample scripts can be used for your situation. So please select one of them.