Googleスプレッドシートで作成したカレンダーに祝日情報を加える｜Google Apps Script(GAS)

Googleスプレッドシートで作成したカレンダーに祝日情報を付加するにはどうしたら良いのでしょう。

Googleスプレッドシート自体には祝日情報を取得する機能は有りませんが、Google Apps Script(GAS)を使えば同じGoogleのサービス「Googleカレンダー」から祝日データを取得しGoogleスプレッドシートで利用する事ができます。

この記事では以前Googleスプレッドシートで作成したカレンダーに祝日情報を付加する方法を紹介するので、Googleスプレッドシートでカレンダーを作成する方法を知りたい方は先にコチラの記事を読んでみてください。

[Googleスプレッドシート]ずっと使えるカレンダーを一瞬で作成する方法

Googleスプレッドシートでカレンダーを作成する方法を紹介します。「表計算ソフトでカレンダーを作成する方法」を検索すると沢山の記事が見つかるのですが、その多くが各セルに数式を記述する方法で「なんか面倒だな」と思い記事に […]

記事ではGoogle Apps Script(GAS)で以下の事をする方法を中心に解説します。

Googleカレンダーの祝日データの利用方法
Googleスプレッドシートの指定した範囲の値を取得する方法
カレンダーの年・月の値を変えると祝日情報も変更される

Google Apps Script(GAS)でGoogleスプレッドシートを操作したい方にとって少しでも参考になればと思います。

[Google Apps Script]Googleスプレッドシートをスクリプト操作してみた[初心者]

INDEX

Googleスプレッドシートのカレンダーに祝日情報を付加するコード
 -カレンダーに祝日情報を付加するコード
Googleカレンダーの祝日データを取得
Googleスプレッドシートの指定した範囲の値を取得する方法
作成したカレンダーの祝日日の背景色を変更
 -祝日情報をセルに入力
カレンダーの年・月の値を変えると祝日情報も変更される
 -カレンダーの年・月の値を変えるとスクリプト実行

Googleスプレッドシートのカレンダーに祝日情報を付加するコード

祝日情報を付加するGoogleスプレッドシートで作成したカレンダー。

以前作成したカレンダーでは最後にTODAY関数で自動更新させましたが、今回使用するカレンダーはその機能を外しています。

[Googleスプレッドシート]ずっと使えるカレンダーを一瞬で作成する方法

Googleスプレッドシートでカレンダーを作成する方法を紹介します。「表計算ソフトでカレンダーを作成する方法」を検索すると沢山の記事が見つかるのですが、その多くが各セルに数式を記述する方法で「なんか面倒だな」と思い記事 […]

カレンダーに祝日情報を付加するコード

Googleスプレッドシートのメニューバーから　ツール > スクリプトエディタ　を選択。

Google Apps Script（GAS）を編集実行するスクリプトエディタを開いて変換処理のスクリプトを作成します。

早速、Googleカレンダーの祝日データを取得して、Googleスプレッドシートで作成したカレンダーに祝日情報を付加するGoogle Apps Scriptのコードを載せました。

function holiday_Function() { 

  //年のセルの位置
  const cell_year_row_No = 1; 
  const cell_year_column_No = 1; 

  //月のセルの位置
  const cell_maonth_row_No = 1; 
  const cell_maonth_column_No = 3; 

  //アクティブなシートを取得 
  let active_sheet = SpreadsheetApp.getActiveSheet(); 
  //アクティブなセルを取得 
  const activecell = active_sheet.getActiveCell(); 

  //年もしくは月のセルが更新されたのを確認 
  let active_value = true; 

  if(activecell.getColumn() == cell_year_column_No && activecell.getRow() == cell_year_row_No){ 

    active_value = true; 

  }else if(activecell.getColumn() == cell_maonth_column_No && activecell.getRow() == cell_maonth_row_No){ 

    active_value = true; 

  }else{ 

    active_value = false; 

  } 


　//年もしくは月のセルが更新されていたら実行 
  if(active_value == true){ 

    //Googleカレンダーの祝日情報のID 
    const calendar_jpn_holiday_Id = "ja.japanese#holiday@group.v.calendar.google.com"; 
    //Googleカレンダーの祝日情報を取得 
    const calendar_jpn_holiday_Data = CalendarApp.getCalendarById(calendar_jpn_holiday_Id); 

    //月の値（何月）を取得 
    let cellValue_manth = active_sheet.getRange(1, 3).getValue(); 
    //祝日情報を格納する連想配列（日付:何の祝日） 
    let holiday_dic = {}; 

    //カレンダーの範囲の値（日付）を配列で取得 
    let cellValues_calendar_data = active_sheet.getRange(3,1,6,7).getValues(); 

    for(let row_i = 0 ; row_i < 6 ; row_i++){ 

      for(let col_i = 0; col_i < 7; col_i++){ 

        //指定した日付の祝日情報を取得 
        let calendarEvent_arry = calendar_jpn_holiday_Data.getEventsForDay(cellValues_calendar_data[row_i][col_i]); 
        let cell_range = active_sheet.getRange(row_i + 3, col_i + 1) 

        //指定した日付に祝日情報があるのかを確認 
        if(calendarEvent_arry[0] == "CalendarEvent"){ 

          //指定した日付に祝日情報が有り、尚且つ当月であることを確認 
          if(parseInt(cellValues_calendar_data[row_i][col_i].getMonth() , 10) + 1 == parseInt(cellValue_manth, 10)){ 

            //セルの背景を赤く塗りつぶす 
            cell_range.setBackground("#e57171"); 
             
            //祝日情報の連想配列に（日付:何の祝日）を追加 
            holiday_dic[cellValues_calendar_data[row_i][col_i].toLocaleString("ja")] = calendarEvent_arry[0].getTitle(); 

          } 
        } 
        else{ 

          //指定した日付に祝日情報が無ければセルの背景をグレーで塗りつぶす 
          cell_range.setBackground("#f3f3f3"); 

      } 
    } 
  } 
    //祝日情報の入力を開始するセルの行番号 
    const holiday_str_row = 11; 

    //セルを空白にする 
    for(let range_i = 0 ; range_i <= 5; range_i++){ 

      active_sheet.getRange(holiday_str_row + range_i , 1).setValue(""); 
      active_sheet.getRange(holiday_str_row + range_i , 2).setValue(""); 

    } 

    //祝日情報の連想配列からキーを取得 
    let key = Object.keys(holiday_dic); 

    for( let key_i = 0 ; key_i < Object.keys(holiday_dic).length ; key_i++){ 

      //祝日の日付をセルに入力 
      active_sheet.getRange(holiday_str_row + key_i , 1).setValue(key[key_i]); 
      //日付の書式を[月/日]に変換 
      active_sheet.getRange(holiday_str_row + key_i , 1).setNumberFormat('M/d'); 
      //祝日情報をセルに入力 
      active_sheet.getRange(holiday_str_row + key_i , 2).setValue(holiday_dic[key[key_i]]); 

    } 
  } 
}

スクリプトが実行されるとまず、どのセルが更新されたのかをセルの位置を取得。

年もしくは月のセルが更新されたら祝日情報を付加するコードを実行。

//年もしくは月のセルが更新されたのを確認 
  let active_value = true; 

  if(activecell.getColumn() == cell_year_column_No && activecell.getRow() == cell_year_row_No){ 

    active_value = true; 

  }else if(activecell.getColumn() == cell_maonth_column_No && activecell.getRow() == cell_maonth_row_No){ 

    active_value = true; 

  }else{ 

    active_value = false; 

  } 


　//年もしくは月のセルが更新されていたら実行 
  if(active_value == true){

Googleカレンダーの祝日データを取得

Googleカレンダーで取得できる日本の祝日データのIDは“ja.japanese#holiday@group.v.calendar.google.com”です。

このIDをCalendarApp.getCalendarById()で指定して、祝日情報を取得します。

    //Googleカレンダーの祝日情報のID  
    const calendar_jpn_holiday_Id = "ja.japanese#holiday@group.v.calendar.google.com";  
    //Googleカレンダーの祝日情報を取得  
    const calendar_jpn_holiday_Data = CalendarApp.getCalendarById(calendar_jpn_holiday_Id);

Googleスプレッドシートの指定した範囲の値を取得する方法

Googleスプレッドシートで作成したカレンダーの範囲を、getRange().getValues()で指定し値を配列で取得します。

    //カレンダーの範囲の値（日付）を配列で取得  
    let cellValues_calendar_data = active_sheet.getRange(3,1,6,7).getValues();

getRange(3,1,6,7)　で範囲を設定する場合は、左上の行・列から右下の行・列の順番で設定します。

getRange(3,1,6,7)で取得できる日付の値は、行毎つまり週毎にまとまった配列で取得されます。

[[Sun Jun 27 00:00:00 GMT+09:00 2021, Mon Jun 28 00:00:00 GMT+09:00 2021, Tue Jun 29 00:00:00 GMT+09:00 2021, Wed Jun 30 00:00:00 GMT+09:00 2021, Thu Jul 01 00:00:00 GMT+09:00 2021, Fri Jul 02 00:00:00 GMT+09:00 2021, Sat Jul 03 00:00:00 GMT+09:00 2021], 
[Sun Jul 04 00:00:00 GMT+09:00 2021, Mon Jul 05 00:00:00 GMT+09:00 2021, Tue Jul 06 00:00:00 GMT+09:00 2021, Wed Jul 07 00:00:00 GMT+09:00 2021, Thu Jul 08 00:00:00 GMT+09:00 2021, Fri Jul 09 00:00:00 GMT+09:00 2021, Sat Jul 10 00:00:00 GMT+09:00 2021], 
[Sun Jul 11 00:00:00 GMT+09:00 2021, Mon Jul 12 00:00:00 GMT+09:00 2021, Tue Jul 13 00:00:00 GMT+09:00 2021, Wed Jul 14 00:00:00 GMT+09:00 2021, Thu Jul 15 00:00:00 GMT+09:00 2021, Fri Jul 16 00:00:00 GMT+09:00 2021, Sat Jul 17 00:00:00 GMT+09:00 2021], 
[Sun Jul 18 00:00:00 GMT+09:00 2021, Mon Jul 19 00:00:00 GMT+09:00 2021, Tue Jul 20 00:00:00 GMT+09:00 2021, Wed Jul 21 00:00:00 GMT+09:00 2021, Thu Jul 22 00:00:00 GMT+09:00 2021, Fri Jul 23 00:00:00 GMT+09:00 2021, Sat Jul 24 00:00:00 GMT+09:00 2021], 
[Sun Jul 25 00:00:00 GMT+09:00 2021, Mon Jul 26 00:00:00 GMT+09:00 2021, Tue Jul 27 00:00:00 GMT+09:00 2021, Wed Jul 28 00:00:00 GMT+09:00 2021, Thu Jul 29 00:00:00 GMT+09:00 2021, Fri Jul 30 00:00:00 GMT+09:00 2021, Sat Jul 31 00:00:00 GMT+09:00 2021], 
[Sun Aug 01 00:00:00 GMT+09:00 2021, Mon Aug 02 00:00:00 GMT+09:00 2021, Tue Aug 03 00:00:00 GMT+09:00 2021, Wed Aug 04 00:00:00 GMT+09:00 2021, Thu Aug 05 00:00:00 GMT+09:00 2021, Fri Aug 06 00:00:00 GMT+09:00 2021, Sat Aug 07 00:00:00 GMT+09:00 2021]]

作成したカレンダーの祝日日の背景色を変更

配列で取得した祝日データをfor文で取り出し、祝日”CalendarEvent”の情報があるかを確認。

祝日情報があればセルの背景を指定した祝日のカラーで塗りつぶし、祝日情報がなければ平日のカラーで塗りつぶす。

加えて、日付に対応する祝日名（日付:何の祝日）を連想配列に追加する。

for(let row_i = 0 ; row_i < 6 ; row_i++){ 

      for(let col_i = 0; col_i < 7; col_i++){

        //指定した日付の祝日情報を取得  
        let calendarEvent_arry = calendar_jpn_holiday_Data.getEventsForDay(cellValues_calendar_data[row_i][col_i]);  
        let cell_range = active_sheet.getRange(row_i + 3, col_i + 1)  

        //指定した日付に祝日情報があるのかを確認  
        if(calendarEvent_arry[0] == "CalendarEvent"){  

          //指定した日付に祝日情報が有り、尚且つ当月であることを確認  
          if(parseInt(cellValues_calendar_data[row_i][col_i].getMonth() , 10) + 1 == parseInt(cellValue_manth, 10)){  

            //セルの背景を赤く塗りつぶす  
            cell_range.setBackground("#e57171");  
              
            //祝日情報の連想配列に（日付:何の祝日）を追加  
            holiday_dic[cellValues_calendar_data[row_i][col_i].toLocaleString("ja")] = calendarEvent_arry[0].getTitle();  
          }  
        }  
        else{  

          //指定した日付に祝日情報が無ければセルの背景をグレーで塗りつぶす  
          cell_range.setBackground("#f3f3f3");  
      }

祝日情報をセルに入力

祝日情報（日付:何の祝日）の連想配列をfor文で取り出し、日付と祝日名をセルに入力します。

//祝日情報の入力を開始するセルの行番号  
    const holiday_str_row = 11;  

    //セルを空白にする  
    for(let range_i = 0 ; range_i <= 5; range_i++){  

      active_sheet.getRange(holiday_str_row + range_i , 1).setValue("");  
      active_sheet.getRange(holiday_str_row + range_i , 2).setValue("");  

    }  


    //祝日情報の連想配列からキーを取得  
    let key = Object.keys(holiday_dic);  

    for( let key_i = 0 ; key_i < Object.keys(holiday_dic).length ; key_i++){  

      //祝日の日付をセルに入力  
      active_sheet.getRange(holiday_str_row + key_i , 1).setValue(key[key_i]);  
      //日付の書式を[月/日]に変換  
      active_sheet.getRange(holiday_str_row + key_i , 1).setNumberFormat('M/d');  
      //祝日情報をセルに入力  
      active_sheet.getRange(holiday_str_row + key_i , 2).setValue(holiday_dic[key[key_i]]);  

    }

カレンダーの年・月の値を変えると祝日情報も変更される

新たに作成したスクリプトを保存するとアクセス権の承認が求められます。

「このアプリはGoogleで確認されていません」が表示されたら「詳細」をクリック

「無題のプロジェクト（安全ではないページ）に移動」をクリック

「許可」をクリックして承認します。

カレンダーの年・月の値を変えるとスクリプト実行

カレンダーの年または月の値を変更したら作成したスクリプトが実行される様にトリガーを設定します。

スクリプトエディターの左側のメニューから「トリガー」を選択

「トリガーを追加」をクリック

実行する関数を選択で、作成したスクリプトを選択、イベントのソースを選択で「スプレッドシートから」を、イベントの種類を選択で「変更時」を設定し「保存」します。

Googleスプレッドシートで作成したカレンダーに戻り、年または月の値を変更。

祝日日のセルの背景色が設定したカラーで塗りつぶされ、祝日情報（日付、祝日名）が表示されたら成功です。

転生したらスプレッドシートだった件

はじめてのGoogle スプレッドシートの教科書2020 Google アプリの教科書シリーズ2020年版

ブラウザだけで学べる Googleスプレッドシートプログラミング入門

【Amazon.co.jp 限定】Google Chromebook HP ノートパソコンインテル® Core i3 8GB 128GB eMMC 14インチブライトビュー・IPSタッチディスプレイ 2in1 コンバーチブルタイプ日本語キーボード HP

【UdemyのおすすめGoogleスプレッドシート学習コース】

ネットショッピングをする感覚で講座を購入し、収録された動画を視聴するスタイルなので自分のペースで学習できるUdemy

そのUdemyにはGoogleスプレッドシートが学べる講座もあります。 Googleスプレッドシートの基本から一歩踏み込んだところまでを学べる評価の高い講座です。スプレッドシート以外の表計算ソフトでも使えるテクニックが習得できるので、コスパも高く実務ですぐに使えます。

Udemy年内最安値のセール開催中！

通常価格の90%の割引率で最低価格が1,200円と、今年最大のお得なセールです！

■ ブラックフライデーセール
　日程：11/19(金) - 11/26(金)

■ サイバーセール
　日程：11/29(月) - 12/1(水)

ぜひこの機会をお見逃しなく >>Udemy