Google Sheets Date Issue-CodePudding

I'm having an issue with dates and I've been unable to find any solutions online. I've created a google sheet with a modal form to collect fleet vehicle driver info; name, dob, oln, etc., with several other date fields. The entire spreadsheet is formatted as plain text and the input types on the associated HTML pages are also text (if I format any of the date-related cells on the sheet as a date, my form won't open when I run the app script code).

When I open the form and edit a driver, the information presented is what I expect. However, if I save the record, the dob (date of birth) field in the underlying sheet turns from a date to #NUM!; surprisingly, the other date fields are not changed.

I made a copy of the spreadsheet with some dummy info, which can be viewed at https://docs.google.com/spreadsheets/d/1_trLFDh1SoIXJnmRz73ludTaT64qgXd8qJKMC6grueU/edit#gid=0

Published link is https://docs.google.com/spreadsheets/d/e/2PACX-1vSzyRgN6i0RJRjcUKxcNlnuQC0P1ZHlhPPuy19G9q-sp0XQfuMjiDWvHMnWA3_HT_QSTQEDYMgrUezV/pubhtml

Code for pulling data in from spreadsheet. Code is in 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; }

Code from 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); }

partial Code from editcustomer.html

<div >
  <label for="birth-date" >DOB</label>
  <input type="text"  id="birth-date" placeholder="Birthday">
</div>

CodePudding user response：

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

Reference

Unary plus ( )