Zenn
🗓️

Google Sheetに書いた日付で祝日対応

2020/12/12に公開
Google Apps Script
Google Sheets
tech

はじめに

Google Sheetで祝日対応をするのは簡単。
ただし、Googleさんが間違えていると、まちがえるので注意。

オリンピックにあたって祝日法案で変更がありました。
急な連絡でしたがGoogleさんは対応されてますね。

手順

  • Google スプレッドシートにカレンダーのベースを作る（土日がわかる程度）
  • GASでGoogle Calendarの祝日情報を取得してシートに書き込む

Google スプレッドシート

日付

日付を書いたセルを選択した状態で

セル選択して右下をドラッグするとダーっと日付は入力できます。

曜日

=TEXT(<日付を書いたセル>, "ddd")

背景色を曜日と祝日は設定

条件付き書式をつかいます。

条件付き書式設定ルールで
列だけ絶対参照（＄をつける）、行は相対参照（＄をつけない）
指定範囲は1000としました。

=$C2="土"

日曜日の表示も同様に設定します。
祝日は、祝日のタイトルをD列に記載してあれば祝日とします。

Google Calendar

Google Calendarの日本の祝日を使います。

追加していなければ、Googleアカウントのデフォルトのカレンダーに追加しておきます。
他のカレンダーから探して追加します。

Google App Scriptを書く

  • シートから取得した日付にはカラ行が入っている（多めに行数をとってるので）
  • カレンダーから取得する取得する祝日タイトルは、確認する日付分必要（500日のカレンダーなら500日取得）
//祝日カレンダー
const CALENDAR_ID = "ja.japanese#holiday@group.v.calendar.google.com";
//日付計算用の定数
const MILLIS_PER_DAY = 1000 * 60 * 60 * 24;

//編集するシート
const SHEET_ID ="<シートのID>";
const SHEET_NAME = "<シートの名前>";
const RANGE_A1 = "B2:B600";//日付記載の範囲：多めにしている。B2:Bだと1000行

//祝日カレンダーのEventを取得
function getCalendar(date) {
  const calendar = CalendarApp.getCalendarById(CALENDAR_ID);
  var now = date;
  var after500DaysFromNow = new Date(now.getTime() + MILLIS_PER_DAY * 500);
  var events = calendar.getEvents(now, after500DaysFromNow);
  return events;
}

function getSheetDate() {
  var sheet = SpreadsheetApp.openById(SHEET_ID);
  var editSheet = sheet.getSheetByName(SHEET_NAME);
  var rangeValues = editSheet.getRange(RANGE_A1).getValues();
  var sheetDates = new Array();
  for (var v of rangeValues){
    sheetDates.push(v[0]);
  }
  return sheetDates;
}

//シートに書き込む
function main() {
  var sheetDates = getSheetDate();    
  var sheet = SpreadsheetApp.openById(SHEET_ID);
  var editSheet = sheet.getSheetByName(SHEET_NAME);
  
  var now = new Date();
  var events = getCalendar(now);
  
  for(var event of events){
    var eventDate = event.getStartTime();
    var checkTimeEvent = eventDate.getTime();
    for (var i in sheetDates){
      var date = sheetDates[i];
      //カラ行は飛ばす
      if (date == ""){
        continue;
      }
      //日付の比較はgetTime()で行う
      if (date.getTime() == checkTimeEvent){
        var rowNo = Number(i) + 2;
        var rowNoA1 = "D" + rowNo;
        editSheet.getRange(rowNoA1).setValue(event.getTitle());
      }
    }
//    Logger.log(event.getStartTime());
//    Logger.log(event.getTitle());
  }
  
}

さいごに

適当に書いたなりに、迷うところが新鮮でした。

  • for...ofやfor...in
  • for...inで取得した値は数値じゃないので数値に戻す
  • 日付の比較を配列のindexOfを使おうとしたらDateの比較はできない

Discussion