如何将Google表格下拉菜单更改为下一个选项? [英] How to change Google Sheets dropdown to the next option?
问题描述
我在GSheets上有一个数据验证输入(下拉列表)，其中包含来自另一个工作表的值范围(在同一文件中，例如:Sheet1！A2:A40)
I have a data validation input (dropdown) on GSheets with a range of values from another sheet, in the same file, (ex: Sheet1!A2:A40 )
我要尝试的是使用基于时间的触发器循环浏览该下拉菜单的所有40个选项.假设脚本每5分钟运行一次，将下拉菜单更改为下一个选项，直到完成所有40个选项为止.
What I'm trying to is to cycle through all 40 options of that dropdown using a time-based trigger. Let's say the script will run at every 5min, changing the dropdown to the next option until it has finished all the 40 options.
最后，例如，星期一，该例程将每周运行一次.
Finally, this routine will run on a weekly basis, said Monday, for example...
现在，我一直在尝试更改下拉选项...
Right now, I'm stuck trying to change the dropdown options ...
有什么建议吗?
推荐答案
通过所有可能的选择来增加DataValidation
此功能将为标准范围为A2:A40的电子表格(即所有工作表)中的每个DataValidation增加选项选择.它将针对标准范围的高度执行此操作，然后将属性ENABLEOPTIONSCHANGE设置为FALSE，这将禁止任何进一步的更改，直到通过调用enableAllTheOptionsChange()将其设置为TRUE为止.因此，首先必须触发enableAllTheOptionsChange()，然后才能触发changeAllTheOptions()，直到A2:A40的高度为39.
Incrementing DataValidations through All Possible Choices
This function will increment the options selection for every DataValidation in the spreadsheet (i.e. all sheets) where it's criteria range is A2:A40. It will do it for the height of the criteria range and then it will set the Property ENABLEOPTIONSCHANGE to FALSE which will inhibit any further changes until it is set back to TRUE by calling enableAllTheOptionsChange(). So first you must trigger enableAllTheOptionsChange() and then you can trigger changeAllTheOptions() upto the height of the A2:A40 which is 39.
function enableAllTheOptionsChange() {
var prop=PropertiesService.getScriptProperties();
prop.setProperty('ENABLEOPTIONSCHANGE', 'TRUE')
}
function changeAllTheOptions() {
var prop=PropertiesService.getScriptProperties();
if(prop.getProperty('ENABLEOPTIONSCHANGE')=='TRUE') {
var ss=SpreadsheetApp.openById('id');
var osh=ss.getSheetByName('Sheet1');//validation values
var org=osh.getRange(2,1,39,1);//validaton values
var ovA=org.getValues();
var optionsA=ovA.map(function(r){return r[0]});
Logger.log(optionsA);
var count=Number(prop.getProperty(org.getA1Notation()));
if(count>ovA.length) {
prop.setProperty(org.getA1Notation(), 0);
prop.setProperty('ENABLEOPTIONSCHANGE', 'FALSE')
}else{
prop.setProperty(org.getA1Notation(), Number(count + 1))
}
//Logger.log('%s-%s',org.getA1Notation(),prop.getProperty(org.getA1Notation()));
var allshts=ss.getSheets();
for(var i=0;i<allshts.length;i++) {
var name=allshts[i].getName();
//if(name=="Globals"){continue;}//You can use this approach to skip sheets
var dataRg=allshts[i].getDataRange();
var vA=dataRg.getValues();
var dataRgA1=dataRg.getA1Notation();
var valRules=dataRg.getDataValidations();
for(var j=0;j<valRules.length;j++) {
for(var k=0;k<valRules[j].length;k++) {
var rule=valRules[j][k];
if(rule!=null) {
var type=rule.getCriteriaType();
var args=rule.getCriteriaValues();
if(args[0].getA1Notation()==org.getA1Notation()) {
var cv=allshts[i].getRange(j + 1,k + 1).getValue();
allshts[i].getRange(j + 1,k + 1).setValue(optionsA[(optionsA.indexOf(cv) + 1) % optionsA.length]);
//Logger.log('cv: %s optionsA[%s]: %s -- Length: %s',cv,(optionsA.indexOf(cv) + 1) % optionsA.length,optionsA[(optionsA.indexOf(cv) + 1) % optionsA.length],optionsA.length);
}
//Logger.log('\nSheet:%s\nType: %s\nArgs[0]: %s\nArgs[1]: %s\nValue[%s][%s]:%s',name,type,args[0].getA1Notation(),args[1],j,k,vA[j][k]);
}
}
}
}
}
}
