谷歌表格日期問題-有解無憂

我遇到了日期問題，而且我一直無法在網上找到任何解決方案。我創建了一個帶有模態表單的谷歌表來收集車隊車輛駕駛員資訊；name、dob、oln 等，以及其他幾個日期欄位。整個電子表格格式為純文本格式，相關 HTML 頁面上的輸入型別也是文本格式（如果我將作業表上任何與日期相關的單元格格式設定為日期，當我運行應用程式腳本時，我的表單將不會打開代碼）。

當我打開表單并編輯驅動程式時，顯示的資訊正是我所期望的。但是，如果我保存記錄，基礎作業表中的 dob（出生日期）欄位將從日期變為 #NUM！；令人驚訝的是，其他日期欄位沒有更改。

我制作了一份包含一些虛擬資訊的電子表格副本，可以在https://docs.google.com/spreadsheets/d/1_trLFDh1SoIXJnmRz73ludTaT64qgXd8qJKMC6grueU/edit#gid=0查看

發布的鏈接是https://docs.google.com/spreadsheets/d/e/2PACX-1vSzyRgN6i0RJRjcUKxcNlnuQC0P1ZHlhPPuy19G9q-sp0XQfuMjiDWvHMnWA3_HT_QSTQEDYMgrUezV/pubhtml

從電子表格中提取資料的代碼。代碼在 ServerSideFuncs.gs 中

function editCustomerById(id, customerInfo){ const ss = SpreadsheetApp.getActiveSpreadsheet(); const ws = ss.getSheetByName("Drivers"); const custIds = ws.getRange(2, 1, ws.getLastRow()-1, 1).getValues().map(r => r[0].toString().toLowerCase()); const
posIndex = custIds.indexOf(id.toString().toLowerCase()); const rowNumber = posIndex === -1 ? 0 : posIndex   2; ws.getRange(rowNumber, 2, 1, 20).setValues([[ customerInfo.firstName, customerInfo.middleName, customerInfo.lastName,  customerInfo.birthDate,
customerInfo.driverClassification, customerInfo.email, customerInfo.cellPhone, customerInfo.active, customerInfo.dpaa, customerInfo.driversLicense, customerInfo.dlState, customerInfo.deptOrg, customerInfo.dateSubmitted, customerInfo.dateReceived, customerInfo.dateExpires,
customerInfo.accepted, customerInfo.certDate, customerInfo.certClassification, customerInfo.notified, customerInfo.standing ]]); return true; }

來自 main.html 的代碼

function afterEditViewLoads(params){ loadingStart(); document.getElementById("customer-id").value = params.custID; google.script.run.withSuccessHandler(function(customerInfo){ document.getElementById("first-name").value = customerInfo.firstName; document.getElementById("middle-name").value
= customerInfo.middleName; document.getElementById("last-name").value = customerInfo.lastName; document.getElementById("birth-date").value = customerInfo.birthDate; document.getElementById("driver-classification").value = customerInfo.driverClassification;
document.getElementById("email").value = customerInfo.email; document.getElementById("cell-phone").value = customerInfo.cellPhone; document.getElementById("active").value = customerInfo.active; document.getElementById("dpaa").value = customerInfo.dpaa;
document.getElementById("drivers-license").value = customerInfo.driversLicense; document.getElementById("dl-state").value = customerInfo.dlState; document.getElementById("deptOrg").value = customerInfo.deptOrg; document.getElementById("date-submit").value
= customerInfo.dateSubmitted; document.getElementById("date-received").value = customerInfo.dateReceived; document.getElementById("accepted").value = customerInfo.accepted; document.getElementById("date-expires").value = customerInfo.dateExpires; document.getElementById("cert-date").value
= customerInfo.certDate; document.getElementById("cert-classification").value = customerInfo.certClassification; document.getElementById("notified").value = customerInfo.notified; document.getElementById("standing").value = customerInfo.standing; loadingEnd();
}).getCustomerById(params.custID); } function editCustomer(){ loadingStart(); var customerInfo = {}; customerInfo.firstName = document.getElementById("first-name").value; customerInfo.middleName = document.getElementById("middle-name").value; customerInfo.lastName
= document.getElementById("last-name").value; customerInfo.birthDate = document.getElementById("birth-date").value; customerInfo.driverClassification = document.getElementById("driver-classification").value; customerInfo.email = document.getElementById("email").value;
customerInfo.cellPhone = document.getElementById("cell-phone").value; customerInfo.active = document.getElementById("active").value; customerInfo.dpaa = document.getElementById("dpaa").value; customerInfo.driversLicense = document.getElementById("drivers-license").value;
customerInfo.dlState = document.getElementById("dl-state").value; customerInfo.deptOrg = document.getElementById("deptOrg").value; customerInfo.dateSubmitted = document.getElementById("date-submit").value; customerInfo.dateReceived = document.getElementById("date-received").value;
customerInfo.dateExpires = document.getElementById("date-expires").value; customerInfo.accepted = document.getElementById("accepted").value; customerInfo.certDate = document.getElementById("cert-date").value; customerInfo.certClassification = document.getElementById("cert-classification").value;
customerInfo.notified = document.getElementById("notified").value; customerInfo.standing = document.getElementById("standing").value; var id = document.getElementById("customer-id").value; google.script.run.withSuccessHandler(function(res){ document.getElementById("save-success-message").classList.remove("invisible");
loadingEnd(); setTimeout(function(){ document.getElementById("save-success-message").classList.add("invisible"); },2000); }).editCustomerById(id,customerInfo); }

來自 editcustomer.html 的部分代碼

<div class="col-md-auto">
  <label for="birth-date" class="form-label">DOB</label>
  <input type="text" class="form-control" id="birth-date" placeholder="Birthday">
</div>

uj5u.com熱心網友回復：

function editCustomerById(id, customerInfo) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ws = ss.getSheetByName("Drivers");
  const custIds = ws.getRange(2, 1, ws.getLastRow() - 1, 1).getValues().map(r => r[0].toString().toLowerCase());
  const posIndex = custIds.indexOf(id.toString().toLowerCase());
  const rowNumber = posIndex === -1 ? 0 : posIndex   2;
  ws.getRange(rowNumber, 2, 1, 20).setValues([
    [
      customerInfo.firstName,
      customerInfo.middleName,
      customerInfo.lastName,
       customerInfo.birthDate,   // <------------ I think the trouble is here
      customerInfo.driverClassification,
      customerInfo.email,
      customerInfo.cellPhone,
      customerInfo.active,
      customerInfo.dpaa,
      customerInfo.driversLicense,
      customerInfo.dlState,
      customerInfo.deptOrg,
      customerInfo.dateSubmitted,
      customerInfo.dateReceived,
      customerInfo.dateExpires,
      customerInfo.accepted,
      customerInfo.certDate,
      customerInfo.certClassification,
      customerInfo.notified,
      customerInfo.standing
    ]
  ]);
  return true;
}

參考

一元加 ( )

轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/378461.html

標籤：谷歌应用程序脚本谷歌表格

上一篇：Google表格：索引匹配回傳多個單元格

下一篇：Google表格-在查詢中使用INDIRECT（或替代）