2

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.

  • there isnt any. – user0 Jan 3 at 15:44
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.

enter image description here

  • 1
    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
0

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 agree to our terms of service, privacy policy and cookie policy

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