Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Create Data Validation in certain range

I'm trying this script to create data validation but it's always doesn't work

   function test_validation()
      {
      var Spread = SpreadsheetApp.getActiveSpreadsheet();
      var Sheet = Spread.getSheetByName('Sheet1'); 
      var validate1 = Sheet.getRange("A1").getDataValidation();
      Logger.log(validate1.getCriteria());
      Logger.log(validate1.getCriteriaValues());
      var option = new Array();
      option[0]="true";
      option[1]="1";
      option[2]="2";
      option[3]="3";
      var criteria="ITEM_IN_LIST";
      var helpText="Test Help Text"
      var Combobox = Sheet.getRange("A2").getDataValidation();
      //The line below display error 
      //"Cannot find method  (class)setCriteria(string,String[]). "
      Combobox.setCriteria(criteria,option);
      Combobox.setHelpText(helpText);
      var validate2 = Sheet.getRange("A2").setDataValidation(Combobox);
      Logger.log(Sheet.getRange("A2").getDataValidation().getCriteria());
      Logger.log(Sheet.getRange("A2").getDataValidation().getCriteriaValues());
       }
like image 349
Amany Avatar asked May 24 '26 03:05

Amany


1 Answers

By now, some functions like getDataValidation() are not working anymore, instead use:

function test()
    {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); 
    var option = new Array();
    option[0]="0";
    option[1]="1";
    option[2]="2";
    option[3]="3";
    var dv = SpreadsheetApp.newDataValidation();
    dv.setAllowInvalid(false);
    dv.setHelpText("Some help text here");
    dv.requireValueInList(option, true);
    sheet.getRange("A3").setDataValidation(dv);
    }
like image 145
javapaco Avatar answered May 26 '26 21:05

javapaco



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!