Blank Time displaying as 4:00 PM

[Alex P]

I'm using this script to import events to a calendar from a spreadsheet. the events import as whole days with times in the notes, which is perfect for what I need. It is working great, except whenever there's a blank time in the spreadsheet, for some reason it puts it in as 4:00 PM. I need to fix this because there are a number of people who access the calendar, and this is likely to cause confusion.

Here's the script:

function addEventsToCalendar() {

  var spreadsheet = SpreadsheetApp.getActiveSheet();

  var eventCal = CalendarApp.getCalendarById(calendarId);

  var rawEvents = spreadsheet.getRange(dataRange).getValues();

  var events = rawEvents.filter(function(r){

    return r.join("").length > 0;

  });

  deleteAutoCreatedEvents();

 

  for (var event of events) {

   

    var date = event[2];

    var name = event[3];

    var set_time = event[18];

    var address = event [5];

    var city = event [6];

    var state = event [7];

    var zip = event [8];

    var load_in = event [15];

    var accomodations = event [32];

    var notes = event [42];

    var parking = event [41];

    var tix = event [23];

    var status = event [1]

   

   var setTime = Utilities.formatDate (new Date(set_time),"PST", "hh:mm a")

   var loadTime = Utilities.formatDate (new Date(load_in), "PST", "hh:mm a")

    var lineBreak = "\r\n";

    var eventTitle = `${name}-${status} ${uniqueEventSuffix}`;

    var eventDescription = `Load In: ${loadTime} ${lineBreak}Set Time:${setTime} ${lineBreak}Notes:${lineBreak} ${accomodations}${lineBreak}${notes}${lineBreak}${parking}${lineBreak}Ticket link: ${tix}`;

    var where = ` ${address} ${city} ${state} ${zip}`

   

    var newEvent = eventCal.createAllDayEvent(eventTitle, new Date(date),

      {description: eventDescription,

      location: where

    });

    Logger.log(`Added ${eventTitle} on ${date} (${newEvent.getId()})`);

  }

}

What is going on?

[Emerson Maia Paula]

Good morning, see if this resolves the issue.

function addEventsToCalendar() {

  const spreadsheet = SpreadsheetApp.getActiveSheet();
  const eventCal = CalendarApp.getCalendarById(calendarId);
  const rawEvents = spreadsheet.getRange(dataRange).getValues();
  const events = rawEvents.filter(row => row.some(cell => cell.toString().trim() !== ""));

  deleteAutoCreatedEvents();

  for (const event of events) {
    const date = event[2];
    const name = event[3];
    const set_time = event[18] || "";
    const address = event[5];
    const city = event[6];
    const state = event[7];
    const zip = event[8];
    const load_in = event[15] || "";
    const accomodations = event[32] || "";
    const notes = event[42] || "";
    const parking = event[41] || "";
    const tix = event[23] || "";
    const status = event[1];

    const setTime = set_time ? Utilities.formatDate(new Date(set_time), "PST", "hh:mm a") : "Horário não definido";
    const loadTime = load_in ? Utilities.formatDate(new Date(load_in), "PST", "hh:mm a") : "Horário não definido";

    const lineBreak = "\r\n";
    const eventTitle = `${name} - ${status} ${uniqueEventSuffix}`;
    const eventDescription = `Load In: ${loadTime} ${lineBreak}Set Time: ${setTime} ${lineBreak}Notes: ${lineBreak}${accomodations}${lineBreak}${notes}${lineBreak}${parking}${lineBreak}Ticket link: ${tix}`;
    const where = `${address} ${city} ${state} ${zip}`.trim();

    const newEvent = eventCal.createAllDayEvent(eventTitle, new Date(date), {

      description: eventDescription,
      location: where
    });

    console.log(`Added ${eventTitle} on ${date} (${newEvent.getId()})`);
  }
}


In JavaScript, when a value can be falsy (such as null, undefined, or an empty string ""), we can use the logical "or" operator (||) to set a default value.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/2c6ce2ab-ea34-4ab7-9786-5fb32139b59cn%40googlegroups.com.

[wesley chun]

As for the cause, it seems to me that an "undefined" time is really a time of "0", meaning 0:00 GMT, and for those in PST/GMT-8, that would be 1600 or 4 PM the day before. That's my guess as to why you're getting that. Hopefully Emerson's fix suffices.

Cheers,

--Wesley

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
"A computer never does what you want... only what you tell it."
    Wesley Chun :: @wescpy (Tw, BS) :: Python, GCP & GWS & GMP

    Principal Consultant at CyberWeb; author of Core Python

    Google Developer Expert (GDE) in Cloud/GCP & Workspace/GWS

    Google Python/NodeJS dev blog; Python App Engine migration help

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CADH-QrAiLhy4dvUUxPFb%2BNuXGHr%2BC3SEaLMqArimhEZ3vcV2KA%40mail.gmail.com.

[Alex P]

Thanks for this, but unfortunately this version still does the same thing. I tried taking this wholesale, and going into what I had and adding in the OR options as well, but that didn't solve it either.