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

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スプレッドシートで作成したカレンダーに戻り、年または月の値を変更。

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

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

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

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

【IT初心者でもわかる！Google スプレッドシート入門編】基本機能を活用したワンランク上の使い方、情報処理をマスター

Google スプレッドシートを用いて表計算ソフトの一般的な機能を体験するコースです。四則演算や絶対参照、キーボードショートカットからはじまり、関数SUM / IF / VLOOKUP や、フィルタ、並べ替え、ピボット、グラフまでを体験

【業務自動化をゼロからマスター】Google Apps Scriptの仕事効率化・自動化をプログラミングで実現

Google Apps Script(GAS)をマスターして毎日の業務(gmail、google スプレッドシート)を劇的に自動化させよう。

CGデザイナーにオススメする学ぶべきプログラミング言語は？

近年プログラミング学習の需要が高くなっています。 CGデザインの業務に携わる現場でも、その需要は益々高くなりプログラミングが分かる人と分からない人で、今後大きな開きが出てくると思われます。そしてプログラミングが学べるプ […]

毎月第1日曜日は「スカパー！無料の日」どんなチャンネルが見れるのか紹介

自宅時間が増えた人も多いと思いますが、そうなると当然、部屋での過ごし方も充実させたくなります。スポーツ専門のDAZN、ドコモのdTV 、そしてAmazon Prime Videoなど、最近はネット動画配信サービスが充実 […]

BTOパソコンの草分け的ブランド【FRONTIER】

教育機関や研究施設/3DCG/動画編集/そしてゲームプレイにも、用途に合わせたオーダーメイド高性能PCがお得な価格で購入できます

▶最新GPUも格安構成も自由に選べる！パソコン買うならBTOの【FRONTIER】

【デジハリONLINEの基礎動画 + AdobeCC一年ライセンスのお得プラン】

デジタルハリウッドのオンラインCGスクール「デジハリONLINE」が提供する、60時間の基礎動画とAdobe creative cloudの一年版ライセンスがパッケージされた、お得な講座プラン

「Adobeマスター講座」

受講ノルマは一切無し、Adobe製品の利用を目的に契約してもOK！これぞコスパ最強プラン！