Create Data Validation in certain range

Question

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

javapaco · Accepted Answer · 2017-08-29 15:38:56Z

7

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

answered Aug 29, 2017 at 15:38

javapaco

711 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Miss Chanandler Bong · Accepted Answer · 2021-02-24 12:23:14Z

1

I found the answer finally in issue 2680

the working code

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 = sheet.getRange("A3").getDataValidation();
    dv.setAllowInvalidData(false);
    dv.setHelpText("Some help text here");
    dv.setCriteria(SpreadsheetApp.DataValidationCriteria.ITEM_IN_LIST,true,option );
    sheet.getRange("A3").setDataValidation(dv);
}

edited Feb 24, 2021 at 12:23

Miss Chanandler Bong

4,27811 gold badges30 silver badges40 bronze badges

answered May 29, 2013 at 8:16

Amany

3392 gold badges5 silver badges16 bronze badges

1 Comment

Wicket Over a year ago

This answer is obsolete. Related Do Google have changed how data validation is handled?

Yuri Khristich · Accepted Answer · 2021-07-18 00:36:24Z

1

Working example from 2021:

function set_data_validation() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dest_range = ss.getActiveSheet().getRange('A1:A10');
  var list_range = ss.getSheetByName('List').getRange('A1:A');
  var rule = SpreadsheetApp.newDataValidation()
                           .requireValueInRange(list_range)
                           .setAllowInvalid(false)
                           .build();
  var rules = dest_range.getDataValidations().map(x=>[rule]);
  dest_range.setDataValidations(rules);
}

It will set data validation 'List from a range' for cells 'A1:A10' on current sheet. The range is column 'A' on a sheet with name 'List'.

edited Jul 18, 2021 at 0:36

answered Jul 18, 2021 at 0:29

Yuri Khristich

14.6k3 gold badges11 silver badges25 bronze badges

Collectives™ on Stack Overflow

Create Data Validation in certain range

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related