Google-sheets – How to you find or highlight all cells having validation in Google Sheets

google sheetsgoogle-sheets-data-validationlistsearch

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?

Best Answer

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.

enter image description here

Related Topic