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スプレッドシートをスクリプト操作してみた[初心者] 普段データ管理のツールとしてGoogleスプレッドシートを使用していますが、 GoogleスプレッドシートをExcelのVBAの様にスクリプトで操作したいと思いました。   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 AppSheet ではじめる ノーコード開発入門

Amazonで見る

楽天市場で見る

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

Amazonで見る

楽天市場で見る

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

Amazonで見る

楽天市場で見る

CGデザイナーにオススメする学ぶべきプログラミング言語は？|実務での活用方法やプログラミングスクールを紹介 近年プログラミング学習の需要が高くなっています。 CGデザインの業務に携わる現場でも、その需要は益々高くなりプログラミングが分かる人と分からない人で、今後大きな開きが出てくると思われます。 そしてプログラミングが学べるプ […]

「SAMURAI TERAKOYA」月額3,278円のサブスク型プログラミングスクールを徹底解説 飲み会一回分以下の低料金で受講できる、完全オンライン・プログラミングスクール 「SAMURAI TERAKOYA（旧SAMURAI ENGINEER Plus+）」 低料金で学べるプログラミングスクールみたいだけど、実際 […]

[PR]ahamo プランはたった一つ、基本料金2,970円（税込）容量たっぷり20GB。高品質な4G/5Gネットワーク時間帯による速度制限もなし。迷わずシンプルで分かりやすい申し込みサイトでサクッと契約。24時間いつでも受け付け可能。

Twitter Facebook Pocket LINE はてブ

Googleスプレットシートにカスタムメニューを追加する方法｜Google Apps Script(GAS) Google Apps Script(GAS)でスクリプトを作成すれば、面倒な操作や反復操作、Googleのサービスとの連携など、できることが広がります。 でも、スクリプトを実行するたびにGoogle Apps Scri […]

Googleスプレッドシート|全角日本語入力モードのままでセルに半角英数字を入力する方法-Google Apps Script 「商品名にサイズに価格に商品説明、全角・半角・数字、入力モードをいちいち切り替えるの面倒くさい何とかして！！」 商品管理のデータを入力するのは想像するより大変です。 例えば、スプレッドシートにデータを入力する時の決まり事 […]

[Google Apps Script]Googleスプレッドシートをスクリプト操作してみた[初心者] 普段データ管理のツールとしてGoogleスプレッドシートを使用していますが、 GoogleスプレッドシートをExcelのVBAの様にスクリプトで操作したいと思いました。   Googleスプレッドシートをスクリ […]