0

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?

random-parts
  • 2,137
  • 2
  • 13
  • 20
Oktawian Jagiełło
  • 1
  • 1
  • 3

2 Answers2

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.

Janine White
  • 439
  • 5
  • 14
  • Unfortunately there is no way to protect the data validation... in other words, any editor could remove the data validation intentionaly or accidentaly by pasting another cell over the cell with data validation. – Rubén Nov 06 '20 at 21:06
  • [@Rubén](https://stackoverflow.com/users/1595451/rub%c3%a9n) I added a script for checking if the value is a date and responding to it. – Janine White Nov 06 '20 at 23:41
  • 1
    The OP mentioned the case that someone paste a value. I think that your answer might be better if it clearly addresss the OP concerns at the begging of the answer. – Rubén Nov 06 '20 at 23:48
0

You're looking for the formatDate(date, timeZone, format) in Apps Script which formats date according to specification you described.

 // This formats the date as Greenwich Mean Time in the format
 // year-month-dateThour-minute-second.
 var formattedDate = Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd'T'HH:mm:ss'Z'");
 Logger.log(formattedDate);
ReyAnthonyRenacia
  • 17,219
  • 5
  • 37
  • 56