电子表格脚本验证:数据验证规则参数"= Employees！B1:B1000"是无效的 [英] Spreadsheet Script Validation: The data validation rule argument "=Employees!B1:B1000" is invalid

问题描述

我正在尝试使用具有以下代码的另一个电子表格对员工进行验证:

I'm trying to validate employees based on another spreadsheet with the following code:

function validation() {
  var globals = SpreadsheetApp.openByUrl('https://docs.google.com/myurl');
  var globalsheet = globals.getSheetByName('Employees');
  var validate = SpreadsheetApp.newDataValidation();
  var cell = SpreadsheetApp.getActive().getRange('A1:A');
  var range = globalsheet.getRange('B1:B');
  var rule = SpreadsheetApp.newDataValidation().requireValueInRange(range).build()
  cell.setDataValidation(rule);
}

我收到的错误消息是数据验证规则参数"= Employees！B1:B1000"无效.知道问题可能在哪里吗?预先感谢您的帮助.

The error message I receive is The data validation rule argument "=Employees!B1:B1000" is invalid. Any idea where the issue might be? Thanks in advance for the help.

推荐答案

应用脚本和Google表格不允许您使用其他电子表格中的数据来定义数据验证.您收到的错误是由Apps Script在当前电子表格中查找"Employees"表而没有找到的结果.

Apps Script and Google Sheets don't allow you to use data from other spreadsheets to define data validations. The error you are getting is the result of Apps Script looking in the current spreadsheet for the 'Employees' sheet and not finding it.

您可以让Apps脚本功能将该数据复制到当前电子表格中(而不是直接在其他电子表格中使用该数据)(如果要隐藏它，可以在另一个工作表或隐藏的列中)，然后进行设置本地副本中的数据验证.

Instead of attempting to use the data in the other spreadsheet directly, you can have an Apps Script function copy that data to the current spreadsheet (perhaps in another sheet or hidden column if you want to hide it), and then set up the data validation from the local copy.

如前所述，范围符号格式"A1:A"有效，可以在此处使用而不会出现问题.

As noted, the range notation format 'A1:A' is valid and can be used here without issue.

这篇关于电子表格脚本验证:数据验证规则参数"= Employees！B1:B1000"是无效的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！