Google App Script Ignore Data Validation Error

Question

I have a few Sheets that have Data Validation rules to use as drop downs. My code is now creating errors when trying to insert other (non-validated) data into these cells.

I am looking for a way to ignore the Data Validation rule and insert the data without making changes to the Data Validation rules already set. I just want to ignore the App Script Error.

The data that you entered in cell U440 violates the data validation rules set on this cell.

The line that the code errors on has no Data validation issues so im not fully sure how to deal with the error.

function CountCol(ss, col){var vals = ss.getRange(col+'1:'+col).getValues();var last = vals.filter(String).length;return last+1;}

This function returns the row number of the first empty row for a declared Column. Not sure how this violates the Data Validation rule as in my code i am Counting Column 'C' and Data validations are on columns 'O', 'P', 'Q', 'U' and 'V' and the validation error column is 'U'.

var row_data = [[""], [""]]; //example data
var team = "12345645647abcdef"; //example spreadsheet id
var sn = "Sheet1"; //example sheet name
var caseload = SpreadsheetApp.openById(team).getSheetByName(sn);
var append = CountCol(caseload, "C");
Logger.log("adding to row: "+String(append));
try{
  caseload.getRange(append, 1, 1, row_data[0].length).setValues(row_data); //this is where the error should be. The output manages to write most colums but stops before writing column 'U'.
}catch(e){
  Browser.msgBox(e);
  Logger.log("failed to append: "+String(append)+ ":" + String(row_data[0][0]) + ": " + String(row_data[0][1]));
}
caseload.getRange(append, 1, 1, row_data[0].length).setBackground("red");

due to this requiring a Google Sheets with Data Validation i cannot create a MCVE, The code i posted is 100% of the code required to test this function. You just need to create a sheet with data validation and ensure row_data[x][y] violates that rule. — Scott Paterson

stewartmcgown stewartmcgown · Accepted Answer · 2019-02-01T14:17:45

Have you tried a simple try/catch?

try {
  SpreadsheetApp.doThing()
} catch (e) {
  // ignore
}

You could also disable the data validation on a range by calling setDataValidation(rule) to allow any input, then set the data validation back to the old one after you've inserted the data you need.

This page has examples on how to do just that: https://developers.google.com/apps-script/reference/spreadsheet/data-validation

Google App Script Ignore Data Validation Error

1 Answers