CODEWITHSUNDEEP
validationgoogle-apps-scriptgoogle-sheetsformula
Shalin
Shalin

Reputation: 135

Custom Formula Data Validation

Can we set the following data validation rule across a range to prevent duplicate entries?

=COUNTIF(B$6:B,B6)<2

The above custom formula in Data Validation stops duplicate entries in column B from row 6 downwards, so the rule on the 15th column would be:

=COUNTIF(B$6:B,B15)<2

Can we do this programmatically in GAS?

Upvotes: 0

Views: 372

Answers (1)

CMB
CMB

Reputation: 5163

Solution:

The Data Validation Builder can define a data validation rule across a range:

Sample Code:

function myFunction() {
  var cell = SpreadsheetApp.getActive().getRange("B6:B");
  var rule = SpreadsheetApp.newDataValidation().requireFormulaSatisfied("=COUNTIF(B$6:B,B6)<2").build();
  cell.setDataValidation(rule);
}

Sample Sheet:

enter image description here

P.S. If you want to reject duplicate values, you can add .setAllowInvalid(false) to the rule definition.

Upvotes: 2

Related Questions