自動化は怖くないッ！～Google Apps Script編②～

いちいちボタンをクリックする辛さよ

前回、Google App Scriptを利用して国民の祝日を取得するスクリプトを作りました。
ボタン一つで国民の祝日を取得できる、何てラクチンなんだ！
techblog.cartaholdings.co.jp
「ちょぉ～～～～～っと、待ったァ！！」

ここで私はストップをかけさせていただきます！！！
確かに祝日を一気に取得できるようになりましたが、そのためには…
①スプレッドシートのボタンをクリックする
②取得したい年を入力する（まあ入力しなくてもいいけど）
③OKボタンをクリックする
3回も手動で何かしてるじゃないすか！やだー！（床ゴロ）
どうせ毎年取らなくちゃいけないんだったら自動で取ってほしいよ！！
しかもできれば決まった日に実施して欲しい！！祝日取得は自動で今年と来年分が取れればいいんだよ！！

Google App Scriptの出番ですよ奥様！

おいおい前回Google App Scriptを使ったよ！と言うツッコミが来そうですね。
厳密にはGoogle App Scriptをあれこれ指定した日時に自動で動かしてくれる機能を使うことで、自動的に祝日を取得できるようになります。
差し当たっては自動で動かすために必要・不要なものがありますので、前回のスクリプトを参考にしつつ一部処理を追加・削減していきます。
どうぞお付き合いくださいませ。

と、言うわけで早速Googleスプレッドシートの上部メニューの「拡張機能」＞「Apps Script」をクリックしましょう。Apps Scriptのページが表示されますので、右側のコード領域を全て選択して削除した後、後述のコードをコピー＆ペーストしてください。

クリックで展開／折りたたみ

//********************************************************* 
// グローバル変数系（変更可能）
//********************************************************* 
// 祝日を一覧化するシート名（無ければ作ります）
const holidaySheetName = "祝日一覧";
// 祝日が入っている列
const holidayDataColumn = "A:A";
// 祝日を書き込む列（範囲）
const holidayColumn = "A:B";

//*********************************************************
// ！！！以降は基本的に変更してはいけません！！！ 
// calCsvUrlのみURLが変更される場合もあるかもしれないので変更可
//********************************************************* 
// 内閣府より提示されている国民の祝日についての情報をまとめたCSVファイルのURL（ほぼ固定）
const calCsvUrl = "https://www8.cao.go.jp/chosei/shukujitsu/syukujitsu.csv";
// 祝日を取得するためのURLフェッチオプション（固定）
const urlFetchOption = {
  'method' : 'get',
  'contentType' : 'text/csv',
  'muteHttpExceptions' : true
};

//********************************************************* 
// 公開Function
//********************************************************* 
//********************************************************* 
// 概要：ボタンクリック時処理
//      ボタンクリック時に祝日カレンダー自動取得処理を呼び出す
// 引数：なし
// 戻値：なし
//********************************************************* 
function buttonClick() {
  getHolidayCalendar_(true);
  //getHolidayCalendarForButton_();
}

//********************************************************* 
// 概要：トリガー時処理
//      トリガー起動時に祝日カレンダー自動取得処理を呼び出す
// 引数：なし
// 戻値：なし
//********************************************************* 
function triggerExecute() {
  getHolidayCalendar_(false);
}

//********************************************************* 
// 非公開Function
//********************************************************* 
//********************************************************* 
// 概要：祝日カレンダー自動取得（メイン処理）
//      ダイアログ表示をし対象年を取得する
//      当年よりも前のデータは非表示にする
// 引数：msgDispFlag ダイアログ表示フラグ
//        true  ボタン起動等ダイアログ表示を要するもの
//        false トリガー起動等
// 戻値：なし
//********************************************************* 
function getHolidayCalendar_(msgDispFlag) {
  // 現在表示しているシート
  let dispSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  // 取得対象年
  let targetYear = new Date().getFullYear();
  // 祝日全権取得フラグ
  let allFlag = false;

  let preLastHoliday;       // 取得対象の前祝日
  let response;             // CSV取得のレスポンスオブジェクト
  let csvHolidayData;       // 祝日データ
  let csvHolidayDataLengh;  // 祝日データの量
  let csvHolidayDate;       // CSVファイル内の日付（yyyy/MM/dd）
  let csvHolidayName;       // CSVファイル内の祝日名
  let holidayDate;          // csvHolidayDateをDateオブジェクト変換するための変数
  let formatCsvHolidayDate; // holidayDateをyyyy/MM/dd形式の文字列にした日付
  let strTarget;            // 取得対象年（ダイアログ表示用）
  let holSheet;             // 取得した祝日を保持するシート
  let existHolidays;        // すでに取得済みの祝日データ
  let row;                  // 行番号
  let getCount = 0;         // 取得した件数

  // ボタン起動の場合はダイアログ表示
  if (msgDispFlag) {
    targetYear = Browser.inputBox("1955年以降で取得対象年を数字で入力してください。\\n「全て」入力時：1955年から現時点で取得できる全ての祝日を取得\\n空白含む数字以外指定時：本年を自動設定\\n（入力例：" +targetYear + "）", Browser.Buttons.OK_CANCEL);
    // 全角数字は半角数字変換（よくありそうな入力だし）
    targetYear = targetYear.replace(/[０-９]/g, function(s) {
      return String.fromCharCode(s.charCodeAt(0) - 0xFEE0);
      });
    if (targetYear == "cancel") {
      // ×ボタン、キャンセルが押下されたときはユーザが明示的にキャンセルしているはずなので処理終了
      Browser.msgBox("キャンセルされたため処理を終了します。");
      return;
    }
    strTarget = targetYear;
    if (targetYear == "全て") {
      allFlag = true;
      // 全量取得するため現在取得できる最小年を設定
      targetYear = 1955;
    } else if (!isFinite(targetYear) || targetYear == "") {
      targetYear = new Date().getFullYear();
      strTarget = targetYear;
    } else if (targetYear < 1955) {
      // 不正な値の場合は処理終了（1955年よりも前）
      Browser.msgBox("取得対象年が1955年以前のため、処理を終了します。");
      return;
    }
  }

  // 取得した祝日を保持するシートを設定
  holSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(holidaySheetName);
  // 対象シートが存在しない場合はシート作成
  if (!holSheet) {
    holSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet();
    holSheet.setName(holidaySheetName);
    // 新しいシートがアクティブ化してしまうので今まで表示していたシートをアクティブ化
    dispSheet.activate();
  }

  // 祝日を一覧化するシートのデータ一覧および最終行（ここだけ直書きだが日付をFilterで抽出するのはここ）
  existHolidays = holSheet.getRange(holidayDataColumn).getValues().filter(String);
  row = existHolidays.length;

  // そのままではセル側とCSVファイル側で日付形式の差分があるためフォーマットを揃える
  convertDate_(existHolidays);

  // 対象年-1で昨年末の最終祝日を取得
  // 月に11を指定しているが、実際はgetMonthに+1をしたものが対象月（欲しい月：12月）となるため
  preLastHoliday = Utilities.formatDate(new Date(targetYear - 1, "11", "31"),"JST","yyyy/MM/dd");

  // URLアクセスし情報を取得
  response = UrlFetchApp.fetch(calCsvUrl, urlFetchOption);
  if (response == null || typeof response === 'undefined') {
    return;
  }
  // 国民の祝日をまとめたCSVファイルは文字コードがShift-JISなのでエンコードする
  csvHolidayData = response.getContentText("Shift-JIS").split("\r\n");
  csvHolidayDataLengh = csvHolidayData.length;
  holSheet.unhideRow(holSheet.getRange(holidayColumn));

  let allRows = holSheet.getMaxRows() - row;
  // 最終行までの空行よりもCSVファイルの行数が多い場合は行挿入（ループ処理前に実施することで処理速度を遅くしない）
  if (allRows < csvHolidayDataLengh) {
    holSheet.insertRows(1, csvHolidayDataLengh - allRows);
  }

  // 取得したCSVファイルの行数だけ実行する
  // 行内容：yyyy/MM/dd,国民の祝日名称
  for (let i = 1; i < csvHolidayData.length; i++) {
    csvHolidayDate = csvHolidayData[i].split(",")[0];
    // ダイアログで入力された対象年でない行はスキップ
    if (msgDispFlag && !allFlag && !csvHolidayDate.startsWith(targetYear)) { continue; }
    holidayDate =  new Date(csvHolidayDate);
    // ダイアログ入力なしかつ対象年よりも前の行はスキップ
    if (!msgDispFlag && holidayDate.getFullYear() < targetYear) { continue; }
    formatCsvHolidayDate = Utilities.formatDate(holidayDate,"JST","yyyy/MM/dd");
    // 不正な値や過去の祝日データ、同日の場合はスキップ（祝日予定が途中でなくなることはよほどのイレギュラーなのでチェックしない）
    if (holidayDate.toString() === "Invalid Date" || holidayDate < preLastHoliday || existHolidays.toString().indexOf(formatCsvHolidayDate)　!= -1) { continue; }

    // ここまで来たら取得対象の祝日となる
    row++;  // 行番号
    csvHolidayName = csvHolidayData[i].split(",")[1];
    holSheet.getRange(row, 1).setValue(csvHolidayDate);
    holSheet.getRange(row, 2).setValue(csvHolidayName);
    preLastHoliday = holidayDate;
    getCount++;
  }

  // 日付をソート
  holSheet.getRange(holidayColumn).sort(1);

  // 手動実行時は結果ダイアログを表示し、トリガー起動の場合は該当年よりも前の祝日行を非表示にする
  if (msgDispFlag) {
    Browser.msgBox(holSheet.getSheetName() + "シートへの処理が完了しました。\\n対象年：" +strTarget　+ "\\n追加した祝日数：" + getCount + "日");
  } else {
    // 祝日を一覧化するシートのデータ一覧および最終行を再取得（不要な祝日（対象年よりも前のもの）は非表示にする）
    existHolidays = holSheet.getRange(holidayDataColumn).getValues().filter(String);
    for (var i = 0; i < row; i++) {
      if (targetYear <= existHolidays[i][0].getFullYear()) { break; }
      holSheet.hideRows(i + 1);
    }
  }
}

//********************************************************* 
// 概要：日付比較用データ整形（Utilities.formatDateを利用）
//      引数で渡されたものに対して処理を実施
//      日付である場合「yyyy/MM/dd」形式に変換
//      日付以外の場合は変換無し
// 引数：values
// 戻値：なし
//*********************************************************
function convertDate_(values) {
  //date型をstringに変換
  for(let i = 0; i < values.length; i++){
    let newValues = values[i].map(
    function(x){
      var type = Object.prototype.toString.call(x);
      if(type == "[object Date]"){
        return x = Utilities.formatDate(x, "JST", "yyyy/MM/dd");
        } else {
        return x;
      }
    });
    values[i] = newValues;
  }
}

そのままキーボードの[Ctrl]キーと[S]キーを同時に押して保存してしまいましょう。

実行してみよう（App Scriptの画面から）

早速、保存したスクリプトを実行してみましょう。
実行する関数を「triggerExecute」に変更し、「▷実行」をクリックします。
実行ログ側に「お知らせ　実行終了」まで出たらスプレッドシート側に戻ってみます。

あっという間にスプレッドシートの「祝日一覧」シートに、日付が入力されましたね？よく見ると今年よりも前の祝日は非表示行になり、ついでに来年の祝日が入ってます。
これはすごい！（自画自賛）

前回と何が変わったの？

前回のスクリプトとの動きの違いは大まかに以下の通りです。
①ダイアログ表示をやめた
②自動で今年以降の祝日を取得するようにした
③今年よりも前の祝日行は非表示にした

①について
自動で動かしたいのにダイアログを表示してユーザからの入力待ちにする必要はありませんよね？
なので、ダイアログは全て表示しないようにしています。（対象年の指定をするダイアログと取得結果のダイアログ）
②について
①で対象年を入力しなくなったため、最新の祝日（今年以降）を自動的に取得するようにしました。
ちなみに、国立天文台が、毎年2月に翌年の「春分の日」、「秋分の日」を官報で公表しているとのことなので、2月1日以降に動くと翌年の祝日データが取得できます。
③について
祝日を一覧化するシートに対して手動メンテナンスをするタイミングが少なくなることを想定しており、その上でユーザーがシート内容を確認したときに、去年より前のデータを確認する状況がほとんどないと仮定し、今必要としていそうな情報をぱっと見られるように、必要な行以外を非表示にしています。
（行削除しないのは、大体は処理速度の都合です）
そんな親切大きなお世話だよという方は174～180行目を削除しましょう。

実行してみよう（App Scriptのトリガー画面から）

今でこそGoogle Apps Scriptのページから手動実行していますが、今回は自動で取ってほしいから新しくスクリプトを組んだんですよね？忘れていませんよ！！
自動でスクリプトを実行するには、トリガーという機能を使って実行する必要があります。
App Scriptのプロジェクトページを今開いていると思いますが、そちらの時計マークをクリックしてみましょう。

はて、何やら画面が表示されましたね？
これが、今まで作ったスクリプトを自動で動かしてくれる「トリガー」設定のページになります。
御託は不要、さっさと設定を教えて！！という声が出てきそうなので、早速右下の「＋トリガーを追加」ボタンを押しましょう！
トリガーを追加するダイアログが表示されたと思います。その後、次の画像のように設定してみてください。

これは、さっきコピペしてもらったスクリプトの「triggerExecute」を「月1回」「毎月2日」の「午前0～1時」に実行する設定になります。
（本当は年一起動で十分なのにGoogleAppsScriptのトリガーに年単位のトリガーが無いから……）
2日起動にしている理由は、上述の通りであり2月2日には確実に祝日が確定しているはずなので1日ではなく2日に起動するように設定します。
そのまま「保存」ボタンを押しましょう。

トリガーの一覧画面に戻り、こんな感じに何か1行追加されたと思います。
ちなみに、エラーが無く動けば実際はこんな感じになります。

2日の起動まで待てないよ！という人は、さっきの設定画面で時間ベースのトリガーのタイプをもう少し短いスパンのもの（週ベースのタイマーや日付ベースのタイマー）にしてもよいと思います。沢山動いても重複した日付がとられるわけではありませんからね！
（逆に、時間ベースや分ベースの起動だと、日付取得自体はできますが、別シートで作業している間とかでも処理が動いてしまうので、「何かちょっと重くなったか？」と感じることがあるかもしれません）

ちなみに、タイムゾーンの設定によっては期待した日付時刻に起動していないこともあり得るので、その場合は設定ファイルの変更が必要です。
①左側の歯車アイコン（プロジェクトの設定）をクリック
②右側に表示された「appsscript.json」マニフェスト ファイルをエディタで表示するを選択
③左側の＜＞アイコン（エディタ）をクリック
④左側アイコンのすぐ隣にある「ファイル」領域から「appscript.json」をクリック
⑤右側に表示されたソースコードの「timeZone」の値を「"Asia/Tokyo"」に設定し、Ctrl＋Sで保存

自動化万歳！！

ほら、月一回（または指定した間隔で）Googleスプレッドシートに現時点で国が設定している祝日が取れるようになりましたね！
ボタンを押す苦痛からも、対象年を入力する苦痛からも解き放たれて、その分色んな業務に時間を費やすことが可能になりました！ぱちぱちぱち！！
決められた情報を取得するのにマウスとキーボードを使うのって本当に面倒くさいですよね。
Let’s enjoy your automatic programming！！