liquidjumper GoogleAppsScript,javascript,アプリ/ネットサービス Googleスプレッドシートで作成したカレンダーに祝日情報を加える|Google Apps Script(GAS)

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

googlespreadsheet_calendar_gas_holiday_topimage

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

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

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


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

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

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

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

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

当月以外の日にちがグレー表示


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

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

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) の範囲


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

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

祝日情報(日付、祝日名)が表示されたら成功






𝕏 ( 旧Twitter ) Facebook Pocket LINE はてブ

おすすめの関連記事



あわせてよく読まれている記事