I have a google sheet which is edited everyday by over 20 people. And sometimes it happens that someone paste something other than a date in "Date Column" or just simple write date in incorrect format. So I am looking to make a script which set date format to "yyy-mm-dd", then set data validation to check if date is correct (to avoid situation when you have a date like "2017-22-17"), and at the end popup message box if you have pasted or wrote incorrect date. I wrote below code and triggered it "onEdit" but I cannot handle this popup message thing ;/
function SetDateFormat() {
var ss = SpreadsheetApp.getActiveSpreadsheet()
var sheet = ss.getSheetByName("Sheet1")
var cell = sheet.getRange(4, 3, sheet.getMaxRows())
cell.setNumberFormat('yyyy-mm-dd')
}
function setAndCheckDate() {
var ss = SpreadsheetApp.getActive()
var sheet =ss.getSheetByName("Sheet1")
var cell = sheet.getRange(4, 3, sheet.getMaxRows())
var rule1 = cell.getDataValidation()
var rule2 =SpreadsheetApp.newDataValidation().requireDate().build()
cell.setDataValidation(rule2)
if(rule1 !== rule2) {
Browser.msgBox("Date is incorrect!")
}
}
Another thing is that validating script doesn't work while I'm pasting cell with incorrect data to my date column.
https://docs.google.com/spreadsheets/d/1ZPbfX60E46W95XhMTXpBSLTUAT71QCe-MFnYhYy2PPw/edit?usp=sharing
Can you advise?