CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Casper
Casper

Reputation: 1435

Equivalent of getDataRange() to get range(s) with datavalidations?

I have a sheet where people copy/paste values. This causes them to bypass the data validations as it just "warns" them instead of rejecting the value.

I want to find all ranges in a sheet that have data validations. Tried to find something in the reference guide but it seems the only way to do this is to just loop over every cell and check for its data validations?

Would be great if someone has a more efficient way of checking this.

Upvotes: 2

Views: 511

Answers (1)

user6655984
user6655984

Reputation:

You do need a loop, but not over cells (which would take an API call getDataValidation per cell) but over the double array returned by getDataValidations: 

var sheet = SpreadsheetApp.getSheetByName("Sheet1");
var range = sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns());
var rules = range.getDataValidations();
for (var i = 0; i < rules.length; i++) {
  for (var j = 0; j < rules[0].length; j++) { 
    if (rules[i][j]) {
      // do something with the cell
    }
  }
}

There isn't a more direct method like getNamedRanges or getProtections.

Possible workaround: give names to the ranges that you want to validate, so you can retrieve them with getNamedRanges.

Upvotes: 1

Related Questions