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?
-
there isnt any.– user0Jan 3, 2019 at 15:44
2 Answers
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.
-
1I 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, 2019 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.