Hey there! Let's break down why your code is returning undefined and get that expiration reminder date working properly.

The Root Cause #

Your main issue is how you're handling the expiration date from the form. When you grab responses['Expiration Date'][0], that's a string value (like "2024-12-31"), not a timestamp. Trying to multiply that string by 1000 gives you NaN (Not a Number), and any math with NaN results in NaN—which shows up as undefined or a weird numeric error in your sheet.

Step-by-Step Fix #

Here's the corrected code with explanations:

function onFormSubmit(e) {
  var responses = e.namedValues;
  var MILLIS_PER_DAY = 1000 * 60 * 60 * 24;
  var expireDateStr = responses['Expiration Date'][0].trim();
  
  // Convert the date string to a valid Date object first
  var expireDate = new Date(expireDateStr);
  
  // Check if the date is valid (avoid errors from bad date formats)
  if (isNaN(expireDate.getTime())) {
    SpreadsheetApp.getUi().alert('Invalid expiration date submitted: ' + expireDateStr);
    return;
  }
  
  // Calculate reminder date: subtract 183 days (in milliseconds)
  var noticeDateMillis = expireDate.getTime() - (183 * MILLIS_PER_DAY);
  var noticeDate = new Date(noticeDateMillis); // Convert back to Date object
  
  // Get the correct row from the event object (more reliable than getActiveRange)
  var sheet = e.range.getSheet();
  var row = e.range.getRow();
  var column = e.values.length + 1; // Target the next empty column
  
  // Write the Date object (Sheets will auto-format it as a date)
  sheet.getRange(row, column).setValue(noticeDate);
}

Key Improvements: #

• Valid Date Conversion: We use new Date(expireDateStr) to turn the form's date string into a proper Date object, then get its timestamp with getTime().
• Validation Check: Added a check to catch invalid dates (like typos in the form input) and alert you instead of breaking the script.
• Reliable Row Reference: Using e.range.getRow() instead of sheet.getActiveRange().getRow() ensures we target the exact row that was just submitted (critical if multiple people submit at once).
• Date Object Output: Writing a Date object to the sheet instead of raw milliseconds lets Google Sheets recognize it as a date and display it in a readable format.

Quick Note #

Make sure your form's "Expiration Date" field uses a format that the JavaScript Date constructor can parse (like YYYY-MM-DD, MM/DD/YYYY, or DD/MM/YYYY depending on your locale). If you're getting invalid date alerts, you might need to adjust the string parsing logic to match your form's date format.

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