I try to use a script from Create Google Calendar Events from Spreadsheet but prevent duplicates, however I can't make it work. It seems to me that there is a problem with the date format, though I use the standard one in my spreadsheet.

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Export Events",
    functionName : "exportEvents"
  }];
  sheet.addMenu("Calendar Actions", entries);
};

/**
 * Export events from spreadsheet to calendar
 */
function exportEvents() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var headerRows = 1;  // Number of rows of header info (to skip)
  var range = sheet.getDataRange();
  var data = range.getValues();
  var calId = "YOUR_CALENDAR_ID";
  var cal = CalendarApp.getCalendarById(calId);
  for (i=0; i<data.length; i  ) {
    if (i < headerRows) continue; // Skip header row(s)
    var row = data[i];
    var date = new Date(row[0]);  // First column
    var title = row[1];           // Second column
    var tstart = new Date(row[2]);
    tstart.setDate(date.getDate());
    tstart.setMonth(date.getMonth());
    tstart.setYear(date.getYear());
    var tstop = new Date(row[3]);
    tstop.setDate(date.getDate());
    tstop.setMonth(date.getMonth());
    tstop.setYear(date.getYear());
    var loc = row[4];
    var desc = row[5];
    var id = row[6];              // Sixth column == eventId
    // Check if event already exists, update it if it does
    try {
      var event = cal.getEventSeriesById(id);
    }
    catch (e) {
      // do nothing - we just want to avoid the exception when event doesn't exist
    }
    if (!event) {
      //cal.createEvent(title, new Date("March 3, 2010 08:00:00"), new Date("March 3, 2010 09:00:00"), {description:desc,location:loc});
      var newEvent = cal.createEvent(title, tstart, tstop, {description:desc,location:loc}).getId();
      row[6] = newEvent;  // Update the data array with event ID
    }
    else {
      event.setTitle(title);
      event.setDescription(desc);
      event.setLocation(loc);
      // event.setTime(tstart, tstop); // cannot setTime on eventSeries.
      // ... but we CAN set recurrence!
      var recurrence = CalendarApp.newRecurrence().addDailyRule().times(1);
      event.setRecurrence(recurrence, tstart, tstop);
    }
    debugger;
  }
  // Record all event IDs to spreadsheet
  range.setValues(data);
}

Please, could you have a look what is wrong (my spreadsheet: https://docs.google.com/spreadsheets/d/1OIZLOjG5cg32EbV-bI5c2qDWtbhbggDTTobPK_dYfyQ/edit?usp=sharing). The script works but there is no result in my Google Calendar.

CodePudding user response：

I thought that when I saw your showing script, date.getYear() might be required to be modified to date.getFullYear(). I thought that this might be the reason for your current issue of The script works but there is no result in my Google Calendar..

And, in your script, var date = new Date(row[0]), var tstart = new Date(row[2]) and var tstop = new Date(row[3]) might be written as var date = row[0], var tstart = row[2] and var tstop = row[3]. Because the cell values are the date object. So, how about the following modification?

From:

var date = new Date(row[0]);  // First column
var title = row[1];           // Second column
var tstart = new Date(row[2]);
tstart.setDate(date.getDate());
tstart.setMonth(date.getMonth());
tstart.setYear(date.getYear());
var tstop = new Date(row[3]);
tstop.setDate(date.getDate());
tstop.setMonth(date.getMonth());
tstop.setYear(date.getYear());

To:

var date = row[0];
var title = row[1];
var tstart = row[2];
tstart.setDate(date.getDate());
tstart.setMonth(date.getMonth());
tstart.setYear(date.getFullYear());
var tstop = row[3];
tstop.setDate(date.getDate());
tstop.setMonth(date.getMonth());
tstop.setYear(date.getFullYear());

Note:

• In the case of var event = cal.getEventSeriesById(id);, I think that even when id is empty and the invalid value, no error occurs. So, try - catch might not be required to be used.

Reference:

• getFullYear()