How can you find or highlight all cells having validation in Google Sheets?

Question

My spreadsheet has several cells that have had validation applied to them, but it would be nice to recheck them or switch the validation to a complimentary cell. After searching on Google and looking through the web page's GUI, it was not obvious how all cells having validation could be found. Is there a way to highlight or list all cells that have had validation applied to them?

Answer 1

As I can see you need a report about all cells with data validation rules.

The next code creates the sheet with name 'dataValidation report' into the active spreadsheet and puts a little report to there.

function genDataValidationReport() {

  var res = Sheets.Spreadsheets.get(SpreadsheetApp.getActive().getId(), {
    fields: 'sheets(properties(title),data(rowData(values(dataValidation))))',
    includeGridData: false
  });

  var out = [];

  for (var i = 0; i < res.sheets.length; i++) {
    Logger.log(res.sheets[i].properties.title);
    var rowData = res.sheets[i].data[0].rowData || [];
    for (var row = 0; row < rowData.length; row++) {
      var values = rowData[row].values || [];
      for (var column = 0; column < values.length; column++) {
        if (values[column].dataValidation)
          out.push([res.sheets[i].properties.title, SpreadsheetApp.getActive().getSheetByName(
              res.sheets[i].properties.title).getRange(row + 1, column + 1).getA1Notation(),
            values[column].dataValidation.condition.type
          ]);
      }
    }
  };
  var outTitle = 'dataValidation report';
  var outSheet = SpreadsheetApp.getActive().getSheetByName(outTitle);
  if(!outSheet)
    outSheet = SpreadsheetApp.getActive().insertSheet(outTitle);
  outSheet.clearContents().getRange(1, 1, out.length, out[0].length).setValues(out);
}

Be sure that Google Sheets Advanced Service should be enabled and to include the instructions or a reference to do that.

Answer 2

The visible indicators of cells having data validation applied are

• Drop-down button
• The invalid data indicator

If the above indicators aren't shown, then you could use an add-on, Google Apps Script or the Google Sheets API to look for those cells either to apply some special formatting or to make a report of those cells.