Issue with date formatting when syncing spreadsheet data to Google Calendar

Other Questions Google Sheets
1

I’m having trouble with a script that’s intended to transfer events from my spreadsheet to Google Calendar. It appears there is an issue with the date formatting, even though my spreadsheet uses the standard format.

Here’s an updated version of the code I’m using:

function syncEvents() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const data = sheet.getDataRange().getValues();
  const calendar = CalendarApp.getCalendarById('MY_CALENDAR_ID');

  data.slice(1).forEach(row => {
    const [eventDate, title, startTime, endTime, location, description, eventId] = row;
    const start = new Date(eventDate);
    start.setHours(startTime.getHours(), startTime.getMinutes());
    const end = new Date(eventDate);
    end.setHours(endTime.getHours(), endTime.getMinutes());

    try {
      const event = eventId ? calendar.getEventSeriesById(eventId) : null;
      if (event) {
        event.setTitle(title);
        event.setDescription(description);
        event.setLocation(location);
        event.setTime(start, end);
      } else {
        calendar.createEvent(title, start, end, { description, location });
      }
    } catch (error) {
      console.log('Error processing event:', error);
    }
  });
}

Could someone help me identify what might be causing the date formatting issue? I appreciate any assistance.

2

I’ve encountered similar issues with date formatting when syncing spreadsheet data to Google Calendar. One potential solution is to explicitly parse the date string from your spreadsheet. Try modifying your code to use:

const start = new Date(Date.parse(eventDate));
const end = new Date(Date.parse(eventDate));

This approach ensures that the date is correctly interpreted, regardless of its format in the spreadsheet. Additionally, consider logging the parsed date values to verify they’re being processed correctly. If the issue persists, you might need to investigate the specific date format used in your spreadsheet and adjust your parsing method accordingly.

3

hey there excitedgamer85! looks like ur havin some trouble with the dates. have u tried using the getTime() method to convert the dates to milliseconds? that might help with any weird formatting issues. also, double-check ur spreadsheet to make sure the dates are actually in the format u think they are. good luck!

4

I faced a similar challenge when syncing my work schedule to Google Calendar. What worked for me was using the Utilities.formatDate() function to ensure consistent date formatting. Here’s a snippet that might help:

const formattedDate = Utilities.formatDate(eventDate, Session.getScriptTimeZone(), ‘yyyy-MM-dd’);
const start = new Date(formattedDate + ‘T’ + startTime.toTimeString());
const end = new Date(formattedDate + ‘T’ + endTime.toTimeString());

This approach explicitly sets the date and time, avoiding potential timezone issues. Also, make sure your spreadsheet’s locale settings match your script’s timezone. It’s a small tweak, but it made a big difference in my case. Hope this helps you resolve the formatting problem!