首页 > 解决方案 > 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();
        })
    })
}

标签: google-apps-scriptgoogle-sheets

解决方案


在您的情况下,如何修改您的脚本如下?

修改脚本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)
  });
}

参考:


推荐阅读