google spread sheetに自動更新される日本の祝日シートを作る

googleスプレッドシートに、Google Apps Scriptを使って、定期的に自動更新される日本の祝日一覧を作成するスクリプトを作成しました。

経緯

発端は、スプレッドシートで管理されている予定表に、祝日の行のみを色分けしたいという要件が生まれたところから始まりました。
これ自体はカスタム数式で出来そうだなと思い、やり方を調べて見ると…

• 【スプレッドシート】別シートの国民の祝日一覧から判定してセルの背景色を変える : miyadir.com

なるほど、「国民の祝日一覧がシートにあれば出来そう…」

これはどこかで更新を忘れるやつだ…

手動でやる場合でも、どこかから祝日一覧を見つけて形式を整えて貼り付けるだけなので、難しくはないですがその都度手間がかかるので、自動化出来ないかと方法を探しました。

GAS

目的がスプレッドシートの自動更新なので、Google Apps Scriptを使うことにしました。
Google Apps Scriptであれば、Googleの各種サービスを簡単に操作することが出来るので、今回の用途的にちょうどいい感じです。

メンテされている日本の祝日一覧の取得

自動化しようと思った時に最初にネックになるのが、「定期的に更新されている日本の祝日一覧」を取得する方法でした。
国民の祝日について - 内閣府に国民の祝日がテーブル形式で記載されています。しかし、これをスクレイピングしようと思っても、その手間がかかる上にいつ書式が変わってもおかしくないので、かなり面倒です。

この対応として、googleカレンダーに「日本の祝日」カレンダーがあり、そのカレンダーを使用することにしました。
しかも、今回のようにGoogle Apps Scriptで行うのであれば、googleカレンダーへのアクセスは容易です。

• 参考: GoogleAppsScriptで日本の祝日を取得する - Qiita

完成品のソースコード

そして実際に作成したスクリプトが以下になります。
(実際に使用する際には、1行目のSHEET_URLの値を挿入したいスプレッドシートのURLに書き換えてください)

var SHEET_URL = "https://docs.google.com/spreadsheets/d/XXXXXXXXXX/";
var SHEET_NAME = "祝日一覧";

function holiday_main() {
  // 今年の1/1から
  var startDate = new Date();
  startDate.setMonth(0, 1);
  startDate.setHours(0, 0, 0, 0);

  // 来年の12/31まで
  var endDate = new Date();
  endDate.setFullYear(endDate.getFullYear() + 1, 11, 31);
  endDate.setHours(0, 0, 0, 0);  
  
  var sheet = getholidaysheet();
  var holidays = getHoliday(startDate, endDate);

  var lastRow = sheet.getLastRow();
  var startRow = 1;

  // シートが空白で無いとき、取得した祝日配列の先頭の日付と一致するカラムの位置を探索
  if (lastRow > 1) {
    var values = sheet.getRange(1, 1, lastRow, 1).getValues();
    for(var i = 0; i < lastRow; i++) {
      if(values[i][0].getTime() == holidays[0][0].getTime()) {
        break;
      }
      startRow++;
    }
  }

  sheet.getRange(startRow, 1, holidays.length, holidays[0].length).setValues(holidays);
}

/**
 * SHEET_URLで指定したドキュメント内のSHEET_NAMEのシートを取得する
 * SHEET_NAMEのシートが存在しない時は、シートを作成する
 */
function getholidaysheet(){
  var ss = SpreadsheetApp.openByUrl(SHEET_URL);
  var sheet = ss.getSheetByName(SHEET_NAME);

  if(sheet == null) {
    sheet = ss.insertSheet(SHEET_NAME);
  }
  
  return sheet;
}

/**
 * startDate〜endDateまでの祝日をgoogleカレンダーの「日本の祝日」から取得
 * [日付,祝日名]の多次元配列にした上で返す
 */
function getHoliday(startDate, endDate) {
  var cal = CalendarApp.getCalendarById("ja.japanese#[e-mail]");

  var holidays = cal.getEvents(startDate, endDate);
  var values = [];
  
  for(var i = 0; i < holidays.length; i++) {
    values[i] = [holidays[i].getStartTime(), holidays[i].getTitle()];
  }
  
  return values;
}

作成したら、実行 & プロジェクトのトリガーでmainを定期的に実行するようにすれば、
SHEET_URLに指定したシートに、自動更新される「祝日一覧」が追加されます。
(頻繁に更新されるものではないので、プロジェクトのトリガーは月1回程度で十分だと思います)

完成図

実際に実行すると、以下の様なシートが作成されます。

今回のスクリプトは、取得日以前の値を上書きしないように書いているので、以前のデータを削除しないまま、新しく登録された祝日をシートに追記してくれます。

祝日を使う

後は、シートに記載された祝日一覧を目的の用途に合わせて使っていくだけです。
今回であれば、条件付き書式のカスタム数式に以下のように指定します。

=COUNTIF(INDIRECT("祝日一覧!A:A"), $A1)>0

他にもgoogleスプレッドシートには、
指定した開始日からの就業日数を計算してくれるWORKDAYという関数があるので、これに使用すれば、特定の日付からn営業日後の日付を計算することができます。

=WORKDAY(TODAY(), A2, '祝日一覧'!A:A)

• WORKDAY - ドキュメント エディタ ヘルプ