在Google Apps脚本中创建动态下拉列表 [英] Create dynamic dropdown list in the Google apps script
问题描述
我想使用Google Apps脚本在Google电子表格的单元格验证功能中动态更改候选列表的值.我需要从其他电子表格中获取值.
I would like to dynamically change the value of the candidate list in the cell validation feature of a Google Spreadsheet using the Google Apps Script. I need to take values from other spreadsheet.
推荐答案
如果我对您的理解正确的话,这就是我要解决的问题.通常,我使用importRange来本地化"远程列表,但这在新的Google Spreadsheet中尚不起作用...因此,您可以尝试以下更直接的方法:
This is something I've grappled with, if I understand you correctly. Normally I use importRange to "localise" the remote list but this is not yet working in the NEW Google Spreadsheet ... so you might try a more direct approach like the following:
function setDropdown(){
var sourceSS = SpreadsheetApp.openById('yourSpreadsheetID'); // replace yourSpreadsheetID with the value in the sheet URL
var ss = SpreadsheetApp.getActive();
// get the range where the dynamic dropdown list is kept in the source spreadsheet
var dynamicList = sourceSS.getSheetByName('Dropdown List Sheet').getRange('A2:A'); // set to your sheet and range
// define the dropdown/validation rules
var rangeRule = SpreadsheetApp.newDataValidation().requireValueInRange(dynamicList).build();
// set the dropdown validation for the row
ss.getRange('sheet 1!A5:A10').setDataValidation(rangeRule); // set range to your range
}
我怀疑以上内容在新的Google Spreadsheet中仍不起作用.以下修改改为将范围转换为值列表.在短名单上对此进行了测试,并且可以完成工作...
I suspect that the above does not work yet in new Google Spreadsheet. The following modification converts the range to a list of values instead. This was tested on a short list, and does the job...
function setDropdown(){
var sourceSS = SpreadsheetApp.openById('yourSpreadsheetID'); // replace yourSpreadsheetID with the value in the sheet URL
var ss = SpreadsheetApp.getActive();
// get the range where the dynamic dropdown list is kept in the source spreadsheet
var dynamicList = sourceSS.getSheetByName('Dropdown List Sheet').getRange('A2:A10'); // set to your sheet and range
var arrayValues = dynamicList.getValues();
// define the dropdown/validation rules
var rangeRule = SpreadsheetApp.newDataValidation().requireValueInList(arrayValues);
// set the dropdown validation for the row
ss.getRange('sheet 1!A5:A10').setDataValidation(rangeRule); // set range to your range
}
这篇关于在Google Apps脚本中创建动态下拉列表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!