How to change Google Sheets dropdown to the next o

Question

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 )

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 ...

Any suggestions?

Answer 1

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]);
          }
        }
      }
    }
  }
}

• Data Validation Criteria
• Data Validation