title: Check if date is a holiday in Google Sheets
body: <p>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...</p>
  
  <p><a href="http://qiita.com/kamatama_41/items/be40e05524530920a9d9" rel="nofollow noreferrer">http://qiita.com/kamatama_41/items/be40e05524530920a9d9</a></p>
  
  <p>My issue is that it doesn't work in google spreadsheets.. I am having the following to main issues.</p>
  
  <ul>
  <li><p>Can't get the <code>endDate</code> set properly, it always turns out as in the below log.</p></li>
  <li><p>It isn't recognized as an custom function in sheets. i.e =isJapaneseHolidy(somedate) isn't recognized.</p></li>
  </ul>
  
  <p><strong>Log:</strong></p>
  
  <pre><code>[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#holiday@group.v.calendar.google.com]) [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]
  </code></pre>
  
  <p><strong>Function:</strong></p>
  
  <pre><code>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#holiday@group.v.calendar.google.com");
    var holidays =  cal.getEvents(startDate, endDate);
  
    return holidays.length != 0; 
  }
  </code></pre>
  
  <p>Has anyone experienced this before?</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>If I use values instead of variables for the date, I get a valid return.</p>
  
  <p>Please see the first Logger.log output below where I used <code>var startDate = new Date(2012,12,12,0,0,0,0)</code> to test.</p>
  
  <pre><code>[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#holiday@group.v.calendar.google.com]) [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]
  </code></pre>
  
  <p><strong>EDIT2:</strong></p>
  
  <p>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.</p>
  
  <p>Additionally It was mentioned that GoogleSheets and GoogleCalendar do not allow access for each other... there must be a way... </p>
  
  <p><strong>EDIT3:</strong> </p>
  
  <p>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.</p>
  
  <p><a href="https://developers.google.com/apps-script/guides/menus" rel="nofollow noreferrer">https://developers.google.com/apps-script/guides/menus</a></p>
  
  <p>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.</p>
  
  <p><strong>EDIT4:</strong></p>
  
  <p>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 <code>getEvents()</code> function the output appears to work properly.</p>