概要

　スプレッドシート出社記録を付けているのだが、この表何と手作り(* ´艸｀)ｸｽｸｽ
無くなったら新たに作成するのですが、、、（コピペと日付直しと祝祭日のセルカラー変更と～などちまちました操作が必要）

面倒なので自動化します！(`・ω・´)

…というのが#001から続けてきた事です。前回#004はセル書式の設定等細々とした部分の実装を行いました(`・ω・´)

GAS(GoogleAppsScript)を使ってみるシリーズ一覧

#001. Calendar.getEvents(startTime, EndTime)の挙動がおかしい報告
#002. リハビリ出社記録表作成支援ツールの総括
#003. トリガーの失敗例とソース修正
#004. セルの書式設定の細かいお話
いまここ☞#005. jQueryとTimePickerをユーザプロンプトとして出力するお話
#006. リファクタリングとクラス設計のお話

課題

　今回の課題は予定出社時間と予定退社時間のセルを予め指定した時刻で埋めたいという事です。

前回までの実行結果↓

今回ではこうしたい↓

雑な設計

　大雑把に以下の機能が必要になると考える。

phase 1. どこかで時刻を指定する　
phase 2. 指定した時刻を出力する

検討

結論、こうなった。

以下で他にも考えられる手法をそれぞれ幾つか上げてみよう。

phase 1. どこかで時刻を指定する（設計）

A. どこかのセルに設定値として残しておく

　これが一番簡単だがカッコ悪いし何よりメンテしづらく事故りやすい。自分しか使わないコードならこれでも良いかも知れない。しかしこのやり方はブログにUPるにはあまりにもカッコ悪過ぎるし少し工夫を凝らしたいため今回は見送った。

B. Googleフォームを作成して入力する

　これが次に簡単と思われる。が、ブログにするために少し工夫を凝らしたいため見送った。

C. 実行時に簡易な入力インタフェースを表示してユーザーからの入力値を得る

　GASにはダイアログやサイドバーとして新たにHTML出力することが出来るHTMLServiceというものがある。更に、その生成したコンテンツからクライアントサイドAjaxとしてGASのユーザ関数を呼ぶことが出来るgoogle.script.runというクラスがある。実は#003終了後に何となくリファレンスを眺めていたらこれらを見つけて本稿を思いついた。なので、色々考案した体で書いているが実は出来レースですはい(`・ω・´)

D. Calendarの様にセルをクリックして入力インタフェースを出す

　EventTypeを調べてみたがSpreadSheetAPIとしてはクリックイベントを実装していない様子…。addEventListenerでDOMを渡せば出来そうな気もするが、セルの識別が面倒だ…（と言う体）

　※実際にDOMツリーを調べてみたが、（セキュリティの観点から当然といえば当然だが）表面上はアクセスすることが出来なかった。.goog-inline-block grid4-inner-container内部に描写こそされているが、DOMとしてアクセス可能だったのはActiveCellを示す外枠（border）とAuto-Fillするための■までだった。Spreadsheet自体の実装方法がちょっと気になるが、深追いは本稿の対象外のためここまでに留める。

phase 2. 指定した時刻を出力する（設計）

　ここで少し厄介なのが、入力したデータを出力時まで保持する必要があるということ。この課題をクリアするためには、以下の方法が考えられる。

a. ファイルに書き出して読み込む

　セキュリティにうるさい昨今、昔のようにFileSystemObjectを使っていいのだろうか...。これは見送ろう。

b. Cookieに書き出して読み込む

　Cookieに書き出す事で安全にデータのやり取りをする。始めに考えたのはこれ。GASではCacheServiceを利用してキーバリュー式でデータを短時間の間格納できる。但し、どの種類のCacheも例外なくexpireの時間をセット出来るような要素がないため、文字通りのキャッシュの様だ。

　document.cookieで期待する効果は得られるかと一度は考えたが、Google等の昨今のマルチデバイスアクセスサービスを考えると、やっぱりサーバーサイドで値を保持したい…(´･ω･)

（ユーザーキーバリューとしてもしかしたらGoogleアカウントが持ってくれるかも知れないけど確認が面倒だったので）

と、いうことでこれも見送った。

c. インスタンス化して保持する。

　GAS側で果たしてこれが出来るのか…

d. 入力と出力を一つの処理内でおさめる。

　これは少し無理があるので打つ手が無くなった時の最終手段用…

e. Propertiesを使ってみる。

　bが頓挫するや否や、いつもの様にぼーっとAPIリファレンスを眺めていると…( ﾟ ρ ﾟ )ﾎﾞｰ

PropertiesService
なるものが目に留まる。

The Properties service lets you store simple data in key-value pairs scoped to one script, one user of a script, or one document in which an add-on is used. It is typically used to store developer configuration or user preferences. Properties are never shared between scripts.

ほう！なんかイケそうな気がする(∩´∀｀)∩
これは任意のキーバリューをサーバーサイドでプロパティとして保持できる様だ。

と、いうことで
C-eのパターンで実装します(`・ω・´)

実装

　前回までの関数は無論そのままにしたかったが、敢えて printFormatOfWeek関数を少しだけ いじって 実装しました。

大事な事なのでもう一度言います(`・ω・´)

「敢えて」いじって みました(`・ω・´)

** テスト終わってる完成品を「敢えて」 ** (`・ω・´)

理由はですね、、、第一回でも触れた通り、GASはセル検索が弱いので、、、
printFormatOfWeek関数でOffsetした「予定」のセル範囲をそのまま引数として関数に渡したかったからです(;´･ω･)

そうすると範囲全体に同じ値が書き込まれ、後で実行されるprintFormatHoliday関数が土日祝の部分を空白で上書いてくれますので、、、(;´･ω･)

こうやって所謂 クソコード って出来上がるんですね(;´･ω･)

という部分を残したかったので「敢えて」いじってみました(;;`・ω・´)

こうすることでリファクタリングのための第六回枠への伏線が自然と出来上がる訳ですな(∩´∀｀)∩

と、いうことで phase 1 から見ていきましょう。

phase 1. どこかで時刻を指定する（実装）

　phase 1 を実現するための関数setPlanTime()を実装します。実行するとTimePickerを実装したダイアログが出てきて、ユーザー入力を促します。OKボタンを押すとそのデータがサーバー側に引き渡されてPropertyとしてセットされるという想定です(`・ω・´)

function setPlanTime() {
  
  // htmlの生成
  var html = '<html><head>';
  html += '<script src=\"https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js\"></script>';
  html += '<link rel="stylesheet" href="//cdnjs.cloudflare.com/ajax/libs/timepicker/1.3.5/jquery.timepicker.min.css\"></head><br>';
  html += '<body><script src="https://cdnjs.cloudflare.com/ajax/libs/timepicker/1.3.5/jquery.timepicker.min.js\"></script>';
  html += '出社時刻<input id="from_start" value="" class="timepicker text-center" jt-timepicker="" time="model.time" time-string="model.timeString" default-time="model.options.defaultTime" time-format="model.options.timeFormat" start-time="model.options.startTime" min-time="model.options.minTime" max-time="model.options.maxTime" interval="model.options.interval" dynamic="model.options.dynamic" scrollbar="model.options.scrollbar" dropdown="model.options.dropdown" /><br>';
  html += '退社時刻<input id="to_end" value="" class="timepicker text-center" jt-timepicker="" time="model.time" time-string="model.timeString" default-time="model.options.defaultTime" time-format="model.options.timeFormat" start-time="model.options.startTime" min-time="model.options.minTime" max-time="model.options.maxTime" interval="model.options.interval" dynamic="model.options.dynamic" scrollbar="model.options.scrollbar" dropdown="model.options.dropdown" /><br><br>';
  html += '<input id="btnOK" type="button" value="OK" onclick="google.script.run.withSuccessHandler(uiClose).setValue(document.querySelector(\'#from_start\').value, document.querySelector(\'#to_end\').value);" /><br>';
  
  var htmloutput = HtmlService.createHtmlOutput(html)
    .setWidth( 300 )
    .setHeight( 200 )

  // スクリプトの生成
  .append( "<script>$('#from_start').timepicker({timeFormat: \"H:mm\",interval: 15,minTime: \"6:00\",maxTime: \"23:00\", startTime: \"9:00\",dynamic: false,dropdown: true,scrollbar: true });" )
  .append( "$('#from_start').change( () => { $(this).attr( 'value', $(this).val() ) } );" )
  .append( "$('#to_end').timepicker({ timeFormat: \"H:mm\", interval: 15, minTime: \"6:00\", maxTime: \"23:00\", startTime: \"17:30\", dynamic: false, dropdown: true, scrollbar: true });" )
  .append( "$('#to_end').change( () => { $(this).attr( 'value', $(this).val() ) } );" )
  .append( 'function uiClose() { google.script.host.close(); }' )
  .append( "</script></body></html>" );
  
  // dialogの呼び出し
  SpreadsheetApp.getUi().showModalDialog( htmloutput, '予定時刻のセット' );
  
}

　GAS的には中段のvar htmloutput = HtmlService.createHtmlOutput(html)と最後のSpreadsheetApp.getUi().showModalDialog( htmloutput, '予定時刻のセット' );が今回のキモです。

HTMLServiceを使うことでHTMLコンテンツを生成してクライアント側に投げることが出来るようになります。当然ですが、クライアント環境のため（クライアントに応じた）JavaScriptも動作します。

SpreadsheetApp.getUi()関数は、スプレッドシート内におけるユーザインタフェースを提供するUiクラスを返します。なお、Uiクラスはアプリケーション毎に異なる。

※注：旧バージョンのスプレッドシートだと上記getUi()メソッドは動作しない模様

　jQueryはchange()とval()とattr()だけで実装している。また、TimePickerの基本的な使い方は簡単で、オプションやイベントを渡して呼び出すだけなので簡単。各オプションやイベントの使い方はコチラ。

HTML+jQueryとして表示するとこの通りシンプル( *´艸｀)

<html>
 <head>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
  <script>
    var starttime = "";var endtime = "";
  </script>
  <link rel="stylesheet" href="//cdnjs.cloudflare.com/ajax/libs/timepicker/1.3.5/jquery.timepicker.min.css\">
 </head>
 <br>
 <body>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/timepicker/1.3.5/jquery.timepicker.min.js\"></script>
  出社時刻
  <input id="from_start" value="" class="timepicker text-center" jt-timepicker="" time="model.time" time-string="model.timeString" default-time="model.options.defaultTime" time-format="model.options.timeFormat" start-time="model.options.startTime" min-time="model.options.minTime" max-time="model.options.maxTime" interval="model.options.interval" dynamic="model.options.dynamic" scrollbar="model.options.scrollbar" dropdown="model.options.dropdown" />
  <br>
  退社時刻
  <input id="to_end" value="" class="timepicker text-center" jt-timepicker="" time="model.time" time-string="model.timeString" default-time="model.options.defaultTime" time-format="model.options.timeFormat" start-time="model.options.startTime" min-time="model.options.minTime" max-time="model.options.maxTime" interval="model.options.interval" dynamic="model.options.dynamic" scrollbar="model.options.scrollbar" dropdown="model.options.dropdown" />
  <br>
  <br>
  <input id="btnOK" type="button" value="OK" onclick="google.script.run.withSuccessHandler(uiClose).setValue(document.querySelector(\#from_start\).value, document.querySelector(\#to_end\).value);" />
  <br>
  
  <script>
   $(#from_start).timepicker( {
     timeFormat: "H:mm",
     interval: 15,
     minTime: "6:00",
     maxTime: "23:00",
     startTime: "9:00",
     dynamic: false,
     dropdown: true,
     scrollbar: true 
   } );

   $(#from_start).change( () => { 
     $(this).attr( 'value', $(this).val() )
   } );
   
   $(#to_end).timepicker( { 
     timeFormat: "H:mm",
     interval: 15,
     minTime: "6:00",
     maxTime: "23:00",
     startTime: "17:30",
     dynamic: false,
     dropdown: true,
     scrollbar: true
   } );
   
   $(#to_end).change( () => { 
     $(this).attr( 'value', $(this).val() )
   } );
   
   function uiClose() { 
     google.script.host.close(); 
   }
  </script>
 </body>
</html>

　この中で少しややこしい部分はGAS側のWithSuccessHandlerとクライアント側のuiClose()で呼び出しているgoogle.script.host.close()だと思います。

　ざっくりと説明するとgoogle.script.run自体がGASのユーザ定義関数群を呼び出す事が可能な中間インタフェースの様なもので、google.script.run.withSuccessHandler(function)は、GAS側の処理が成功した場合に限り、引数に渡したコールバック関数を完結する関数型インタフェースの様なものと考えて良いと思います。（実際にはJavaScriptには関数型はありません。）withSuccessHandler自体はgoogle.script.runを返すため、メソッドチェーンで指定したGAS側のsetValue(start_time, end_time)関数が成功した場合に限りコールバック関数であるuiClose()に処理を移譲すると考えてください。

　google.script.hogehoge系はクライアントサイドJavascriptとしてAjax通信を行ってサーバーサイドJavascriptAPIをキックしているため、制御が必要場合に備えてリターンコードによるSuccessとFailureのハンドルが実装されてます。

　つまり、GAS側のsetValueが成功した場合に限り、クライアント側のuiClose()を通してGAS側のgoogle.script.host.close()が呼び出されるという事です。

絵にするとこんな感じ？？

withSuccessHandler

not use handlers

google.script.runの公式リファレンスに詳しく記載あり。

phase 2. 指定した時刻を出力する（実装）

このsetValueが、google.script.runから呼ばれてDOMにセットされた値を保存するための関数。

function setValue( s, e ) {
  PropertiesService.getDocumentProperties()
    .setProperty( 'estimated_start_time', s )
    .setProperty( 'estimated_end_time', e );  

  Logger.log( s + "~" + e );
}

引数に出力範囲セルを渡すとPropertiesの内容を読み取って出社時間と退社時間をセルに書き込む処理。今回はprintFormatOfWeek関数から呼び出している。

function printPlanTime( range ) {
  var prop = PropertiesService.getDocumentProperties().getProperties();
  start_time = prop['estimated_start_time']; 
  end_time = prop['estimated_end_time'];
  range.setValue( start_time )
    .offset( 1, 0 ).setValue( end_time );
}

以下printFormatOfWeek()関数本文。体裁的に少し直したが、実質的な変更点は printPlanTime( yotei.offset( 0, 2, 1, 7 ) ); の1行のみ。

function printFormatOfWeek() {
  var horizontalAlignments = [
    [ "center", "center", "left", "left", "left", "left", "left", "left", "left" ]
  ];
  var range = sh.getRange( sh.getDataRange().getLastRow() + 2, 1, 8, 9 ); // 出力先のRange
  range.setBorder( true, true, true, true, true, true )
    .setHorizontalAlignment( "center" )
    .offset( 8, 0, 1, 9 ).setHorizontalAlignments( horizontalAlignments );
  
  var youbi = sh.getRange( range.getRow(), range.getColumn(), 1, 2 ); // '曜日'
  youbi.merge()
    .setValue( '曜日' )
    .offset( 0, 2, 1, 1 ).setValue( '日' )
    .offset( 0, 1, 1, 1 ).setValue( '月' )
    .offset( 0, 1, 1, 1 ).setValue( '火' )
    .offset( 0, 1, 1, 1 ).setValue( '水' )
    .offset( 0, 1, 1, 1 ).setValue( '木' )
    .offset( 0, 1, 1, 1 ).setValue( '金' )
    .offset( 0, 1, 1, 1 ).setValue( '土' );

  var tsukihi = youbi.offset( 1, 0 ) // '月日'
    .merge().setValue( '月日' );
  
  var yotei = tsukihi.offset( 1, 0, 2, 1 )
    .merge().setValue( '予定' );
 
  yotei
    .offset( 0, 1, 1, 1 ).setValue( '出社時間' )
    .offset( 1, 0, 1, 1 ).setValue( '退社時間' );
  
/* 重複した線形操作になるためいずれ直す */
  printPlanTime( yotei.offset( 0, 2, 1, 7 ) );  // 予定時刻の入力
/* * */

  var zisseki = yotei.offset( 2, 0, 2, 1 )
    .merge().setValue( '実績' );
  
  zisseki
    .offset( 0, 1, 1, 1 ).setValue( '出社時間' )
    .offset( 1, 0, 1 ,1 ).setValue( '退社時間' );

  var taichomen = zisseki.offset( 2, 0, 1, 2 )
    .merge().setValue( '体調面' );
  
  var shokan = taichomen.offset( 1, 0, 1, 2 )
    .merge().setValue( '所感' );
  
  shokan
    .offset( 0, 2, 1, 7)
    .setHorizontalAlignment( 'left' )
    .setVerticalAlignment( 'top' )
    .setWrapStrategy( SpreadsheetApp.WrapStrategy.CLIP)
    .setWrap( true );
  
  return range;
}

Propertiesのテスト用コードはこんな感じ。

function debugProperties() {
  Logger.clear();
  var prop = PropertiesService.getDocumentProperties().getProperties();
  for ( var k in prop ) {
    Logger.log('key => %s , value => %s', k, prop[k] );
  }
}

事前準備

1. マクロ登録

　ユーザーが予定時間を変更したい時にsetPlanTime関数を呼び出すため、マクロ登録しておきます( *´艸｀)

ツール > マクロ > インポートからsetPlanTimeの部分の《関数を追加》をクリック

※☟は既にsetPlanTimeをインポート済みのため表示されていません

2. 予定時間のセット

　インポートしたsetPlanTimeはツール > マクロ > にリスト化されているのでこれを実行。または、ショートカットキーをセットしてそのキーを実行すると関数を実行することが出来る。

ちゃんとTimePickerも動いている( *´艸｀)

3. Propertiesの値を確認する

　OKボタンを押すとサーバー側のsetValueが実行されてPropertiesに時間がセットされるはずなので、スクリプトエディタからdebugPropertiesを実行する。

念のため日をまたいでみたがしっかりと値を保持してくれていた(* ´艸｀)ｸｽｸｽ

実行結果

before

after

大成功(∩´∀｀)∩

終わりに

　次回#006は（多分）ソース全体のリファクタリングをしようかな。
　無駄な重複線形処理があるので抽象化したいなと思ってます( *´艸｀)

　リハビリ出社いつまで続くんだよとか突っ込まないでください(;´Д`)