Check if date is a holiday in Google Sheets

Question

I have borrowed/modified the below script from the following website. This script didn't work in its original form either... syntactically it should be working, but for some reason the Date() function keeps failing...

http://qiita.com/kamatama_41/items/be40e05524530920a9d9

My issue is that it doesn't work in google spreadsheets.. I am having the following to main issues.

• Can't get the endDate set properly, it always turns out as in the below log.

• It isn't recognized as an custom function in sheets. i.e =isJapaneseHolidy(somedate) isn't recognized.

Log:

[17-04-28 12:34:19:357 JST] Starting execution
[17-04-28 12:34:19:364 JST] Logger.log([Thu Jan 01 09:00:00 GMT+09:00 1970, []]) [0 seconds]
[17-04-28 12:34:19:364 JST] Logger.log([Thu Jan 01 09:00:00 GMT+09:00 1970, []]) [0 seconds]
[17-04-28 12:34:19:401 JST] CalendarApp.getCalendarById([ja.japanese#[email protected]]) [0.035 seconds]
[17-04-28 12:34:19:424 JST] Calendar.getEvents([Wed Dec 31 16:00:00 PST 1969, Wed Dec 31 16:00:00 PST 1969]) [0.022 seconds]
[17-04-28 12:34:19:425 JST] Execution succeeded [0.062 seconds total runtime]

Function:

function main() {
  Logger.log(isJapaneseHoliday(2017,5,3)); 
}

function isJapaneseHoliday(year, month, day) {   
  var startDate = new Date(year,month-1,day,0,0,0);
  Logger.log(startDate);
  var endDate = new Date(year,month-1,day,23,59,59,999);
  Logger.log(endDate);

  var cal=CalendarApp.getCalendarById("ja.japanese#[email protected]");
  var holidays =  cal.getEvents(startDate, endDate);

  return holidays.length != 0; 
}

Has anyone experienced this before?

EDIT:

If I use values instead of variables for the date, I get a valid return.

Please see the first Logger.log output below where I used var startDate = new Date(2012,12,12,0,0,0,0) to test.

[17-04-28 13:01:42:098 JST] Starting execution
[17-04-28 13:01:42:107 JST] Logger.log([Wed Dec 12 00:00:00 GMT+09:00 2012, []]) [0 seconds]
[17-04-28 13:01:42:108 JST] Logger.log([Thu Jan 01 09:00:00 GMT+09:00 1970, []]) [0 seconds]
[17-04-28 13:01:42:141 JST] CalendarApp.getCalendarById([ja.japanese#[email protected]]) [0.032 seconds]
[17-04-28 13:01:42:142 JST] Calendar.getEvents([Tue Dec 11 07:00:00 PST 2012, Wed Dec 31 16:00:00 PST 1969]) [0 seconds]
[17-04-28 13:01:42:146 JST] Execution failed: Event start time must be before event end time. (line 12, file "Code") [0.038 seconds total runtime]

EDIT2:

As Jack Brown mentioned, I needed to run the main() function in order to get this working properly... I was executing the isJapaneseHoliday funtion. which doesn't have the inputs by default.

Additionally It was mentioned that GoogleSheets and GoogleCalendar do not allow access for each other... there must be a way...

EDIT3:

Jack Brown answered my question, I am now facing different issues, but due to the inability to authenticate, I am creating a custom menu and adding the functionality to that.

https://developers.google.com/apps-script/guides/menus

However, for those that are trying to use public calendars in your scripts, be aware, that apparently they (or at least the Japanese Holiday calendar) take input in PST/PDT and it makes the output appear incorrect... I have yet to know if it affects the results.

EDIT4:

I just verified, and it appears to not affect the results, it just shows the time for the PST/PDT timezone as input to the getEvents() function the output appears to work properly.

Answer 1

Instead of using cal.getEvents(startDate, endDate), you may try using cal.getEventsForDay(date). The codes could be revised as:

function main() {
  Logger.log(isJapaneseHoliday(2017,5,3)); 
}

function isJapaneseHoliday(year, month, day) {   
  var date = new Date(year,month-1,day);
  Logger.log(date);

  var cal=CalendarApp.getCalendarById("ja.japanese#[email protected]");
  var holidays =  cal.getEventsForDay(date);

  return holidays.length != 0; 
}