u4carson
0
I am looking for assistance in developing a google sheets formula or script that returns the Cell Reference of any cells (in that Row) that contain a validation Error.
For example in Row 1, Cells B1 and E1 contain validation errors. I would like to return the following in A1.
A1 = "Validation Error in Cell(s) B1, E1"
Each row contains 50+ columns so individually checking each validation in a formula is not practical.
This may require some modification depending upon you unique siutation.
function cellerrors(ra1 = "A1:M1") {
const ss = SpreadsheetApp.getActive();
const sh = ss.getActiveSheet();
const rg = sh.getRange(ra1);
const ri = rg.getRow();
const ci = rg.getColumn();
const vs = rg.getDisplayValues();
let o = vs.map((r, i) => {
let row = [];
r.forEach((c, j) => {
let cv = sh.getRange(ri + i, ci + j).getDataValidation().getCriteriaValues();
cv[0].unshift('');
if (!~cv[0].indexOf(c)) {
row.push(`Valid Values: ${cv[0].join(',')} Row: ${ri + i} Col: ${ci + j} Error - Value: ${c}`);
} else {
row.push(`Valid Values: ${cv[0].join(',')} Row: ${ri + i} Col: ${ci + j} No Error - Value: ${c}`);
}
})
return row;
})
Logger.log(JSON.stringify(o));
sh.getRange("A2").setValue(o.flat().join('\n'));
}
