google-apps-script - Google 表格 - 在多张表格上更新相同的目标范围
问题描述
我想要以下代码来更新几个 targetSheets 上的相同 targetRange:“Trip 1”、“Trip 2”、“Trip 3”、“Trip 4”、“Trip 5”等。
但我不知道如何实现这一点。我将非常感谢一些帮助。
function updateSheet() {
let ss = SpreadsheetApp.getActive()
let sourceValues = ss.getSheetByName("Master").getRange("A2:A").getValues().filter(String)
let targetSheet = ss.getSheetByName("Trip 1")
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)
}
Array.prototype.showDif = function (array) {
let that = this;
return array.filter(function (r) {
return !that.some(function (x) {
return r.join() === x.join();
})
})
}
解决方案
在您的情况下,如何修改您的脚本如下?
修改脚本1:
在此脚本中,声明了要使用的工作表名称。
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",,, ];
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)
});
}
修改脚本2:
在此脚本中,除“主”工作表之外的工作表用作targetSheet
.
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);
ss.getSheets().filter(e => e.getSheetName() != "Master").forEach(targetSheet => {
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)
});
}