如何以相对方式复制数据验证，特别是从范围(带有或不带有脚本)的Google表格中列出数据 [英] How to copy data validation in a relative way, specifically list from range (with or without scripts) Google Sheets
问题描述
我创建了一个电子表格来跟踪约会.在此电子表格中，我具有动态相关的下拉列表，因此列表1中的选择将填充列表2中的选项.这如下:
单元格C2:客户端-客户端1或客户端2.(用于示例，实际列表将扩展.)
如果单元格C2 =客户端1，则地址下拉列表如下:
单元格C3:地址-地址1或地址2.
如果单元格C2 =客户端2，则地址下拉列表如下:
单元格C3:地址-地址3或地址4.
客户端列表的数据验证很简单，因为它是恒定的，可以在列之间进行复制.但是，不能从多个地址复制对从属地址列表的数据验证，因为它始终引用C列，并且与相对客户列表失去连接.
单元格C3(地址列表)的实际数据验证如下:
单元格范围='DIARY(V-2.1)'！C3
条件='日记(V-2.1)'！C53:C55
单元格C53:C55由一个过滤器公式填充，该公式将根据单元格C2中客户端的选择对相关地址进行排序，从而依次填充C3中的地址列表.
可能值得注意的是，我每列有10组这些约会空位(每天10个)，因此我对每个单独的约会都使用了过滤功能，因此它们可以独立工作，但是即使这样也很乏味，所以我需要是为了能够从C列开始复制该列(或仅进行数据验证)，并使它相对于该列中的过滤器功能保持不变.
请参见下面的示例电子表格链接，这一切都说得通！在示例中，我已将C列复制到D列，并且地址列表的数据验证已针对所有十个约会空位进行了分离. (所有编辑都是免费的.)
注意:这些单元格必须是下拉列表-范围中的列表-以使用此特定电子表格的功能.
如果有人可以提供帮助，我将不胜感激，因为我一直在勤勉地搜索论坛，却找不到解决方案.我确实看过了这段视频(这真是令人头晕)，似乎可以使用Java脚本来实现:
https://www.youtube.com/watch?v = ZiYnuZ8MwgM& feature = youtu.be
Google表格目前没有用于相对地复制/填充数据验证参考或公式的内置解决方案.但是有人已经在 Google文档论坛帖子中写了一个不错的脚本.为了避免仅将链接作为答案，我将在此处复制脚本和说明.记入 Google .
如何使用他们的脚本: 已包含脚本的原始解决方案示例Google表格链接-您可以保存自己的副本，然后开始使用. 或者从头开始重新创建，这是脚本. I have created a spread sheet to track appointments. Within this spread sheet I have dynamic dependent drop down lists, so the choice from list 1 will populate the options in list 2. This is as follows: Cell C2: Client - Client 1 or Client 2. (this is for the use of the example, the actual lists will expand.) If cell C2 = Client 1 then the address drop down is as below: Cell C3: Address - Address 1 or Address 2. If cell C2 = Client 2 then the address drop down is as below: Cell C3: Address - Address 3 or Address 4. The data validation for the client list is simple because this is constant and can be copied from column to column. The data validation for the dependent address list, however, can not be copied across multiple columns as it will always refer to column C and lose connection with the relative client list. The actual data validation for the cell C3 (Address List) is below: Cell Range = 'DIARY (V-2.1)'!C3 Criteria = 'DIARY (V-2.1)'!C53:C55 The cells C53:C55 are populated by a filter formula which will sort the relevant addresses dependent on the choice of client in cell C2, this in turn populates the address list in C3. It may be worth noting that I have 10 sets of these appointment slots per column (10 per day) so I have used a filter function for each of these separate appointments so they work independently, but even this is tedious so my need is to be able to copy the column (or just the data validation) from column C onward and have it stay relative to the filter functions in that column. Please see the below link to the example spreadsheet and this will all make sense! In the example I have copied column C to column D and the data validation for the address list has detached for all ten appointment slots. (permission is free for all to edit.) Note: These cells must be drop down lists - list from range - for the functionality of this specific spreadsheet. If anyone can help with this it would be MASSIVELY appreciated as I have diligently scoured the forums and cannot find a solution. I did see this video (which is way over my head) that seems to accomplish this using java script: https://www.youtube.com/watch?v=ZiYnuZ8MwgM&feature=youtu.be Google Sheets does not currently have a built-in solution for copying/filling data validation references or formulas relatively. But somebody already wrote a nice script in this Google Docs forum post. To avoid just a link as an answer, I'm going to copy in the script and instructions here. Credit to AD:AM from Google Docs forum. How to use their script: Link to original solution's example Google Sheets with script already included - you can save your own copy and then start using. Or to recreate from scratch, here is the script.
这篇关于如何以相对方式复制数据验证，特别是从范围(带有或不带有脚本)的Google表格中列出数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
function onOpen()
{
SpreadsheetApp.getActiveSpreadsheet().addMenu
(
"Validation+",
[
{name: "Copy validation (all relative references)", functionName: "copyValidation"},
{name: "Copy validation (relative rows, absolute columns)", functionName: "copyValidationColumnsAbsolute"},
{name: "Copy validation (absolute rows, relative columns)", functionName: "copyValidationRowsAbsolute"}
]
);
}
function copyValidation(rowsAbsolute, columnsAbsolute)
{
var ss = SpreadsheetApp.getActiveSpreadsheet();
var r = ss.getActiveRange();
var dv = r.getDataValidations();
var dvt = dv[0][0].getCriteriaType();
if (dvt != SpreadsheetApp.DataValidationCriteria.VALUE_IN_RANGE) return;
var dvv = dv[0][0].getCriteriaValues();
Logger.log(dvv);
for (var i = 0; i < dv.length; i++)
{
for (var j = i ? 0 : 1; j < dv[0].length; j++)
{
dv[i][j] = dv[0][0].copy().withCriteria(dvt, [dvv[0].offset(rowsAbsolute ? 0 : i, columnsAbsolute ? 0 : j), dvv[1]]).build();
}
}
r.setDataValidations(dv);
}
function copyValidationRowsAbsolute()
{
copyValidation(true, false);
}
function copyValidationColumnsAbsolute()
{
copyValidation(false, true);
}
function onOpen()
{
SpreadsheetApp.getActiveSpreadsheet().addMenu
(
"Validation+",
[
{name: "Copy validation (all relative references)", functionName: "copyValidation"},
{name: "Copy validation (relative rows, absolute columns)", functionName: "copyValidationColumnsAbsolute"},
{name: "Copy validation (absolute rows, relative columns)", functionName: "copyValidationRowsAbsolute"}
]
);
}
function copyValidation(rowsAbsolute, columnsAbsolute)
{
var ss = SpreadsheetApp.getActiveSpreadsheet();
var r = ss.getActiveRange();
var dv = r.getDataValidations();
var dvt = dv[0][0].getCriteriaType();
if (dvt != SpreadsheetApp.DataValidationCriteria.VALUE_IN_RANGE) return;
var dvv = dv[0][0].getCriteriaValues();
Logger.log(dvv);
for (var i = 0; i < dv.length; i++)
{
for (var j = i ? 0 : 1; j < dv[0].length; j++)
{
dv[i][j] = dv[0][0].copy().withCriteria(dvt, [dvv[0].offset(rowsAbsolute ? 0 : i, columnsAbsolute ? 0 : j), dvv[1]]).build();
}
}
r.setDataValidations(dv);
}
function copyValidationRowsAbsolute()
{
copyValidation(true, false);
}
function copyValidationColumnsAbsolute()
{
copyValidation(false, true);
}
