Custom date validation in google sheets with popup alert

Question

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?

Answer 1

The simplest answer is to use Data Validation, not a script, to control what people can enter in the date column.

1. Select the column.
2. Select Format > Number > More Formats > More Date and Time Formats.
3. Select the format that you want for your column.
4. Click Apply.
5. Shift-click on the header to remove it from the selected area.
6. Select Data > Data Validation.
7. Select Criteria: Date is valid date.
8. Select Reject input.
9. Click the Appearance checkbox.
10. Enter help text, for example, "Enter a valid date in the format YYYY-mm-dd."
11. Click Save.

If you're concerned about people removing the data validation intentionally or by pasting a value into the cell, you may use the following script for a value captured from onEdit to check if the value is a date.

const cols = {
  "date": 2 // column number that contains date
}

function onEdit(e) {
  let range = e.range;
  let col = range.getColumn();
  if (col == cols.date) {
    let value = range.getValue();
    let newDate = new Date(value);
    let timeCheck = newDate.getTime();
    let isTime = (timeCheck === timeCheck);
    if (!isTime) {
      let message = "'" + value + "' is not a date. Please enter a valid date.";
      let ui = SpreadsheetApp.getUi();
      ui.alert(message);
      range.setValue(""); // or however you want to handle a date error
    }
  }
}

If the value is not a valid date, the time returned from it is NaN, and NaN never equals itself. e will have the old and new values of the cell, so you can correct it and reapply validation if needed.