谷歌表格 - 从"主"到"行程1"，"行程2"等的双向同步

Question

@Tanaike非常友好地提供了这个修改后的代码，它可以完美地工作，所以当我在范围内添加一个唯一的数字"A2: a "关于"大师"号;让它更新范围';A4: a ';在所有其他的页上写着"旅行1"、"旅行2"等等

我还希望发生相反的情况，以便当我删除"A2:A"中的一个唯一数字时;关于"大师"号;表格，然后它会删除相同的唯一数字在"A4: a";在每张纸上写着"旅行1"、"旅行2"等。

如果有人能提供一些帮助，我将非常感激。

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)
});
}

Answer 1

您可以在任何更改后复制您的列'A':

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);
}
}

为了以防万一，下面是更新其他工作表上的列(添加新值，并删除已删除的值)并保持目标列中元素顺序的函数:

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))
}

列'A'中的值必须唯一。