google-apps-script - 来自不同电子表格的数据验证范围与 onEdit 触发器

Question

我的源电子表格中有一个下拉列表，其中包含来自同一电子表格中另一个选项卡的范围的数据验证，其中数据从另一个电子表格中导入，并使用以下脚本。

主电子表格非常慢，因为我有很多带有脚本导入数据的选项卡，那么如何在我的源电子表格上创建一个 onEdit 触发器，它会在编辑另一个电子表格时更新数据验证？

剧本：

function importSheetA() {
var values1 = SpreadsheetApp.openById('xxxxx').
  getSheetByName('xxxxx').getRange('A1:EO2000').getValues(); 
SpreadsheetApp.getActive().getSheetByName('masterop').
  getRange(1,1,values1.length,values1[0].length).setValues(values1);  
}

更新：

使用此脚本，我可以在 1 张纸（“id1”）上显示下拉列表。如何为许多电子表格 ID 执行此操作？

function installTrigger() {
  ScriptApp.newTrigger('changeValidationRule').forSpreadsheet(SpreadsheetApp.getActive()).onEdit().create();
}

function changeValidationRule(e) {
  let values = e.source.getRange("A1:A3").getValues().flatMap(value => value); //flatting the row structures into a single dimension array
  let sourceSheet= SpreadsheetApp.openById("id1").getSheets()[0];
  let rule = SpreadsheetApp.newDataValidation().requireValueInList(values, true); // The boolean stands for `show dropdown`
  sourceSheet.getRange(1, 1).setDataValidation(rule);
}

标签： google-apps-scriptgoogle-sheetsimportrange

Answer 1

方法

onEdit()您可以使用 Apps Script可安装触发器来实现此行为。

必须安装触发器，因为它需要授权才能访问源电子表格。

触发器将放置在验证表中，并在手动更新时触发。触发器将运行一个函数，该函数将根据您要考虑的值更新验证规则。

为简单起见，我假设这些值位于验证表的列中 range A1:A3。

我将获取这些值，并在源电子表格的所需范围上构建验证规则。

代码

function installTrigger() {
  ScriptApp.newTrigger('changeValidationRule').forSpreadsheet(SpreadsheetApp.getActive()).onEdit().create();
}

function changeValidationRule(e) {
  let values = e.source.getRange("A1:A3").getValues().flatMap(value => value); //flatting the row structures into a single dimension array
  // Initialize the variables
  let ids = ['id1','id2','id3']; // Add here the ids of your source sheets
  let rule = SpreadsheetApp.newDataValidation().requireValueInList(values, true); // The boolean stands for `show dropdown`
  // Loop through the ids and update the rule
  ids.map(id => {
      let sourceSheet= SpreadsheetApp.openById(id).getSheets()[0];
      sourceSheet.getRange(1, 1).setDataValidation(rule);
  });
  
}

免责声明

在您的示例中，您将这些值导入到源电子表格中。您实际上可以在源电子表格中引用范围，但我认为使用值列表更容易，因为我们直接在验证电子表格上工作。我将在下面引用此方法。

编辑

您可以在验证规则中使用的值项存在限制。此限制为 500 项。如果您需要验证超过 500 个项目，除了在源电子表格的验证表中导入所有值之外别无他法。您将能够在您的数据验证规则中引用此范围，并且您可以构建一个脚本，以便在验证电子表格更改时更新此范围。这是一个想法：

function installTrigger() {
  ScriptApp.newTrigger('updateValidationRange').forSpreadsheet(SpreadsheetApp.getActive()).onEdit().create();
}

function updateValidationRange(e) {
  let values = e.source.getRange("a-big-range-in-validation-spreadsheet").getValues();
  // Initialize the variables
  let ids = ['id1','id2','id3'];
  // Loop through the ids and update the rule
  ids.map(id => {
      let sourceSheet= SpreadsheetApp.openById(id).getSheetByName("Validation Sheet"); // Be sure to change this name in order to reflect your validation sheet inside the source Spreadsheet
      sourceSheet.getRange("the-copy-of-the-big-range-in-the-source-spreadsheet").setValues(values); // It's crucial this range is the same size than the one in the validation spreadsheet
  });
  
}

参考

要求值列表

要求值范围内

可安装触发器