【Googleスプレッドシート】カレンダーから、希望する日の数を数える

Googleスプレッドシートにて
このようなカレンダーを製作しました。
左上のプルダウン部分(年・月)を変更すると
その月のカレンダーになるようになっていて、
Googleカレンダーより祝日のデータを取得し、
祝日も判別され色が赤くなるようになっています。
こちらはGASを利用しています。

カレンダー自体はA3セルに
=SEQUENCE(6,7,DATE(A1,C1,1)-(WEEKDAY(DATE(A1,C1,1),1) - 1) )という
関数が入っており、表示されています。

ここから
①月曜または祝日明け
②金曜または金曜が祝日の場合その前日
③土曜・祝日前
④日曜日または祝日（月曜日が祝日の場合は、その前日の日曜日は除く）
⑤それ以外
⑥以上の合計

⑥は月の日数（2022年1月なら31日）に
なるかとおもうのですが、
以上の数を出す関数を作ることは可能でしょうか？
合計を出し、新しく合計を集計する欄を作りたいです。

現在GASに記載しているコードは以下になります。

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("#F15B5B"); 
             
            //祝日情報の連想配列に（日付:何の祝日）を追加 
            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]]); 
    } 
  } 
}

k.a_teratail

2022/06/17 11:12

> カレンダー自体はA4セルに > =SEQUENCE(6,7,DATE(A1,C1,1)-(WEEKDAY(DATE(A1,C1,1),1) - 1) )という > 関数が入っておりこちらは A3セルではないでしょうか？ > Googleカレンダーより祝日のデータを取得し、 > 祝日も判別され色が赤くなるようになっています。こちらの処理はGASを使用しているのでしょうか？ ①〜⑥　については、どこかのセル、もしくはH列とかに表示することを想定しているのでしょうか？

hachi.

2022/06/17 22:45

1つ目につきましては、記入ミスですA3セルでしたすみません。 2つ目につきましては、はいそうです。GASを利用しています。また、①～⑥はそうですね、ほかの部分に表示欄を作り表示させたいと思っております。

行動規範の内容に同意します

回答1件

①~⑤を求めるような関数は、スプレッドシートのみだと実現できないかと思われます。

既にGASで色付けを行なっている処理があると思うので、

その処理内での、データ数ループ時に

A1セルとC1セルの値を取得
new Date(A1セル値, C1セル値, ループしている日付値)で日付型データを作成し、getDay()で曜日値を取得
取得した曜日値使用し、IF文で「①~⑤」のどれに当てはまるかを判別させる
①~⑤に対応するカウンター変数値を1増やす

のようにするのが良いのでは無いでしょうか？

また別解で、どうしてもスプレッドシートの関数で行いたい場合は
別のシートなどに下記のように定義

年/月	①	②	③	④	⑤
2022/6	XX	XX	XX	XX	XX
2022/7	XX	XX	XX	XX	XX
2022/8	XX	XX	XX	XX	XX
2022/9	XX	XX	XX	XX	XX

集計セルに対して
VLOOKUP関数やINDEXとMATCH関数を利用し
①~⑤を取得、⑥は合計なのでSUM関数を使用すれば
解決できるかと思います。

ただし
この方法は自身で「①~⑤」の値をシートに追記していく必要があるので
あまりおすすめはしません。

コメント追記分

こちらにIF分を追加する場合、どのような記載にしたらようでしょうか？

ざっくりですが、コードに追加するなら、このような感じではないかと
曜日の条件部分は曖昧なので、その部分はご自身で記載を

gs
1・・・省略・・・
2
3  // 追記 
4  // ①~⑤の各カウンター
5  let counterArray = Array(0, 0, 0, 0, 0)
6
7  //年もしくは月のセルが更新されていたら実行 
8  if (active_value == true) {
9
10・・・省略・・・
11        let cell_range = active_sheet.getRange(row_i + 3, col_i + 1)
12
13        // 追記 
14        // 曜日を取得
15        let day = cellValues_calendar_data[row_i][col_i].getDay()
16        if (day === 0 || 当日が祝日か) {
17          // ④の条件
18        } else if (day === 1 || 前日が祝日か) {
19          // ①の条件
20        } else if (day === 5 && 当日が祝日か) {
21          // ②の条件
22        } else if (day === 6 || 次の日が祝日か) {
23          // ③の条件
24        } else {
25          // ⑤の条件
26        }
27
28・・・省略・・・
29
30    // 追記
31    // ①~⑥のカウンター値をセルに記載
32    counterArray.forEach(function(v, index) {
33      active_sheet.getRange(`D${index+11}`).setValue(v)
34    })
35    // ここはセルに直接SUM関数記入でも良いかと
36    active_sheet.getRange(`D16`).setValue(counterArray.reduce((sum, val) => {return sum + val}))
37
38    //祝日情報の入力を開始するセルの行番号 
39    const holiday_str_row = 11;