I have a spreadsheet where I would like to compare Column A from different sheets "RawData" and "ForTraining" to perform following tasks

Link to the spreadsheet: https://docs.google.com/spreadsheets/d/19Dqr6ZCq5dO_WO32_xcZV4E5QfJjExe8fgpvd4zj7bE/edit#gid=0

1, Add an entire row to ForTraining Sheet if there is a new entry in RawData for example in RawData, Column A has a new entry Id 11, the entire row must be copied to ForTraining Sheet.

2, After comparing if ForTraining sheet has any Ids that is not present in RawData, the entire column should be deleted from ForTraining Sheet.

Example: In the provided sheet,

In Raw Data Id 5 and 6 were removed, the same has to be removed from ForTraining Sheet.

So far I've tried the following code but this is not removing the missing items from Raw data and it is not updating the new entry correctly

  var sheet = SpreadsheetApp.getActive();
  var sourceData = sheet.getSheetByName('RawData');
  var lastRowSource = (sourceData.getRange("A2").getDataRegion().getLastRow()) - 1;
  var sourceValues = sourceData.getRange(2,1,lastRowSource,1).getValues().filter(String)
  var targetData = sheet.getSheetByName('ForTraining');
  var lastRowTarget = (targetData.getRange("A2").getDataRegion().getLastRow()) - 1;
  var targetValues = targetData.getRange(2,1,lastRowTarget,1).getValues().filter(String)
  var targetRange =  targetData.getRange(2,1,lastRowTarget,1);

  var diff = targetValues.showDif(sourceValues)
  targetRange.clearContent();
  targetValues = (diff && diff.length) ? targetValues.concat(diff) : targetValues;
  targetData.getRange(2, 1, targetValues.length, targetValues[0].length).setValues(targetValues)
 }

Array.prototype.showDif = function (array) {
    let that = this;
    return array.filter(function (r) {
        return !that.some(function (x) {
            return r.join() === x.join();
        })
    })
}

CodePudding user response：

In your situation, how about the following sample script?

Sample script:

function UpdateSheet() {
  var toObj = v => v.reduce((o, r) => (o[r[0]] = r, o), {});
  var ss = SpreadsheetApp.getActive();
  var srcSheet = ss.getSheetByName('RawData');
  var dstSheet = ss.getSheetByName('ForTraining');
  var srcValues = srcSheet.getRange(2, 1, srcSheet.getLastRow() - 1, srcSheet.getLastColumn()).getValues();
  var srcObj = toObj(srcValues);
  var dstRange = dstSheet.getRange(2, 1, dstSheet.getLastRow() - 1, dstSheet.getLastColumn());
  var dstValues = dstRange.getValues();
  var dstObj = toObj(dstValues);
  var values = [...dstValues.filter(r => srcObj[r[0]]), ...srcValues.filter(r => !dstObj[r[0]])];
  if (values.length == 0) return;
  dstRange.clearContent().offset(0, 0, values.length, values[0].length).setValues(values);
}

• In your showing script, sourceValues is one column. By this, diff is one column. From your question, when you want to use all columns by filtering 1st column when all values are used in the script, this might be a simple script. So, I proposed the above sample script.

Note:

• I tested this script using your provided sample Spreadsheet. If you change the Spreadsheet, this script might not be able to be used. Please be careful about this.

References:

• reduce()
• filter()