5 返信 最新の回答 日時: May 22, 2018 7:34 AM ユーザー:Anonymous

    スプレッドシートで日本の祝祭日を表示させたい

    Anonymous

      Google スプレッドシートで、

      任意の日付が、日本の暦のうえで休日であるか否か (「休日」=「土曜・日曜・祝祭日 のいずれか」)

      を表示させたいです。

       

      現在は、画像のとおり、WEEKDAY 関数を使って、土曜・日曜 だけを休日として表示させるに甘んじています。

      しかし、画像の矢印の所に書いてあるとおり、本来は、「土日『と祝祭日』」の場合に "0" を表示させたいです。

       

      ちなみに、Google カレンダーには、

      「日本の祝日」(カレンダー ID: ja.japanese#holiday@group.v.calendar.google.com)

      というカレンダーが Google によって用意されていますので、

      このデータをスプレッドシートから直接参照できれば解決するのですが……。

       

      もちろん、他の方法でも結構です

      (何年後でも使えるような、普遍的な方法が好ましいです。

      なお、前段の「日本の祝日」カレンダーは、もう何年も使えています)。

       

      つきましては、上記の私の希望をかなえる方法がありましたら、お教えいただけないでしょうか。

       

      スクリーンショット 2018-05-21 19.30.45.png

        • Re: スプレッドシートで日本の祝祭日を表示させたい
          Anonymous

          GASでカレンダーIDからデータを取得する方法があるので、

          日本の祝日シートをつくりGASで祝日データを抜き取る。

           

          あとは日付をIFで切り分けてはどうでしょうか?

           

          わたしは土日祝日のセルの色を 条件付き書式で変えるために、

          会社の休日シートを一つ用意しています。

          会社の夏連休とかは日本の祝日カレンダーでは拾えないので、

          毎年会社から提供されるPDFからお休みを抜き出して、スプレッドシートに貼り付ける、

          ローテクです。

           

          GASコード

           

          function myFunction() {

           

            var ss = SpreadsheetApp.getActiveSpreadsheet();

            var as = ss.getActiveSheet();

            var user =Session.getEffectiveUser();

            var id = user.getEmail(); ←このコードではマイカレンダーを参照しようとしております。今回は必要ない。

            var mycal = CalendarApp.getCalendarById(id);  ←ここを祝日カレンダーのIDに変更すると本コード流用できると思います。

            var start = as.getRange("D8").getValue();

            var end = as.getRange("D9").getValue();

            var outrow = 80;

            var colcode = 7

            var coltitle = 8;

            var colstart = 9;

            var colend = 10;

            var coldiff =11;

           

           

            var events = mycal.getEvents(start,end);

            for each (var event in events) {

              as.getRange(outrow, colcode).setValue(event.getTitle().substr(0,3));

              as.getRange(outrow, coltitle).setValue(event.getTitle()); ←ここでタイトル取得できる(空でない)のが祝日になると思います。

              as.getRange(outrow, colstart).setValue(event.getStartTime()); ←ここ以下の行は日付だけ取れれば良いので工夫して下さい。

              as.getRange(outrow, colend).setValue(event.getEndTime());

          //    as.getRange(outrow, coldiff).setValue(event.getEndTime()-event.getStartTime());

              outrow++;

            }

           

          このコードは自分の作業をGoogleカレンダーに入力していって、

          毎日・毎月・毎年、どんな作業に何時間にかかっているかを、

          Googleスプレッドシートに集計するのに使ってます。

          (毎晩午前1:00にコードが自動実行されるようにしております)

           

          タイトルのはじめ3桁の数字は、予め作業内容をコード化したものを入力するようにしてます。

          あとは日本語変換の辞書に登録して、

          作業タイトルを入力すると数字のコードに変換するようにして、

          入力の効率を上げています。

          1 of 1 people found this helpful
            • Re: スプレッドシートで日本の祝祭日を表示させたい
              Anonymous

              ありがとうございます。

              実は GAS についてよく知らないのですが、調べて実行してみたいと思います。

               

              関連事項も参考になりました。

                • Re: スプレッドシートで日本の祝祭日を表示させたい
                  Anonymous

                  GASを書いてみました。うまくいきましたので、共有します。

                   

                  https://goo.gl/6h5TZ9

                  まず上記スプレッドシートをコピーしてマイドライブに置いて下さい。

                  コピーしたスプレッドシートを開いて、

                  ツール>スクリプトエディタ

                  でスクリプトエディタを開いて下さい。

                  実行「▶」アイコンを押して、

                  スクリプトを実行して下さい。

                  途中許可を促す画面が出ると思います。

                  自分のGoogleIDで許可して下さい。

                   

                  コードは以下のものです。

                  特に危ない命令などは使っていません。

                  心配であればGoogleの公式リファレンスをググって確認して下さい。

                  ----------------------------------

                  function myFunction() {

                    var ss = SpreadsheetApp.getActiveSpreadsheet();

                    var as = ss.getActiveSheet();

                    var jhdcal = CalendarApp.getCalendarById('ja.japanese#holiday@group.v.calendar.google.com');

                    var outrow = 1;

                    var colhdate = 1;

                    var coltitle = 2;

                   

                    var events = jhdcal.getEvents(new Date('2018/01/01'), new Date('2018/12/31'));

                    for each (var event in events) {

                      if (event.getTitle() != "") {

                        as.getRange(outrow, colhdate).setValue(event.getAllDayStartDate());

                        as.getRange(outrow, coltitle).setValue(event.getTitle());

                        outrow++;

                      }

                    }

                  }

                  ---------------------------------

                  結果は下記のような感じです。

                  アクティブなシートに出力されます。

                  1 of 1 people found this helpful
                    • Re: スプレッドシートで日本の祝祭日を表示させたい
                      Anonymous

                      ありがとうございます! これなら私にも直ちにできます。

                       

                      日々の作業時間の計算のために、当該カレンダーをお使いとのこと。

                      ネット上に下記のような主旨の記述があるのですが、この辺りは受け入れた上でご使用なのでしょうか?

                       

                      「提供されている『日本の祝日』カレンダーは、内容が不正確なところがある。

                      そのため、正確な版を作成して公開している人もいる」

                       

                      私は日本の休日のデータを、

                      会社の会計関係のシステムに使いたいと思っておりますので、

                      不正確なのはまずいなと思っているのですが……。

                        • Re: スプレッドシートで日本の祝祭日を表示させたい
                          Anonymous

                          作業時間計算は、自分のライフログ程度の感覚でやっているもので、

                          給与とかには関係ないデータです。

                          まずログを取らないと、WSIが出来ないかなと思い、

                          いろいろと工夫しているのですが、

                          社内へ普及する気配はありません。

                           

                          「日本の祝日」カレンダーは、不正確なところがあるのですね。

                          情報提供ありがとうございます。

                          やはり政府などが出している、公共データを取り込むほうが、

                          良いかもしれませんね。