Multi-day calendar events in Google Apps Script

You can create events using the Calendar Class in Google Apps Script. However, only for regular (not all day  events) you can set both the start and the end time. For all day events, you can only set one date, so they will always be one day only. Then how can you create all day events?

There are a few workarounds:

  • Create a regular event and set the start and end time to midnight; remember to specify midnight of the day after the end day. The drawback is the event fills the whole space for a day in your calendar overview, rather then just a line at the top as with all day events.
  • Create a all day event series, so you have a sequence of one-day all day events; this looks already a bit nicer, but in the calendar view, from a single event yio can not see how long it last.
  • Create an event from a description, this creates real multi-day all day events.

So creating an event from a description seems the best way to go. This is worked out in a new function, createMultiDayEvent( calendar, title, startDate, endDate ).

The Google Apps Script method Calendar createEventFromDescription(description) creates an event from a free-from description. The description should use the same format as the UI’s “Quick Add” feature. So you can not directly use a JavaScript Date object, but have to convert this to a string first. There are two possible issues here:

  • The string should be formulated in an unambiguous way, e.g. 3/4/2013 could mean 3 April or March 4
  • The time zone should be chosen correctly–not sure whether this is a real issue, as we look at the day only, but choosing the wrong time zone might shift it one day, so better safe than sorry

Below is the function that should fix these issues and create a multi-day AllDayEvent with a given title, start and end date:

function createMultiDayEvent( calendar, title, startDate, endDate ) {
 var timeZone = calendar.getTimeZone();
 var description = Utilities.formatString( '%s from %s to %s', title, dateString_( startDate, timeZone ), dateString_( endDate, timeZone ));
 return calendar.createEventFromDescription(description);
}

function dateString_( date, timeZone ) {
 // format like 21 Apr 2013
 var format = ' dd MMM yyyy';
 var str = Utilities.formatDate(date, timeZone, format);
 return str;
}

An issue that I have not solved yet, is how to change the end date, aside from replacing it with a new event. Anyone who can share a solution?

8 thoughts on “Multi-day calendar events in Google Apps Script

  1. Martin

    Hi Harry,
    thanks for your post.
    I tried your function but unfortunately it did not work for me on Chrome (German language settings). When I use the format “23 May 2013 – 24 May 2013” I get an event like this: Fri, 24 May, 21:13 – Sat, 25 May, 01:00 with the title “23 May May Test-Title”.

    The only format that creates a multi all-day event in my case is the following:
    var description = Utilities.formatString( ‘%s – %s %s’, startDate, endDate, title);
    with startDate/endDate being “DD/MM/YYYY” or “DD/MM/YY” (“24/05/2013” or “3/05/2013”. “03/05/2013” does not work because of the leading 0).
    However when I asked a friend of mine to run the exact same script Google calendar converts “3/05/2013” into March 5th and not into May 3rd like in my case.

    First I thought the reason would be different country and/or language settings in Google calendar but then I switched my settings to English and it still worked for me. Do you have an idea why it is working differently for him?

    Cheers,
    Martin

    Reply
    1. Harry Post author

      Hi Martin,

      Thanks for this feedback; I had not actually tested it with different languages, and there are some remarkable inconsistencies. As described in this article, it works for English languages (both UK and US), but indeed, not for German (or Dutch, and probably more languages).

      I tested this by changing the language on the Google Account settings page. After changing the language, the interfaces of both the calendar and the script editor change, but the formatted dates remain in English–which apparently is not recognized by the other languages. I used this format with the abbreviated month to avoid the confusion for 3/5: is it 5 March and 3 May?

      From my testing:

      • For English language, the function as described works.
      • For German and French, indicating the month as a number, without leading zeros, works.
      • For Dutch and Spanish, I have not found a format that works 🙁

      So, you may have to experiment and adapt the format for your language.

      Reply
      1. Martin

        Hi Harry,

        you are right. It depends on the language of your Google Account.

        For me the following worked:
        ENGLISH:
        Quick add format:(s):
        “startDate – endDate Title” and “title from startDate to endDate”

        Date formats:
        30 May 2013 – working –> best option
        30-May-2013 – not working
        30/05/2013 – working (but might cause problems as 03/05 sometimes can be interpreted as May 3rd or Mar 5th)

        GERMAN:
        Quick add format:(s): only this works “startDate – endDate Title”

        Date formats:
        30 May 2013 – not working
        30-May-2013 – not working
        30/05/2013 – working (however not with leading 0s, e.g. 03/05/2013 has to be 3/05/2013)

        Hence for German users the best way to use Quick add in Google Apps Script would be “startDate – endDate Title” with the date format DD/MM/YYYY (all numbers and without leading 0s).

        Reply
  2. Gauwain

    Not sure how useful this is, but I managed to fix a similar problem with Drive’s translate function. If you’re reading the date from a cell, why not have Spreadsheet translate that cell into a language that does work?

    =GoogleTranslate(text; source language; target language)

    Would that be any help?

    Kind regards,
    Gauwain

    Reply
  3. Tim

    I’m getting errors trying to run this? I found I needed to add a load of variables to get past the first function, but now stuck on the second one.

    How am i supposed to use this?

    Sorry, newb to apps scripts 🙁

    Tim

    Reply
    1. Tim

      It’s OK I figures it out. In fact this method allows the entry of single events to a calendar and probably recurring events too 🙂

      Reply
  4. Mark

    Fantastic Harry! Thanks so much for this. I had about pulled out my hair trying to figure this out!

    Reply

Leave a Reply

Your email address will not be published.