Google Apps Script スケジュール登録を行うAddOn パーツを作る


Google Apps Script の AddOn を実装しております。
Google Analytics AddOn の スケジュール登録を行うダイアログと同じものが欲しくなり、自前で実装してみました。
実装した結果を記載します。


参考


Google Analytics AddOn について

今回機能として欲しかったのは、Google スプレッドシートの Google Analytics AddOn の 以下のパーツのところです。

  • Menu部
    menu

  • ダイアログ部
    dialog

上記 AddOn は ソースコードで公開されているわけではなさそうだったので、同じようなものを自前で実装してみました。


作ったもの

作成した部品は以下になります。

Code.gs

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  var addon = ui.createAddonMenu();
  addon.addItem('Schedule', 'onClickSchedule');  
  addon.addToUi();
}

function onInstall() {
  onOpen();
}

function onClickSchedule() {
 var htmlOutput = HtmlService.createHtmlOutputFromFile("updateScheduleUi")
      .setWidth(600).setHeight(100);
 SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Schedule Ping');
}

updateScheduleUi.html

以下ダイアログUI部のHTMLになります。
これは、AddOn のダイアログ部の同様のHTMLになります。

<html>
<head>
<script src="//www.google.com/jsapi"></script><script>window.parent.maeExportApis_();</script>
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons.css">
<style>
body {
  margin: 0;
}
form {
  overflow: hidden;
}
select {
  margin: 0 6px;
  padding-left: 6px;
}
.form-row {
  margin: 10px 0 20px;
}
.form-action {
  margin: 30px 0 0;
}
</style>
</head><body style=""><form id="schedule">
  <div id="automate-container" class="form-row">
    <label>
      <input name="automate" type="hidden" value="0">
      <input id="automate" name="automate" type="checkbox" value="1">
      Enable reports to run automatically.
    </label>
  </div>
  <div class="form-row" id="automate-options-container" style="display:none">
    <label id="interval-container">
      Schedule reports to run
      <select id="interval" name="interval">
                  <option value="0">
            every hour          </option>
                  <option value="1" selected="">
            every day          </option>
                  <option value="2">
            every week          </option>
                  <option value="3">
            every month          </option>
              </select>
    </label>

    <label id="dayOfWeek-container" style="display:none">
      on
      <select id="dayOfWeek" name="dayOfWeek">
                  <option value="0" selected="">
            Monday          </option>
                  <option value="1">
            Tuesday          </option>
                  <option value="2">
            Wednesday          </option>
                  <option value="3">
            Thursday          </option>
                  <option value="4">
            Friday          </option>
                  <option value="5">
            Saturday          </option>
                  <option value="6">
            Sunday          </option>
              </select>
    </label>
    <label id="dayOfMonth-container" style="display:none">
      on the
      <select id="dayOfMonth" name="dayOfMonth">
                  <option value="0" selected="">
            1st          </option>
                  <option value="1">
            2nd          </option>
                  <option value="2">
            3rd          </option>
                  <option value="3">
            4th          </option>
                  <option value="4">
            5th          </option>
                  <option value="5">
            6th          </option>
                  <option value="6">
            7th          </option>
                  <option value="7">
            8th          </option>
                  <option value="8">
            9th          </option>
                  <option value="9">
            10th          </option>
                  <option value="10">
            11th          </option>
                  <option value="11">
            12th          </option>
                  <option value="12">
            13th          </option>
                  <option value="13">
            14th          </option>
                  <option value="14">
            15th          </option>
                  <option value="15">
            16th          </option>
                  <option value="16">
            17th          </option>
                  <option value="17">
            18th          </option>
                  <option value="18">
            19th          </option>
                  <option value="19">
            20th          </option>
                  <option value="20">
            21st          </option>
                  <option value="21">
            22nd          </option>
                  <option value="22">
            23rd          </option>
                  <option value="23">
            24th          </option>
                  <option value="24">
            25th          </option>
                  <option value="25">
            26th          </option>
                  <option value="26">
            27th          </option>
                  <option value="27">
            28th          </option>
                  <option value="28">
            29th          </option>
                  <option value="29">
            30th          </option>
                  <option value="30">
            31st          </option>
              </select>
    </label>

    <label id="hourOfDay-container">
      between
      <select id="hourOfDay" name="hourOfDay">
                  <option value="0">
            midnight &nbsp;&nbsp; 1 a.m.         </option>
                  <option value="1">
            1 a.m. &nbsp;&nbsp; 2 a.m.           </option>
                  <option value="2">
            2 a.m. &nbsp;&nbsp; 3 a.m.           </option>
                  <option value="3">
            3 a.m. &nbsp;&nbsp; 4 a.m.           </option>
                  <option value="4" selected="">
            4 a.m. &nbsp;&nbsp; 5 a.m.           </option>
                  <option value="5">
            5 a.m. &nbsp;&nbsp; 6 a.m.           </option>
                  <option value="6">
            6 a.m. &nbsp;&nbsp; 7 a.m.           </option>
                  <option value="7">
            7 a.m. &nbsp;&nbsp; 8 a.m.           </option>
                  <option value="8">
            8 a.m. &nbsp;&nbsp; 9 a.m.           </option>
                  <option value="9">
            9 a.m. &nbsp;&nbsp; 10 a.m.          </option>
                  <option value="10">
            10 a.m. &nbsp;&nbsp; 11 a.m.         </option>
                  <option value="11">
            11 a.m. &nbsp;&nbsp; noon            </option>
                  <option value="12">
            noon &nbsp;&nbsp; 1 p.m.             </option>
                  <option value="13">
            1 p.m. &nbsp;&nbsp; 2 p.m.           </option>
                  <option value="14">
            2 p.m. &nbsp;&nbsp; 3 p.m.           </option>
                  <option value="15">
            3 p.m. &nbsp;&nbsp; 4 p.m.           </option>
                  <option value="16">
            4 p.m. &nbsp;&nbsp; 5 p.m.           </option>
                  <option value="17">
            5 p.m. &nbsp;&nbsp; 6 p.m.           </option>
                  <option value="18">
            6 p.m. &nbsp;&nbsp; 7 p.m.           </option>
                  <option value="19">
            7 p.m. &nbsp;&nbsp; 8 p.m.           </option>
                  <option value="20">
            8 p.m. &nbsp;&nbsp; 9 p.m.           </option>
                  <option value="21">
            9 p.m. &nbsp;&nbsp; 10 p.m.          </option>
                  <option value="22">
            10 p.m. &nbsp;&nbsp; 11 p.m.         </option>
                  <option value="23">
            11 p.m. &nbsp;&nbsp; midnight        </option>
              </select>
    </label>

  </div>

  <div class="form-action">
    <button class="action" type="submit">Save</button>
    <button onclick="google.script.host.close()" type="button">Cancel</button>
  </div>

</form>

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script>
<script>
$('#schedule').on('submit', function(e) {
  e.preventDefault();
  var formData = $(this).serializeArray();
  google.script.run
      .withSuccessHandler(google.script.host.close)
      .withFailureHandler(alert)
      .updateSchedule(formData);
});
$('#automate').on('click', function() {
  if ($(this).is(':checked')) {
    $('#automate-options-container').show();
    google.script.host.setHeight($(document.body).height());
  }
  else {
    $('#automate-options-container').hide();
    google.script.host.setHeight($(document.body).height());
  }
});
$('#interval').on('change', function() {
  switch (+$(this).val()) {
    case 0: 
      $('#dayOfWeek-container').hide();
      $('#dayOfMonth-container').hide();
      $('#hourOfDay-container').hide();
      break;
    case 1: 
      $('#dayOfWeek-container').hide();
      $('#dayOfMonth-container').hide();
      $('#hourOfDay-container').show();
      break;
    case 2: 
      $('#dayOfWeek-container').show();
      $('#dayOfMonth-container').hide();
      $('#hourOfDay-container').show();
      break;
    case 3: 
      $('#dayOfWeek-container').hide();
      $('#dayOfMonth-container').show();
      $('#hourOfDay-container').show();
      break;
  }
});
google.script.host.setHeight($(document.body).height());
</script>
</body></html>

updateSchedule.gs

var KEY = "trigger";
var FUNCTION_NAME = "pingSitemap";

var weekDay = [ScriptApp.WeekDay.MONDAY, 
               ScriptApp.WeekDay.TUESDAY, 
               ScriptApp.WeekDay.WEDNESDAY,
               ScriptApp.WeekDay.THURSDAY,
               ScriptApp.WeekDay.FRIDAY,
               ScriptApp.WeekDay.SATURDAY,
               ScriptApp.WeekDay.SUNDAY
              ]

function updateSchedule(formData) {
  var formData = toJson_(formData);
  Logger.log(JSON.stringify(formData))
  if (formData != null) {
    if (formData.automate == 0) {
      deleteTrigger_();
    } else if (formData.automate == 1) {
      if (formData.interval == 0) {
        deleteTrigger_();
        var triggerId = ScriptApp.newTrigger(FUNCTION_NAME).timeBased()
        .everyHours(1)
        .create().getUniqueId();
        setTrigger_(triggerId);
      } else if (formData.interval == 1) {
        deleteTrigger_();
        var triggerId = ScriptApp.newTrigger(FUNCTION_NAME).timeBased()
        .atHour(formData.hourOfDay)
        .everyDays(1)
        .inTimezone(Session.getTimeZone())
        .create().getUniqueId();
        setTrigger_(triggerId);
      } else if (formData.interval == 2) {
        deleteTrigger_();
        var triggerId = ScriptApp.newTrigger(FUNCTION_NAME).timeBased()
        .onWeekDay(weekDay[formData.dayOfWeek])
        .atHour(formData.hourOfDay)
        .nearMinute(30)
        .create().getUniqueId();
        setTrigger_(triggerId);
      } else if (formData.interval == 3) {
        deleteTrigger_();
        var triggerId = ScriptApp.newTrigger(FUNCTION_NAME).timeBased()
        .onMonthDay(formData.dayOfMonth)
        .atHour(formData.hourOfDay)
        .nearMinute(30)
        .create().getUniqueId();
        setTrigger_(triggerId);
      } else {
        throw new Error("Illegal Argments...");
      }
    }
  }  
}

//serializeArrayをjsonに変換する
function toJson_(formData) {
  var result = {};
  var automateValue = 0;
  formData.forEach(function(elem, i) {
    if(elem["name"] == "automate" && elem["value"] == 1) {
      automateValue = 1;
    }
    result[elem.name] = elem.value;
  });
  result["automate"] = automateValue;
  return result;
}

//指定したkeyに保存されているトリガーIDを使って、トリガーを削除する
function deleteTrigger_() {
  var triggerId = PropertiesService.getScriptProperties().getProperty(KEY);
  if(!triggerId) return;
  ScriptApp.getProjectTriggers().filter(function(trigger){
    return trigger.getUniqueId() == triggerId;
  })
  .forEach(function(trigger) {
    ScriptApp.deleteTrigger(trigger);
  });
  PropertiesService.getScriptProperties().deleteProperty(KEY);
}

//トリガーを発行
function setTrigger_(triggerId){ 
  //あとでトリガーを削除するためにトリガーIDを保存しておく
  PropertiesService.getScriptProperties().setProperty(KEY, triggerId);
}

組み込んで使用する際に変更が必要な箇所

  • SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Schedule Ping');
    'Schedule Ping' がダイアログのタイトルに影響します。変更が必要な場合は変更してください。

  • var FUNCTION_NAME = "pingSitemap";
    スケジュール実行時に実行するfunction名になります。実装するfunction名に変更してください。


勉強になったこと

AddOn のメニュー追加について

  • スプレッドシート起動時にonOpen の処理が発火する
    onOpen 内で、SpreadsheetApp.getUi().createAddonMenu();で、AddOnMenuを作成し、addItem() していく。
    addToUi()を実行しないと、画面には反映されない。

  • onInstall() は AddOnのインストール時に発火する
    参考にした何かのサンプル実装で、onOpen()を実行しており踏襲で問題ないかと思い同じくonOpen()を呼び出すようにしています。

  • JQuery#serializeArray() について
    オブジェクトをjson の配列に変換してくれるメソッドです。
    使ったことなかったので、jsonが返ってくると思い込んで実装して、結構はまりました。
    HTML上でformをJQuery#serializeArray()で変換した後に、
    Apps Script側で、以下のメソッドでjson に変換するようにしました。
    automate をスペシャルロジックで処理しているのは、html側の実装都合です。

//serializeArrayをjsonに変換する
function toJson_(formData) {
  var result = {};
  var automateValue = 0;
  formData.forEach(function(elem, i) {
    if(elem["name"] == "automate" && elem["value"] == 1) {
      automateValue = 1;
    }
    result[elem.name] = elem.value;
  });
  result["automate"] = automateValue;
  return result;
}
  • スケジュール実行用のトリガー登録について
    ScriptApp.newTrigger(FUNCTION_NAME) 実行後、メソッドチェーンで月次、週次、日次等でスケジュール登録しています。
    Class Trigger  |  Apps Script  |  Google Developers を参考にしながら実装は進めました。

使い回しは効きそうな実装なので今後 AddOn を作成する際は流用していこうかと思います。
以上です。

コメント