AppsScriptPulse

Comment, Snippet, Beginner

Formatting date/time values using the user’s timezone in Google Apps Script

Tags:
| | 0 Comments |

Working with dates, times and time zones can often be a bit of a headache. If you’d like to learn more about some of the challenges of dealing with ‘big balls of wibbly-wobbly, timey-wimey… stuff’ I recommend watching Comptuerphile’s Problem with Time & Timezones.

For displaying dates/time values for users in different time zones you can use Utilities.formatDate(date, timeZone, format), the challenge however is working out the user’s timeZone. For Google Workspace Add-ons developers can configure the manifest option to include user locale information in event objects included as part of action callback functions [See Accessing user locale and timezone].

For web apps and editor add-ons using HTML Service it is possible to get the user’s browser time zone setting with the JavaScript code Intl.DateTimeFormat().resolvedOptions().timeZone (at time of writing this works in 93.85% of browsers). This can be passed back to your server script and used to format date/times for the user’s time zone. This forked gist provides an example of how this can be used in Google Sheets (props to Eric Koleda for the original solution, which uses .getTimezoneOffset() to get the users time zone offset).

The merits of Intl.DateTimeFormat() and .getTimezoneOffset() are discussed in Getting the client’s time zone (and offset) in JavaScript.

If using HTML Service is not an option an alternative workaround is getting the user’s Google Calendar time zone using the Calendar Service and .getTimeZone(). Here is an example script shared on Stack Overflow:

function getUserTimeZone() {
  var userTimeZone = CalendarApp.getDefaultCalendar().getTimeZone();
  Logger.log(userTimeZone)
}

The downside of this approach is it potentially adds an additional authorisation scope the user will need to approve before the script can run. As noted by Google best practice is always to limit the scopes in your Apps Script projects to the ones you need.

If you’ve got other tips for handling user time zones feel free to pop them in the comments.

Comment, Solutions, Calendar, V8, Beginner

Getting a Google Calendar event owner using Google Apps Script 

Tags:
| | 0 Comments |

In the Google Group Apps Script community there was an interesting question about returning a Google Calendar event owner/organiser. The problem was that using the Calendar Service it is possible to use getGuestList(includeOwner) to return the EventGuest[] array and in theory using getGuestStatus() find the guest with the OWNER GuestStatus Enum:

const event = CalendarApp.getEventById('some_event_id');
const guestList = event.getGuestList(true); // get the guest list including owner  
// Iterate across EventGuest[]
for (let i = 0; i < guestList.length; i++){
  let guest = guestList[i].getEmail();
  let status = guestList[i].getGuestStatus().toString();
  Logger.log(guest + ' ' + status);
}

However, in practice as the organiser status defaults to ‘Yes’ and can change to ‘No’ or ‘Maybe’ the OWNER status is never returned:

How to solve? Well one solution to find the owner is to get the event guest list with and without the owner then filter out the list ignoring any accounts that appear twice:

  const event = CalendarApp.getEventById('some_event_id);
  const guestList = event.getGuestList(true); // get the guest list including owner
  const guestListWithoutOwner = event.getGuestList(); // get the guest list without the owner

  // filter the guest list to ingore duplicate emails leaving only the owner
  // based on https://stackoverflow.com/a/42995029/1027723
  const owner = guestList.filter(o1 => !guestListWithoutOwner.some(o2 => o1.getEmail() === o2.getEmail()));

  Logger.log('The owner is: ' + owner[0].getEmail());

Alternatively if the Calendar Advanced Service is enabled in your Apps Script project the owner email can be returned using:

  // With Calendar Advanced Service enabled
  // Note: eventId is different to the one returned by CalendarApp as it doesn't include @google.com
  // See https://stackoverflow.com/a/55545091/1027723 for how to handle 
  const event = Calendar.Events.get(CalendarApp.getDefaultCalendar().getId(), 'some_event_id');
  Logger.log('The owner is: ' + event.organizer.email);

Clearly the second method is more direct, but which is quicker? I’ll let you tell me :)