@Tanaike very kindly provided this modified code which works perfectly, so that when I add a unique number in the range "A2:A" on the "Master" sheet it update the range "A4:A" on all the other sheets "Trip 1", "Trip 2", etc.

I would also like the reverse to happen, so that when I delete one of the unique numbers in "A2:A" on the "Master" sheet then it will delete the same unique number in "A4:A" on each of the sheets "Trip 1", "Trip 2", etc.

If someone could provide some help that would be so very much appreciated.

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

  let ss = SpreadsheetApp.getActive()
  let sourceValues = ss.getSheetByName("Master").getRange("A2:A").getValues().filter(String);
  const sheetNames = [ "Trip 1", "Trip 2", "Trip 3", "Trip 4", "Trip 5", "Trip 6", "Trip 7", "Trip 8", "Trip 9", "Trip 10"];
  sheetNames.forEach(name => {
    let targetSheet = ss.getSheetByName(name);
    if (!targetSheet) return;
    let targetRange = targetSheet.getRange("A4:A");
    let targetValues = targetRange.getValues().filter(String)
    let diff = targetValues.showDif(sourceValues)
    targetRange.clearContent();
    targetValues = (diff && diff.length) ? targetValues.concat(diff) : targetValues;
    targetSheet.getRange(4, 1, targetValues.length, targetValues[0].length).setValues(targetValues)
  });
}

CodePudding user response：

You can just copy your column 'A' after any changes:

function copy_data() {
  var ss    = SpreadsheetApp.getActiveSpreadsheet();
  var sh    = ss.getSheets()[0];
  var data  = sh.getRange('A2:A').getValues();
  var names = ["Trip 1", "Trip 2", "Trip 3"];

  for (var name of names) {
    var sheet = ss.getSheetByName(name);
    if (!sheet) continue;
    sheet.getRange('A4:A').clearContent();
    sheet.getRange(4, 1, data.length).setValues(data);
  }
}

Just in case, here is the function that updated columns (adds new values, and removes removed ones) on other sheets and keeps the order of elements in the destination columns:

function update_column_keep_order() {
  var ss       = SpreadsheetApp.getActiveSpreadsheet();
  var sh       = ss.getSheets()[0];
  var src_data = sh.getRange(`A2:A${sh.getLastRow()}`).getValues().flat();
  var names    = ["Trip 1", "Trip 2", "Trip 3"];

  for (var name of names) {
    var sheet = ss.getSheetByName(name);
    if (!sheet) continue;

    var dest_data = sheet.getRange(`A4:A${sheet.getLastRow()}`).getValues().flat();
    var map = new Map(dest_data.map(x => ['' x,x]));

    // add all values from source range to destination range
    for (var key of src_data) if (key !='') map.set('' key, key); 

    // clear the values in destination range that absent in source range
    var diffs = src_data.dif(dest_data);
    for (var dif of diffs) map.set(''   dif, '');
    var updated_dest_data = Array.from(map.values()).map(x => [x]);

    // put the values in destinaion range on the sheet
    sheet.getRange('A4:A').clearContent();
    sheet.getRange(4, 1, updated_dest_data.length).setValues(updated_dest_data);
  }
}

// based on Tanaike's solution
Array.prototype.dif = function (array) {
  return array.filter(r => !this.some(x => r === x))
}

Values in column 'A' must be unique.