带有弹出式警报的Google表格中的自定义日期验证 [英] Custom date validation in google sheets with popup alert

问题描述

我有一个Google表格，每天有20多个人对其进行编辑.有时会发生某人将日期以外的其他内容粘贴到日期栏"中，或者只是以错误的格式粘贴了简单的写入日期的情况.因此，我正在寻找一个脚本，将日期格式设置为"yyy-mm-dd"，然后设置数据验证以检查日期是否正确(以避免出现日期类似于"2017- 22 -17)，如果您粘贴或输入了错误的日期，则在最后弹出消息框. 我在下面的代码中编写并触发了"onEdit"，但我无法处理此弹出消息；/

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

你能建议吗?

推荐答案

您正在Google Apps脚本中查找formatDate(日期，timeZone，格式)，该格式根据您所描述的规范对日期进行格式化.

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);

这篇关于带有弹出式警报的Google表格中的自定义日期验证的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！