如何将DD/MM/YYYY HH:mm格式字符串转为Google Sheets可识别日期？

需求描述 #

需将DD/MM/YYYY HH:mm格式（如25/01/2022 11:00）的字符串转换为Google Sheets和Apps Script可识别的日期格式，支持公式或Apps Script脚本实现，优先脚本方案，要求适配意大利时区。

尝试过的方法及问题 #

• 自定义ARRAYFORMULA公式（从意大利语翻译）：

=ARRAYFORMULA(IF(F10:F="",,TEXT(DATE(
 IF.ERROR(REGEXEXTRACT(F10:F, "/(\d+) "), YEAR(F10:F))*1, 
 IF.ERROR(REGEXEXTRACT(F10:F, "/(\d+)"), MONTH(F10:F))*1,
 IF.ERROR(REGEXEXTRACT(F10:F, "\d+"),   DAY(F10:F))*1)+
 IF.ERROR(TIME.VALUE(F10:F), REGEXEXTRACT(F10:F, "\d+:\d+")+
 IF(REGEXMATCH(F10:F, "PM"), 0.5, 0)), "yyyy-mm-dd hh:mm")))

该公式返回#VALUE错误，提示“'11:00'是字符串，无法识别为日期”。

• 尝试正则表达式/([\d])\w+\/([\d])\w+\/([\d])\w+\s([\d])\w+\:([\d])\w+/g，但不知如何在代码中使用。
• 调整时区未解决问题。

测试场景 #

F列为源字符串列，Q列为目标日期列（需识别为日期），示例如下：

脚本调试问题 #

基于@Cooper的脚本修改后，split函数无法识别，无法覆盖原字符串日期：

let dateStringed; //source wrong dates 
var i = 0;
var flatArray;
function expired() {
  
  //bLast is the range Last Row
  dateStringed = gen.getRange(10, 6, bLast, 1).getValues(); 
  flatArray = [].concat.apply([], dateStringed);
  while (i <= bLast) {
    i++;
    convert();
  };
Logger.log(flatArray);
gen.getRange(10, 6, bLast, 1).setValues(flatArray);
};

function convert(s=flatArray[i]) {   //instead of "25/01/2022 11:00"
  let [d,m,y,hr,mn] = s.split(/[\/ :]/)
  Logger.log('y: %s m: %s d: %s hr: %s mn: %s',y,m,d,hr,mn);
  Logger.log(new Date(y,m - 1,d,hr,mn).toLocaleString());
  //don't know if it's correct, but it logs the dates
  //in an easier syntax
};

方案测试情况 #

@doubleunary的方案在意大利语版本Sheet中使用无结果，切换为美国时区可正常运行，需寻求适配意大利版本的方法。

最终解决方法 #

使用以下脚本实现格式转换，同时可删除符合特定日期条件的行：

function dateCorrected(){  
 
 gen.getRange('N10:N').clearContent();
//get the formula from another code sheet:
 //'=arrayformula( SE.ERRORE( 1 / VALORE( 
 //regexreplace( to_text(F10:F); 
 //"(\d+)/(\d+)/(\d+) (\d+):(\d+)"; "$3-$2-$1 $4.$5" ) ) ^ -1 ) )'
  var dateCorr = codeSheet.getRange('T1').getFormula();
  Logger.log(dateCorr);
  gen.getRange('N10').setFormula(dateCorr);
  gen.getFilter().sort(14, false);
  gen.getRange('N10:N').clearContent();
  gen.getRange('N10').setFormula(dateCorr);
}

内容的提问来源于stack exchange，提问作者user20749937