电子表格特定格式日期提取时的时区异常问题咨询

Let’s break down how to resolve your timezone issues when extracting dates, moving them to another spreadsheet, and creating calendar events. The core problem is ensuring we preserve the original timezone context at every step—since relying on default script/spreadsheet timezones can lead to unexpected shifts.

1. Correctly Parse the Original Date String #

Your original cell value is a formatted string (not a native Date object), so we need to explicitly specify the original spreadsheet’s timezone (GMT-8) when parsing it to get an accurate timestamp.

function getCorrectDate(originalCellValue) {
  // Remove the day abbreviation prefix (e.g., "mon. ")
  const dateTimeStr = originalCellValue.replace(/^\w+\.\s/, "");
  
  // Define original timezone and input format (matches "dd/mm/yyyy hh:mm:ss")
  const originalTimezone = "GMT-08:00";
  const inputFormat = "dd/MM/yyyy HH:mm:ss";
  
  // Parse the string into a Date object using the original timezone
  return Utilities.parseDate(dateTimeStr, originalTimezone, inputFormat);
}

This function returns a Date object that represents the exact moment in UTC corresponding to 12:00 PM GMT-8 on 02/04/2018—no guesswork from default timezones.

2. Write to the New Spreadsheet Consistently #

When moving dates to the new spreadsheet, use native Date objects instead of formatted strings. This preserves the absolute timestamp, so the new sheet can adjust the display based on its own timezone settings while keeping the event time accurate.

function writeToNewSheet(correctDate) {
  const newSheet = SpreadsheetApp.openById("your-new-spreadsheet-id").getSheetByName("Sheet1");
  
  // Append the Date object (will display in the new sheet's timezone)
  newSheet.appendRow([correctDate]);
  
  // Optional: If you need to display the original local time regardless of the sheet's timezone, use a formatted string:
  const formattedOriginalTime = Utilities.formatDate(correctDate, "GMT-08:00", "EEE dd/MM/yyyy HH:mm:ss");
  newSheet.appendRow([formattedOriginalTime]); // Note: This is a text string, not an editable date
}

3. Create Calendar Events with Explicit Timezone #

The most reliable way to avoid timezone shifts in calendar events is to use the Advanced Calendar Service, which lets you specify the timezone directly (bypassing the script’s default timezone).

First, Enable the Advanced Calendar Service: #

1. Open your script editor.
2. Go to Extensions > Apps Script.
3. Click Services (left sidebar) > Add Service.
4. Select Calendar API and click Add.

Then, Create the Event: #

function createEventWithCorrectTimezone() {
  const originalCellValue = "mon. 02/04/2018 12:00:00";
  const correctDate = getCorrectDate(originalCellValue);
  const calendarId = "primary"; // Use your calendar ID or "primary" for your main calendar
  const eventTimezone = "GMT-08:00"; // Match the original spreadsheet's timezone
  
  // Set event duration (e.g., 1 hour)
  const endDate = new Date(correctDate.getTime() + 60 * 60 * 1000);
  
  // Format times in ISO 8601 format with explicit timezone
  const startTime = {
    dateTime: Utilities.formatDate(correctDate, eventTimezone, "yyyy-MM-dd'T'HH:mm:ss"),
    timeZone: eventTimezone
  };
  
  const endTime = {
    dateTime: Utilities.formatDate(endDate, eventTimezone, "yyyy-MM-dd'T'HH:mm:ss"),
    timeZone: eventTimezone
  };
  
  // Create the event via Advanced Calendar API
  const event = Calendar.Events.insert({
    summary: "Your Event Title",
    start: startTime,
    end: endTime
  }, calendarId);
  
  console.log("Event created successfully: " + event.htmlLink);
}

Why This Works: #

• Parsing: Utilities.parseDate() ensures we interpret the original string using the correct timezone, avoiding misinterpretation by the script’s default settings.
• Spreadsheet Writing: Native Date objects preserve the absolute timestamp, so the new sheet can display it correctly in its own timezone. Formatted strings are optional if you need fixed local time display.
• Calendar Events: The Advanced Calendar Service lets you explicitly define the event’s timezone, so it’s created at the exact local time you want—regardless of the script’s timezone.

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