问题：
如何在谷歌表单中实现动态/相关下拉列表？
卢聪
如何让子类别列根据在google sheets的主类别下拉列表中选择的值填充下拉列表？
我谷歌了一下，找不到任何好的解决方案，所以我想分享我自己的。请看下面我的回答。
共有3个答案
易俊驰
这里有另一个基于@tarheel提供的解决方案
function onEdit() {
var sheetWithNestedSelectsName = "Sitemap";
var columnWithNestedSelectsRoot = 1;
var sheetWithOptionPossibleValuesSuffix = "TabSections";
var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var activeSheet = SpreadsheetApp.getActiveSheet();
// If we're not in the sheet with nested selects, exit!
if ( activeSheet.getName() != sheetWithNestedSelectsName ) {
return;
}
var activeCell = SpreadsheetApp.getActiveRange();
// If we're not in the root column or a content row, exit!
if ( activeCell.getColumn() != columnWithNestedSelectsRoot || activeCell.getRow() < 2 ) {
return;
}
var sheetWithActiveOptionPossibleValues = activeSpreadsheet.getSheetByName( activeCell.getValue() + sheetWithOptionPossibleValuesSuffix );
// Get all possible values
var activeOptionPossibleValues = sheetWithActiveOptionPossibleValues.getSheetValues( 1, 1, -1, 1 );
var possibleValuesValidation = SpreadsheetApp.newDataValidation();
possibleValuesValidation.setAllowInvalid( false );
possibleValuesValidation.requireValueInList( activeOptionPossibleValues, true );
activeSheet.getRange( activeCell.getRow(), activeCell.getColumn() + 1 ).setDataValidation( possibleValuesValidation.build() );
}
与其他方法相比，它有一些好处：
- 您不需要每次添加根选项时都编辑脚本。您只需使用此根选项的嵌套选项创建一个新工作表。
- 我重构了脚本，为变量提供了更多的语义名称等等。此外，我还提取了一些变量参数，以便更容易适应您的特定情况。您只需设置前3个值。
- 嵌套选项值没有限制（我使用了getSheetValue方法和-1值）。
那么，如何使用它：
- 创建包含嵌套选择器的工作表
享受
尉迟冯浩
小心脚本有一个限制：它在一个下拉列表中最多处理500个值。
更多信息
- 文章
- 视频
- GitHub上脚本的最新版本
此解决方案并不完美，但它提供了一些好处：
- 允许您创建多个下拉列表
首先，这里是一个工作示例，所以您可以在继续之前对其进行测试。
安装：
- 准备数据
- 像往常一样做第一个列表：
Data
准备数据
数据看起来像一个包含所有可能变量的表。它必须位于单独的工作表上，以便脚本可以使用它。看看这个例子：
这里我们有四个级别，每个值重复。请注意，数据右侧的2列是保留的，所以不要在那里键入/粘贴任何数据。
第一个简单的数据验证（DV）
准备一个独特值的列表。在我们的例子中，它是一个行星列表。在带有数据的工作表上找到空闲空间，并粘贴公式：=独特（A： A）在您的主工作表上选择第一列，DV将从那里开始。转到数据
剧本
将此代码粘贴到脚本编辑器中：
js lang-js prettyprint-override">function onEdit(event)
{
// Change Settings:
//--------------------------------------------------------------------------------------
var TargetSheet = 'Main'; // name of sheet with data validation
var LogSheet = 'Data1'; // name of sheet with data
var NumOfLevels = 4; // number of levels of data validation
var lcol = 2; // number of column where validation starts; A = 1, B = 2, etc.
var lrow = 2; // number of row where validation starts
var offsets = [1,1,1,2]; // offsets for levels
// ^ means offset column #4 on one position right.
// =====================================================================================
SmartDataValidation(event, TargetSheet, LogSheet, NumOfLevels, lcol, lrow, offsets);
// Change Settings:
//--------------------------------------------------------------------------------------
var TargetSheet = 'Main'; // name of sheet with data validation
var LogSheet = 'Data2'; // name of sheet with data
var NumOfLevels = 7; // number of levels of data validation
var lcol = 9; // number of column where validation starts; A = 1, B = 2, etc.
var lrow = 2; // number of row where validation starts
var offsets = [1,1,1,1,1,1,1]; // offsets for levels
// =====================================================================================
SmartDataValidation(event, TargetSheet, LogSheet, NumOfLevels, lcol, lrow, offsets);
}
function SmartDataValidation(event, TargetSheet, LogSheet, NumOfLevels, lcol, lrow, offsets)
{
//--------------------------------------------------------------------------------------
// The event handler, adds data validation for the input parameters
//--------------------------------------------------------------------------------------
var FormulaSplitter = ';'; // depends on regional setting, ';' or ',' works for US
//--------------------------------------------------------------------------------------
// =================================== key variables =================================
//
// ss sheet we change (TargetSheet)
// br range to change
// scol number of column to edit
// srow number of row to edit
// CurrentLevel level of drop-down, which we change
// HeadLevel main level
// r current cell, which was changed by user
// X number of levels could be checked on the right
//
// ls Data sheet (LogSheet)
//
// ======================================================================================
// Checks
var ts = event.source.getActiveSheet();
var sname = ts.getName();
if (sname !== TargetSheet) { return -1; } // not main sheet
// Test if range fits
var br = event.range;
var scol = br.getColumn(); // the column number in which the change is made
var srow = br.getRow() // line number in which the change is made
var ColNum = br.getWidth();
if ((scol + ColNum - 1) < lcol) { return -2; } // columns...
if (srow < lrow) { return -3; } // rows
// Test range is in levels
var columnsLevels = getColumnsOffset_(offsets, lcol); // Columns for all levels
var CurrentLevel = getCurrentLevel_(ColNum, br, scol, columnsLevels);
if(CurrentLevel === 1) { return -4; } // out of data validations
if(CurrentLevel > NumOfLevels) { return -5; } // last level
/*
ts - sheet with validation, sname = name of sheet
NumOfLevels = 4
offsets = [1,1,1,2] - last offset is 2 because need to skip 1 column
columnsLevels = [4,5,6,8] - Columns of validation
Columns 7 is skipped
|
1 2 3 4 5 6 7 8 9
|----+----+----+----+----+----+----+----+----+
1 | | | | | | | x | | |
|----+----+----+----+----+----+----+----+----+
2 | | | | v | V | ? | x | ? | | lrow = 2 - number of row where validation starts
|----+----+----+----+----+----+----+----+----+
3 | | | | | | | x | | |
|----+----+----+----+----+----+----+----+----+
4 | | | | | | | x | | |
|----+----+----+----+----+----+----+----+----+
| | | | |
| | | | Currentlevel = 3 - the number of level to change
| | | |
| | | br - cell, user changes: scol - column, srow - row,
| | ColNum = 1 - width
|__|________ _.....____|
| v
| Drop-down lists
|
| lcol = 4 - number of column where validation starts
*/
// Constants
var ReplaceCommas = getDecimalMarkIsCommaLocals(); // // ReplaceCommas = true if locale uses commas to separate decimals
var ls = SpreadsheetApp.getActive().getSheetByName(LogSheet); // Data sheet
var RowNum = br.getHeight();
/* Adjust the range 'br'
??? !
xxx x
xxx x
xxx => x
xxx x
xxx x
*/
br = ts.getRange(br.getRow(), columnsLevels[CurrentLevel - 2], RowNum);
// Levels
var HeadLevel = CurrentLevel - 1; // main level
var X = NumOfLevels - CurrentLevel + 1; // number of levels left
// determine columns on the sheet "Data"
var KudaCol = NumOfLevels + 2;
var KudaNado = ls.getRange(1, KudaCol); // 1 place for a formula
var lastRow = ls.getLastRow();
var ChtoNado = ls.getRange(1, KudaCol, lastRow, KudaCol); // the range with list, returned by a formula
// ============================================================================= > loop >
var CurrLevelBase = CurrentLevel; // remember the first current level
for (var j = 1; j <= RowNum; j++) // [01] loop rows start
{
// refresh first val
var currentRow = br.getCell(j, 1).getRow();
loopColumns_(HeadLevel, X, currentRow, NumOfLevels, CurrLevelBase, lastRow, FormulaSplitter, CurrLevelBase, columnsLevels, br, KudaNado, ChtoNado, ReplaceCommas, ts);
} // [01] loop rows end
}
function getColumnsOffset_(offsets, lefColumn)
{
// Columns for all levels
var columnsLevels = [];
var totalOffset = 0;
for (var i = 0, l = offsets.length; i < l; i++)
{
totalOffset += offsets[i];
columnsLevels.push(totalOffset + lefColumn - 1);
}
return columnsLevels;
}
function test_getCurrentLevel()
{
var br = SpreadsheetApp.getActive().getActiveSheet().getRange('A5:C5');
var scol = 1;
/*
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |
range |xxxxx|
dv range |xxxxxxxxxxxxxxxxx|
levels 1 2 3
level 2
*/
Logger.log(getCurrentLevel_(1, br, scol, [1,2,3])); // 2
/*
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |
range |xxxxxxxxxxx|
dv range |xxxxx| |xxxxx| |xxxxx|
levels 1 2 3
level 2
*/
Logger.log(getCurrentLevel_(2, br, scol, [1,3,5])); // 2
/*
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |
range |xxxxxxxxxxxxxxxxx|
dv range |xxxxx| |xxxxxxxxxxx|
levels 1 2 3
level 2
*/
Logger.log(getCurrentLevel_(3, br, scol, [1,5,6])); // 2
/*
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |
range |xxxxxxxxxxxxxxxxx|
dv range |xxxxxxxxxxx| |xxxxx|
levels 1 2 3
level 3
*/
Logger.log(getCurrentLevel_(3, br, scol, [1,2,8])); // 3
/*
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |
range |xxxxxxxxxxxxxxxxx|
dv range |xxxxxxxxxxxxxxxxx|
levels 1 2 3
level 4 (error)
*/
Logger.log(getCurrentLevel_(3, br, scol, [1,2,3]));
/*
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |
range |xxxxxxxxxxxxxxxxx|
dv range |xxxxxxxxxxxxxxxxx|
levels
level 1 (error)
*/
Logger.log(getCurrentLevel_(3, br, scol, [5,6,7])); // 1
}
function getCurrentLevel_(ColNum, br, scol, columnsLevels)
{
var colPlus = 2; // const
if (ColNum === 1) { return columnsLevels.indexOf(scol) + colPlus; }
var CurrentLevel = -1;
var level = 0;
var column = 0;
for (var i = 0; i < ColNum; i++ )
{
column = br.offset(0, i).getColumn();
level = columnsLevels.indexOf(column) + colPlus;
if (level > CurrentLevel) { CurrentLevel = level; }
}
return CurrentLevel;
}
function loopColumns_(HeadLevel, X, currentRow, NumOfLevels, CurrentLevel, lastRow, FormulaSplitter, CurrLevelBase, columnsLevels, br, KudaNado, ChtoNado, ReplaceCommas, ts)
{
for (var k = 1; k <= X; k++)
{
HeadLevel = HeadLevel + k - 1;
CurrentLevel = CurrLevelBase + k - 1;
var r = ts.getRange(currentRow, columnsLevels[CurrentLevel - 2]);
var SearchText = r.getValue(); // searched text
X = loopColumn_(X, SearchText, HeadLevel, HeadLevel, currentRow, NumOfLevels, CurrentLevel, lastRow, FormulaSplitter, CurrLevelBase, columnsLevels, br, KudaNado, ChtoNado, ReplaceCommas, ts);
}
}
function loopColumn_(X, SearchText, HeadLevel, HeadLevel, currentRow, NumOfLevels, CurrentLevel, lastRow, FormulaSplitter, CurrLevelBase, columnsLevels, br, KudaNado, ChtoNado, ReplaceCommas, ts)
{
// if nothing is chosen!
if (SearchText === '') // condition value =''
{
// kill extra data validation if there were
// columns on the right
if (CurrentLevel <= NumOfLevels)
{
for (var f = 0; f < X; f++)
{
var cell = ts.getRange(currentRow, columnsLevels[CurrentLevel + f - 1]);
// clean & get rid of validation
cell.clear({contentsOnly: true});
cell.clear({validationsOnly: true});
// exit columns loop
}
}
return 0; // end loop this row
}
// formula for values
var formula = getDVListFormula_(CurrentLevel, currentRow, columnsLevels, lastRow, ReplaceCommas, FormulaSplitter, ts);
KudaNado.setFormula(formula);
// get response
var Response = getResponse_(ChtoNado, lastRow, ReplaceCommas);
var Variants = Response.length;
// build data validation rule
if (Variants === 0.0) // empty is found
{
return;
}
if(Variants >= 1.0) // if some variants were found
{
var cell = ts.getRange(currentRow, columnsLevels[CurrentLevel - 1]);
var rule = SpreadsheetApp
.newDataValidation()
.requireValueInList(Response, true)
.setAllowInvalid(false)
.build();
// set validation rule
cell.setDataValidation(rule);
}
if (Variants === 1.0) // // set the only value
{
cell.setValue(Response[0]);
SearchText = null;
Response = null;
return X; // continue doing DV
} // the only value
return 0; // end DV in this row
}
function getDVListFormula_(CurrentLevel, currentRow, columnsLevels, lastRow, ReplaceCommas, FormulaSplitter, ts)
{
var checkVals = [];
var Offs = CurrentLevel - 2;
var values = [];
// get values and display values for a formula
for (var s = 0; s <= Offs; s++)
{
var checkR = ts.getRange(currentRow, columnsLevels[s]);
values.push(checkR.getValue());
}
var LookCol = colName(CurrentLevel-1); // gets column name "A,B,C..."
var formula = '=unique(filter(' + LookCol + '2:' + LookCol + lastRow; // =unique(filter(A2:A84
var mathOpPlusVal = '';
var value = '';
// loop levels for multiple conditions
for (var i = 0; i < CurrentLevel - 1; i++) {
formula += FormulaSplitter; // =unique(filter(A2:A84;
LookCol = colName(i);
value = values[i];
mathOpPlusVal = getValueAndMathOpForFunction_(value, FormulaSplitter, ReplaceCommas); // =unique(filter(A2:A84;B2:B84="Text"
if ( Array.isArray(mathOpPlusVal) )
{
formula += mathOpPlusVal[0];
formula += LookCol + '2:' + LookCol + lastRow; // =unique(filter(A2:A84;ROUND(B2:B84
formula += mathOpPlusVal[1];
}
else
{
formula += LookCol + '2:' + LookCol + lastRow; // =unique(filter(A2:A84;B2:B84
formula += mathOpPlusVal;
}
}
formula += "))"; //=unique(filter(A2:A84;B2:B84="Text"))
return formula;
}
function getValueAndMathOpForFunction_(value, FormulaSplitter, ReplaceCommas)
{
var result = '';
var splinter = '';
var type = typeof value;
// strings
if (type === 'string') return '="' + value + '"';
// date
if(value instanceof Date)
{
return ['ROUND(', FormulaSplitter +'5)=ROUND(DATE(' + value.getFullYear() + FormulaSplitter + (value.getMonth() + 1) + FormulaSplitter + value.getDate() + ')' + '+'
+ 'TIME(' + value.getHours() + FormulaSplitter + value.getMinutes() + FormulaSplitter + value.getSeconds() + ')' + FormulaSplitter + '5)'];
}
// numbers
if (type === 'number')
{
if (ReplaceCommas)
{
return '+0=' + value.toString().replace('.', ',');
}
else
{
return '+0=' + value;
}
}
// booleans
if (type === 'boolean')
{
return '=' + value;
}
// other
return '=' + value;
}
function getResponse_(allRange, l, ReplaceCommas)
{
var data = allRange.getValues();
var data_ = allRange.getDisplayValues();
var response = [];
var val = '';
for (var i = 0; i < l; i++)
{
val = data[i][0];
if (val !== '')
{
var type = typeof val;
if (type === 'boolean' || val instanceof Date) val = String(data_[i][0]);
if (type === 'number' && ReplaceCommas) val = val.toString().replace('.', ',')
response.push(val);
}
}
return response;
}
function colName(n) {
var ordA = 'a'.charCodeAt(0);
var ordZ = 'z'.charCodeAt(0);
var len = ordZ - ordA + 1;
var s = "";
while(n >= 0) {
s = String.fromCharCode(n % len + ordA) + s;
n = Math.floor(n / len) - 1;
}
return s;
}
function getDecimalMarkIsCommaLocals() {
// list of Locals Decimal mark = comma
var LANGUAGE_BY_LOCALE = {
af_NA: "Afrikaans (Namibia)",
af_ZA: "Afrikaans (South Africa)",
af: "Afrikaans",
sq_AL: "Albanian (Albania)",
sq: "Albanian",
ar_DZ: "Arabic (Algeria)",
ar_BH: "Arabic (Bahrain)",
ar_EG: "Arabic (Egypt)",
ar_IQ: "Arabic (Iraq)",
ar_JO: "Arabic (Jordan)",
ar_KW: "Arabic (Kuwait)",
ar_LB: "Arabic (Lebanon)",
ar_LY: "Arabic (Libya)",
ar_MA: "Arabic (Morocco)",
ar_OM: "Arabic (Oman)",
ar_QA: "Arabic (Qatar)",
ar_SA: "Arabic (Saudi Arabia)",
ar_SD: "Arabic (Sudan)",
ar_SY: "Arabic (Syria)",
ar_TN: "Arabic (Tunisia)",
ar_AE: "Arabic (United Arab Emirates)",
ar_YE: "Arabic (Yemen)",
ar: "Arabic",
hy_AM: "Armenian (Armenia)",
hy: "Armenian",
eu_ES: "Basque (Spain)",
eu: "Basque",
be_BY: "Belarusian (Belarus)",
be: "Belarusian",
bg_BG: "Bulgarian (Bulgaria)",
bg: "Bulgarian",
ca_ES: "Catalan (Spain)",
ca: "Catalan",
tzm_Latn: "Central Morocco Tamazight (Latin)",
tzm_Latn_MA: "Central Morocco Tamazight (Latin, Morocco)",
tzm: "Central Morocco Tamazight",
da_DK: "Danish (Denmark)",
da: "Danish",
nl_BE: "Dutch (Belgium)",
nl_NL: "Dutch (Netherlands)",
nl: "Dutch",
et_EE: "Estonian (Estonia)",
et: "Estonian",
fi_FI: "Finnish (Finland)",
fi: "Finnish",
fr_BE: "French (Belgium)",
fr_BJ: "French (Benin)",
fr_BF: "French (Burkina Faso)",
fr_BI: "French (Burundi)",
fr_CM: "French (Cameroon)",
fr_CA: "French (Canada)",
fr_CF: "French (Central African Republic)",
fr_TD: "French (Chad)",
fr_KM: "French (Comoros)",
fr_CG: "French (Congo - Brazzaville)",
fr_CD: "French (Congo - Kinshasa)",
fr_CI: "French (Côte d’Ivoire)",
fr_DJ: "French (Djibouti)",
fr_GQ: "French (Equatorial Guinea)",
fr_FR: "French (France)",
fr_GA: "French (Gabon)",
fr_GP: "French (Guadeloupe)",
fr_GN: "French (Guinea)",
fr_LU: "French (Luxembourg)",
fr_MG: "French (Madagascar)",
fr_ML: "French (Mali)",
fr_MQ: "French (Martinique)",
fr_MC: "French (Monaco)",
fr_NE: "French (Niger)",
fr_RW: "French (Rwanda)",
fr_RE: "French (Réunion)",
fr_BL: "French (Saint Barthélemy)",
fr_MF: "French (Saint Martin)",
fr_SN: "French (Senegal)",
fr_CH: "French (Switzerland)",
fr_TG: "French (Togo)",
fr: "French",
gl_ES: "Galician (Spain)",
gl: "Galician",
ka_GE: "Georgian (Georgia)",
ka: "Georgian",
de_AT: "German (Austria)",
de_BE: "German (Belgium)",
de_DE: "German (Germany)",
de_LI: "German (Liechtenstein)",
de_LU: "German (Luxembourg)",
de_CH: "German (Switzerland)",
de: "German",
el_CY: "Greek (Cyprus)",
el_GR: "Greek (Greece)",
el: "Greek",
hu_HU: "Hungarian (Hungary)",
hu: "Hungarian",
is_IS: "Icelandic (Iceland)",
is: "Icelandic",
id_ID: "Indonesian (Indonesia)",
id: "Indonesian",
it_IT: "Italian (Italy)",
it_CH: "Italian (Switzerland)",
it: "Italian",
kab_DZ: "Kabyle (Algeria)",
kab: "Kabyle",
kl_GL: "Kalaallisut (Greenland)",
kl: "Kalaallisut",
lv_LV: "Latvian (Latvia)",
lv: "Latvian",
lt_LT: "Lithuanian (Lithuania)",
lt: "Lithuanian",
mk_MK: "Macedonian (Macedonia)",
mk: "Macedonian",
naq_NA: "Nama (Namibia)",
naq: "Nama",
pl_PL: "Polish (Poland)",
pl: "Polish",
pt_BR: "Portuguese (Brazil)",
pt_GW: "Portuguese (Guinea-Bissau)",
pt_MZ: "Portuguese (Mozambique)",
pt_PT: "Portuguese (Portugal)",
pt: "Portuguese",
ro_MD: "Romanian (Moldova)",
ro_RO: "Romanian (Romania)",
ro: "Romanian",
ru_MD: "Russian (Moldova)",
ru_RU: "Russian (Russia)",
ru_UA: "Russian (Ukraine)",
ru: "Russian",
seh_MZ: "Sena (Mozambique)",
seh: "Sena",
sk_SK: "Slovak (Slovakia)",
sk: "Slovak",
sl_SI: "Slovenian (Slovenia)",
sl: "Slovenian",
es_AR: "Spanish (Argentina)",
es_BO: "Spanish (Bolivia)",
es_CL: "Spanish (Chile)",
es_CO: "Spanish (Colombia)",
es_CR: "Spanish (Costa Rica)",
es_DO: "Spanish (Dominican Republic)",
es_EC: "Spanish (Ecuador)",
es_SV: "Spanish (El Salvador)",
es_GQ: "Spanish (Equatorial Guinea)",
es_GT: "Spanish (Guatemala)",
es_HN: "Spanish (Honduras)",
es_419: "Spanish (Latin America)",
es_MX: "Spanish (Mexico)",
es_NI: "Spanish (Nicaragua)",
es_PA: "Spanish (Panama)",
es_PY: "Spanish (Paraguay)",
es_PE: "Spanish (Peru)",
es_PR: "Spanish (Puerto Rico)",
es_ES: "Spanish (Spain)",
es_US: "Spanish (United States)",
es_UY: "Spanish (Uruguay)",
es_VE: "Spanish (Venezuela)",
es: "Spanish",
sv_FI: "Swedish (Finland)",
sv_SE: "Swedish (Sweden)",
sv: "Swedish",
tr_TR: "Turkish (Turkey)",
tr: "Turkish",
uk_UA: "Ukrainian (Ukraine)",
uk: "Ukrainian",
vi_VN: "Vietnamese (Vietnam)",
vi: "Vietnamese"
}
var SS = SpreadsheetApp.getActiveSpreadsheet();
var LocalS = SS.getSpreadsheetLocale();
if (LANGUAGE_BY_LOCALE[LocalS] == undefined) {
return false;
}
//Logger.log(true);
return true;
}
/*
function ReplaceDotsToCommas(dataIn) {
var dataOut = dataIn.map(function(num) {
if (isNaN(num)) {
return num;
}
num = num.toString();
return num.replace(".", ",");
});
return dataOut;
}
*/
沙靖琪
你可以从一个带有主页的google工作表开始，然后下拉如下所示的源页面。
您可以通过普通数据设置第一列下拉列表
主页
下拉源页面
之后，您需要设置一个名为onEdit的脚本。（如果不使用该名称，getActiveRange（）将只返回单元格A1）
并使用这里提供的代码：
function onEdit() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = SpreadsheetApp.getActiveSheet();
var myRange = SpreadsheetApp.getActiveRange();
var dvSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Categories");
var option = new Array();
var startCol = 0;
if(sheet.getName() == "Front Page" && myRange.getColumn() == 1 && myRange.getRow() > 1){
if(myRange.getValue() == "Category 1"){
startCol = 1;
} else if(myRange.getValue() == "Category 2"){
startCol = 2;
} else if(myRange.getValue() == "Category 3"){
startCol = 3;
} else if(myRange.getValue() == "Category 4"){
startCol = 4;
} else {
startCol = 10
}
if(startCol > 0 && startCol < 10){
option = dvSheet.getSheetValues(3,startCol,10,1);
var dv = SpreadsheetApp.newDataValidation();
dv.setAllowInvalid(false);
//dv.setHelpText("Some help text here");
dv.requireValueInList(option, true);
sheet.getRange(myRange.getRow(),myRange.getColumn() + 1).setDataValidation(dv.build());
}
if(startCol == 10){
sheet.getRange(myRange.getRow(),myRange.getColumn() + 1).clearDataValidations();
}
}
}
之后，通过编辑在脚本编辑器屏幕中设置触发器
在那之后你应该很好！
类似资料：
-
我正在重新创建和扩展我以前制作的文档。我已经引入了我最初使用的脚本，并在我认为合适的地方对其进行了调整，以使其在本表中正常工作，但我一定错过了一些东西。可在此处找到涉及的3个电子表格文件的可编辑示例。这些文件是一个样本“价目表”、“目录”（它汇总了所有价目表中的制造商名称，还有一个“目录”选项卡，用于我的一个主要供应商未销售的杂项物品），以及“附录B”，这是我需要帮助的文件。 这份文件是我合同的增
-
我试图在我的工作表中创建一个依赖的下拉列表，在Col A中，它将允许您选择区域列表（例如。美洲、非洲、亚洲）等，然后在Col B中显示在Col A中选择的该地区国家的列表。 我尝试使用=间接和=索引/匹配公式，但它们对我超过1000行的工作表不起作用。 我的工作簿中有11个选项卡，但是我只想将数据从“国家”选项卡拉到“主呼叫表”选项卡进行验证。所有其他选项卡都是从主工作表中删除的。 在countr
-
我的谷歌表单使用了用户马克斯·马克洛夫（Max Makhrov）编写的代码，这里的代码，在D-F列（用于位置）和H-L列（用于目标）中创建多个相关的动态下拉列表 我想帮助修改脚本以完成两件事： 无论从第一列的下拉菜单中选择了什么活动，我都希望J-L列可以使用相同的下拉菜单选项（重复）。正如您所看到的，我找到了一种方法，但对我来说，它似乎笨重且不理想，并且为错误留下了太多的空间。用户不应该选择活动两
-
我已经开始了一个有4个下拉列表的谷歌表。下拉列表2、3和4都取决于下拉列表1中的选择。我有下拉2工作，但不能在应用程序脚本编辑器的代码下拉3和4。 这是我题为“下拉列表”的工作表： 第1行位于下拉列表1中（事件/事件表的C列） 第4行至第10行位于下拉列表2中（“事件/事件”表的D列） 最后一行的第15行在下拉列表3中（事件/事件表的E列） 我有以下代码，仅适用于下拉列表2（事件/事件表的D列）：
-
我正在制作这个小网络应用程序，它需要2个地址，使用谷歌地图计算距离，并根据车辆mpg评级计算汽油成本。除了我相信最适合AJAX的最后一部分之外，一切都很完整。 我有3个列表（年份、品牌、车型），我需要根据年份和车型限制车型列表。选择后，我有一个按钮，一旦点击，将验证它是否是数据库中的有效车辆，并拉车的mpg评级，对其进行一些基本的计算。 问题是我真的不知道如何解决这个问题。在过去的几个小时里，我搜
-
本文向大家介绍JavaScript实现下拉列表，包括了JavaScript实现下拉列表的使用技巧和注意事项，需要的朋友参考一下 本文实例为大家分享了JavaScript实现下拉列表的具体代码，供大家参考，具体内容如下 这一次写了一个比较简单的下拉列表的实现，点击出现列表内容，再次点击列表消失，研究了很久，发现这种js写法确实比较好用。先看一下效果。 直接上代码，js是主要写的部分，css是随意调试