Google表格邮件脚本优化：空日期字段避免显示1970/01/01

Hey Antonio, I see the issue here—when your spreadsheet's date fields are empty, converting them to a Date object automatically defaults to the Unix epoch (01/01/1970). Let's fix that by adding simple checks to handle empty values before formatting them as dates.

Why This Happens #

Empty date cells in Google Sheets return either null or an empty string. When you pass these values to new Date() or Utilities.formatDate(), they get converted to the Unix epoch timestamp (January 1, 1970), which is why you're seeing that default date in your emails.

Modified Code with Empty Date Checks #

Here's the updated script with conditional logic to skip formatting for empty date fields:

function sendEmailbyTransporter() { 
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Status de Embarque Integrado"); 
  // Get your table data 
  var startRow = 27; // First row of data to process 
  var numRows = sheet.getLastRow() - startRow + 1; // Fix: Calculate correct number of rows to process
  var dataRange = sheet.getRange(startRow, 1, numRows, 13); // Fix: Column range adjusted to 13 (since Entrega is rowData[12])
  var data = dataRange.getValues(); // Fetch values for each row in the Range. 

  // Loop through the data to build your table 
  var message = "<html><body><table style=\"text-align:center\"><tr><th>NF</th><th>CT-E</th><th>Cliente</th><th>Cidade</th><th>UF</th><th> Previsão de Entrega</th><th>Ocorrência</th><th>Detalhe da Ocorr.</th><th>Data da Entrega</th>"; 
  var sendEmail = false; // If there is content to send, will be set to true 
  var Email = sheet.getRange("I24:l24").getValue(); // Moved outside loop to avoid repeated range calls

  for (var i = 0; i < data.length; ++i) { 
    var rowData = data[i]; 
    var NF = rowData[0]; 

    if (NF != "") { //Checks for blank Code. If blank, then skip and don't add to the table. 
      var CTE = rowData[1]; 
      // Handle empty CTE date
      var CTEAsDate = CTE ? Utilities.formatDate(CTE, "GMT" , "dd/MM/yyyy" ) : ""; 

      var Cliente = rowData[4]; 
      var Cidade = rowData[5]; 
      var UF = rowData[6]; 

      var PrevEntrega = rowData[7];
      // Handle empty Previsão de Entrega date
      var PrevEntregaAsDate = PrevEntrega ? Utilities.formatDate(new Date(PrevEntrega), "GMT" , "dd/MM/yyyy" ) : ""; 

      var Ocorr1 = rowData[10]; 
      var Ocorr2 = rowData[11]; 

      var Entrega = rowData[12];
      // Handle empty Data da Entrega date
      var EntregaAsDate = Entrega ? Utilities.formatDate(new Date(Entrega), "GMT" , "dd/MM/yyyy" ) : ""; 

      message += "<tr><td>"+NF+"</td><td style=\"text-align:left\">"+CTE+"</td><td>"+Cliente+"</td><td>"+Cidade+"</td><td>"+UF + "</td><td>"+PrevEntregaAsDate+"</td><td>"+Ocorr1 + "</td><td>"+Ocorr2 + "</td><td>"+EntregaAsDate + "</td></tr>"; 
      sendEmail = true; //There is content to send 
    } 
  } 

  message += "</table></body></html>"; 

  if (sendEmail) { 
    var subject = "Status de Embarque da NF " + NF + "!"; 
    MailApp.sendEmail({ 
      name: 'XXXl', 
      to: Email, 
      subject: subject, 
      htmlBody: message 
    }); 
  } 
}

Key Improvements #

• Empty Date Checks: For each date field (CTE, PrevEntrega, Entrega), we use a ternary operator (condition ? valueIfTrue : valueIfFalse) to check if the cell has a value. If it's empty, we set the formatted date string to blank; otherwise, we format the date normally.
• Optimized Range Calls: Moved the Email range fetch outside the loop to avoid repeated calls to sheet.getRange(), which improves script performance.
• Fixed Row/Column Calculation: Adjusted numRows to calculate the correct number of rows from startRow to the last row, and updated dataRange to include column 13 (since Entrega is at index 12, which is the 13th column).

Now when your spreadsheet has empty date cells, those fields will show up as blank in your email instead of the 01/01/1970 default.

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