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?

migrated from superuser.com Jan 3 at 15:37

This question came from our site for computer enthusiasts and power users.


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++) {
    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(),
  var outTitle = 'dataValidation report';
  var outSheet = SpreadsheetApp.getActive().getSheetByName(outTitle);
    outSheet = SpreadsheetApp.getActive().insertSheet(outTitle);
  outSheet.clearContents().getRange(1, 1, out.length, out[0].length).setValues(out);

enter image description here

  • I think that you should mention that the Google Sheets Advanced Service should be enabled and to include the instructions or a reference to do that. – Rubén Jan 6 at 0:22

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.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.