[Solved] Google Sheets – Create Data Validation

by


You can use this sample code:

function createDataValidation() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s1 = ss.getSheetByName("Sheet1");
  var s2 = ss.getSheetByName("Sheet2");

  var s2_lastRow = s2.getLastRow();

  //create data validation per row 
  for (var row = 1; row <= s2_lastRow; row++){
    
    //create an a1Notation to select a complete row sample: "A1:1", "A2:2", and so on.
    var a1Notation = "A"+row+":"+row;

    //create data validation rule
    var listRange = s2.getRange(a1Notation);
    var rule = SpreadsheetApp.newDataValidation()
                  .requireValueInRange(listRange, true)
                  .setAllowInvalid(false)
                  .build();

    //assign rule to the current cell in sheet1 column "O"
    //add 1 row offset since data validation will start at cell "O2"
    a1Notation = "O"+(row+1);
    Logger.log(a1Notation);
    var cell = s1.getRange(a1Notation);
    cell.setDataValidation(rule);
  }

}

What it does?

  1. Get individual sheets using Spreadsheet.getSheetByName(name)
  2. Get Sheet2’s last row number using Sheet.getLastRow()
  3. Loop each individual row in Sheet2
  4. Create an a1Notation string for the current row using this format (row1: “A1:1”)
  5. Select a range to be used for the data validation using Sheet.getRange(a1Notation)
  6. Create a new data validation rule using SpreadsheetApp.newDataValidation(). This will return a DataValidationBuilder where you can specify what type of data validation to use. For Values in Range, use DataValidationBuilder.requireValueInRange(range, showDropdown)

Regarding rejecting inputs, by default new data validation rules set it to true. See DataValidationBuilder.setAllowInvalid(allowInvalidData)

  1. Set newly created data validation rule for a specific cell using Range.setDataValidation(rule)

OUTPUT:

Sample Sheet2:

enter image description here

Sample Output Sheet1:

enter image description here enter image description here

6

solved Google Sheets – Create Data Validation