0

I am following the conversation and tutorial in post Create Google Calendar Events from Spreadsheet but prevent duplicates

But I cannot get the final solution to work. It fills my calendar but all dates are 1/1/1970 at 3 am.

The code is as follows:

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

function parseDate(s) {
    var months = {
        jan: 0, feb: 1, mar: 2, apr: 3, may: 4, jun: 5, jul: 6, aug: 7, sep: 8, oct: 9, nov: 10, dec: 11
    };
    var p = s.replace(".", "").split('-');
    return new Date(p[2], p[1], p[0]);
}

/**
 * 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.getDisplayValues();
    var calId = "the calendar"; // PRODUCTION
    //var calId = "the calendar; // TEST
    var cal = CalendarApp.getCalendarById(calId);
    //Logger.log(cal);
    //Logger.log(data.length);
    for (i = 0; i<data.length; i++) {
        if (i<headerRows) continue; // Skip header row(s)
        if (data[i][0].length<1) continue; // Skip if no content.

        var row = data[i];
        Logger.log(row);
        var date = parseDate(row[0]); // First column
        //Logger.log(date);
        var title = row[1]; // Second column

        var tstart = new Date();
        var s = row[2].split(":");
        tstart.setHours(s[0]);
        tstart.setMinutes(s[1]);
        tstart.setSeconds(s[2]);
        tstart.setDate(date.getDate());
        tstart.setMonth(date.getMonth());
        tstart.setYear(date.getYear());

        var tstop = new Date();
        var e = row[3].split(":");
        tstop.setHours(e[0]);
        tstop.setMinutes(e[1]);
        tstop.setSeconds(e[2]);

        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
        var event = null;
        if (id.length > 0) {
            try {
                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();
            var r = i + 1;
            var cell = sheet.getRange("G" + r);
            cell.setValue(newEvent);
        } else {
            Logger.log(event);
            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;
    }
}

I think I must be making an obvious rookie mistake but cannot figure it out. Any chance of some help?

BTW: my dates are formatted: 12/01/2019 and times formatted: 18:00:00.

Adrian Mole
  • 49,934
  • 160
  • 51
  • 83
Omarp
  • 1
  • I forgot to mention. My colums are: Date| Title| Start Time| End Time| Location| Description| EventID|INFO| COMMENTS – Omarp Jan 06 '21 at 16:46
  • I think that when the value of `row[0]` is `12/01/2019`, `var p = s.replace(".", "").split('-');` is `12/01/2019`, and `new Date(p[2], p[1], p[0])` cannot parse it. So I cannot understand about your current situation. In order to correctly understand about your question, can you provide the sample Spreadsheet for replicating your issue? – Tanaike Jan 07 '21 at 02:15
  • Thanks, @Tanaike. By accident (yay me) I discovered the debugging tools and through trial and error, I discovered other problems. My date fields 1) were left justified 2) the wrong format -- I tried dd/mm/yyyy, dd-mmm-yyyy but found that yyyy-mm-dd worked 3) and I learned that getYear()routinely returned a year off by 1900 ( aysk bug??? no way!) i.e 2019 was returned as 0119. I fixed this by adding 1900 but this feels wrong and like a total hack bound to create issues down the road. https://docs.google.com/spreadsheets/d/1Ntwcmg7SiRZEB0THQz3KumxaHmtRJg_pHZwH54_OafU/edit?usp=sharing – Omarp Jan 07 '21 at 09:01
  • Just curious... why should visual formatting affect the way a script reads the data? Why, if the date is simply a human-readable version of time, should it matter what formatting order the date is stored in a spreadsheet? As long as it is a valid date I would have thought it would be stored as a serial number not a string, in which case visual formating would be totally irrelevant. – Omarp Jan 07 '21 at 11:18
  • Thank you for replying. I'm glad your issue was resolved. When your issue was resolved, can you post it as an answer? By this, it will be useful for other users who have the same issue. – Tanaike Jan 07 '21 at 12:26
  • Which programming language are you using? You will get more positive attention when you mention the language in the question and also add the appropriate language tag in addition to [date] and [calendar]. – Ole V.V. Jan 07 '21 at 22:42

0 Answers0