概要
前回の#001に引き続き、リハビリ出社記録用のカレンダーテンプレートを自動生成する話の続き。前回は問題にだけ焦点を当てたけど、今回は総括として記録します。ええ、暇なんですよ。。。
GAS(GoogleAppsScript)を使ってみるシリーズ一覧
#001. Calendar.getEvents(startTime, EndTime)の挙動がおかしい報告
いまここ☞#002. リハビリ出社記録表作成支援ツールの総括
#003. トリガーの失敗例とソース修正
#004. セルの書式設定の細かいお話
#005. jQueryとTimePickerをユーザプロンプトとして出力するお話
#006. リファクタリングとクラス設計のお話
課題
簡単に言うと以下の様なスプレッドシートの表フォーマットがあり、会社に用意していただいた分を使い切ると自分で切り貼りしなきゃならず、通院等で定時ダッシュしたい時に思わず「んああああ」となるという極めて個人的な課題に対し、全リソースを持て余している今「せっかくだからGAS使ってみよう」と思い立ったのがきっかけ。
※ちなみにJavaScriptスキルは中級未満程度だと自己分析している。また、jQueryとchartjs以外のフレームワークは使ったことがない。いつかAngularや、もう少しフロントエンドの作りこみをする機会があったらReactとかも触ってみたいなーぐらいにしか理解していないし美的センスが皆無な自分がフロントエンドエンジニアをやっている現実もちょっと想像がつかない。
着手
まず以下の方法で実装することを考えた。本稿はこの流れに沿って書くことにします。
1. シート内既出の最新日付の取得
日付の手入力が面倒だったので、自動的に直前の日付を取得することとした。
2. シート内の新たな表領域の作成と取得
用意してもらったフォーマット通りに出力出来るよう、UsedRangeから１行開けて表のエリアを取得することとした。
3. 1. の翌日から1週間分の日付を2. に出力
日付のフィル操作が面倒だったので、自動的にフィル操作の様になるように連続した日付を出力することとした。
4. 2. のレイアウト調整をする
祝祭日のセル塗りつぶしが面倒だったので、CalendarAPIを利用して祝日の識別をして塗りつぶしを行うこととした。土日の塗りつぶしもここでついでにやる。
事項以降で実際のコードと共に詳しく記載する。
① シート内既出の最新日付の取得
VBA職人だった頃思い出すと「( ´-` ).｡oO 確かRange.Findという便利なクラスメソッドがあったなぁ...」
と思ってリファレンスを漁る。スプレッドシートクラスの内部クラスとして（当然だが）Rangeクラスはあったが、findやsearch等のメソッドが見つからず、SheetやSpreadSheetクラス、SpreadSheetAppクラスにも、少なくとも分かりやすいサーチ系メソッドは見当たらなかった。
あれ、もしかしてCtrl+Fの検索ボックスってブラウザ依存かも？と思って検索ボックス出してみたが、予想に反してカスタムだった。
( ･-･)
何やら一筋縄ではいかない雰囲気で次に考えたのは、すべてのセルデータ配列の中からDateシリアル値が一番大きい値を拾って相対アドレスを取得するというもの。
またまたVBA職人だった頃思い出すのは「( ´-` ).｡oO 確かUsedRangeという便利なオブジェクトがあったなぁ...」
と思ってスプレッドシートクラスを見るとgetDataRange()というメソッドを見つけた。
Returns a Range corresponding to the dimensions in which data is present.
と書いてある。なんだかいけそうな気がする…
var sh = SpreadsheetApp.getActiveSheet();
var range = sh.getDataRange();
Logger.log( range.getA1Notaion() ); // R1C1形式でRangeのアドレスを表示する
結果
A2:I82
ｷﾀ━━━━(ﾟ∀ﾟ)━━━━!!
（事後なので記憶で…）
ここから更にDate型に絞るため、（確か）以下の実装を試みた（と思う…）
range.forEach( function( row ) {
row.forEach( function( cell ) {
if ( Object.prototype.toString.call( new Date() ) ===
Object.prototype.toString.call( cell ) {
// cellがDate型だった場合配列化する処理を…
}
}
}
と、思って内部処理でデバッグしたところ、うっかりが判明。。。
そうだよ、入退社時間もDate型じゃねえか。。。orz...
( ･-･)
※私の想定が甘く実際の実装には至らなかったのですが、上記はこちらの記事を参考にさせていただきました。<(_ _)>
どーしよーかなーと三度（みたび）リファレンスを眺めてはたと気が付く。
「...( ´･-･`)...」
「...( ´-`)...」
「...( ･-･)...」
「...( ﾟдﾟ )ﾊｯ!!...」
Range.getValues()って２次元配列で返ってくるじゃん！何でイチからせっせと配列積み重ねようとしてんの！スタックか！」
と、いうことでArray.filterと正規表現で実現する構想に辿り着き、下記関数が出来上がった。
なお、正確な日付フォーマットを正規表現するのが面倒だったため、スプレッドシートのデフォ表現であるm/d表記と、念のためm/dd, mm/d, mm/ddのみ正規表現するために実際はgetValues()ではなくgetDisplayValues()を使用した。
function getLastDate() {
var year = new Date( Date.now() ).getYear(); // 今年という事にする
var range = sh.getDataRange(); // データ使用範囲
var regex = new RegExp('[0-9]{1,2}/[0-9]{1,2}'); // 簡易日付正規表現
// 表示文字列から、正規表現に一致した日付っぽいデータだけを抽出する
var filterd = range.getDisplayValues().filter( function(cells) {
for( var i = 0; i < cells.length; i++ ) {
if ( regex.test( cells[i] ) ) {
return true;
}
}
} );
var ret = new Date( year + '/' + filterd.sort().pop().pop() ); // ソートして一番大きい値をy/とくっつけてnew Date()する
return ret;
}
② シート内の新たな表領域の作成と取得
いきなり出だしから２回ほどズッコケたが（これだからJavaでもきちっと設計したつもりでコケるんだよポンコツ…）、ようやく①は出来た。②は①でも出てきたgetDataRange()メソッドとoffset(row, column, numrows, numcolumns)メソッドを使えばもう出来たようなもの(* ´艸｀)
と、いうことで下記関数が出来上がった( ´-`).｡oO（①で体力と時間使い切ったなんて言えない…）
function printFormatOfWeek() {
var range = sh.getRange( sh.getDataRange().getLastRow() + 2, 1, 8, 9 ); // 出力先のRange
range.setBorder( true, true, true, true, true, true );
range.setHorizontalAlignments( 'center' );
var youbi = sh.getRange( range.getRow(), range.getColumn(), 1, 2 ); // '曜日'
youbi.merge();
youbi.setValue( '曜日' );
youbi.offset( 0, 2 ).setValue( '日' );
youbi.offset( 0, 3 ).setValue( '月' );
youbi.offset( 0, 4 ).setValue( '火' );
youbi.offset( 0, 5 ).setValue( '水' );
youbi.offset( 0, 6 ).setValue( '木' );
youbi.offset( 0, 7 ).setValue( '金' );
youbi.offset( 0, 8 ).setValue( '土' );
var tsukihi = youbi.offset( 1, 0 ); // '月日'
tsukihi.merge();
tsukihi.setValue( '月日' );
var yotei = tsukihi.offset( 1, 0, 2, 1 );
yotei.merge();
yotei.setValue( '予定' );
yotei.offset( 0, 1, 1, 1 ).setValue( '出社時間' );
yotei.offset( 1, 1, 1, 1 ).setValue( '退社時間' );
var zisseki = yotei.offset( 2, 0, 2, 1 );
zisseki.merge();
zisseki.setValue( '実績' );
zisseki.offset( 0, 1, 1, 1 ).setValue( '出社時間' );
zisseki.offset( 1, 1, 1 ,1 ).setValue( '退社時間' );
var taichomen = zisseki.offset( 2, 0, 1, 2 );
Logger.log( taichomen.getA1Notation() );
taichomen.merge();
taichomen.setValue( '体調面' );
var shokan = taichomen.offset( 1, 0, 1, 2 );
Logger.log( shokan.getA1Notation() );
shokan.merge();
shokan.setValue( '所感' );
sh.getRange( shokan.getRow() + ':' + shokan.getRow() ).setHorizontalAlignments( 'center' );
return range;
}
③ ①の翌日から1週間分の日付を②に出力
( ´-`).｡oO (...と、いうことで下記処理が完成した。)
var date = getLastDate(); // 表で使用されている最後の日付を取得
date.setDate( date.getDate() + 1); // 最後の日の翌日を取得
var range = printFormatOfWeek(); // 新しい表の範囲をセット
printDate( range, date ); // 最終日の翌週を新たな表にセット
function printDate( range, date ) {
var youbi = sh.getRange( range.getRow(), range.getColumn(), 1, 2 ); // '曜日'
var tsukihi = youbi.offset( 1, 1 ); // '月日'
for ( i = 0; i < 7; i++ ) {
tsukihi.offset(0, 0, 1, 7).setValues( date.getDate() + i );
}
}
コードも説明も段々雑になってきたw
④ ②のレイアウト調整をする
ここは前回、Calendar.getEvents(startTime, endTime)の問題に焦点を当てて説明したため割愛。
因みにGASってOSSでは無いんですね、ソースコードが見当たらない。issue一覧見たけどそれらしいものは引っ掛からなかったので新たにレポート発行しておくことにしたのだが、初めてなのでよう分からん…これ待ってればいいのか？？
getEvents(startTime, endTime) is returned value that not between startTime and endTime
( ´-`).｡oO (...と、いうことで下記処理が完成した。)
function printFormatHoliday( range, date ) {
var calHoliday = CalendarApp.getCalendarById( 'ja.japanese#holiday@group.v.calendar.google.com' );
var endDate = new Date( date.getTime() + 6 * 86400000 );
Logger.log( 'start = ' + date.getTime() + '\tend = ' + endDate.getTime() );
var ranges = range.getValues();
// 表内検索
for ( i = 0; i < ranges.length; i++ ) {
// 土日のセルカラーのセット
if ( ranges[i][0] === '曜日' ) {
var cSunday = ranges[i].indexOf( '日' );
var cSaturday = ranges[i].indexOf( '土' );
range.offset( 0, cSunday, range.getNumRows(), 1 ).setBackGrounds( '#f4cccc' );
range.offset( 0, cSaturday, range.getNumRows(), 1 ).setBackGrounds( '#9fc5e8' );
// Logger.log( range.offset( 0, cSunday, range.getNumRows(), 1 ).getA1Notation() );
// Logger.log( range.offset( 0, cSaturday, range.getNumRows(), 1 ).getA1Notation() );
}
// 表内祝日のセルカラーのセット
if ( ranges[i][0] === '月日' ) {
for ( j = 1; j < ranges[i].length; j++) {
if ( calHoliday.getEventsForDay( new Date( ranges[i][j] ) ).length > 0 ) {
// Logger.log( ranges[i][j] + ' is Holiday!' );
// Logger.log( 'RowOffset:' + i + '\tColumnOffset:' + j );
// Logger.log( range.offset(0, j, range.getNumRows(), 1).getA1Notation() );
range.offset(0, j, range.getNumRows(), 1).setBackGrounds( '#f4cccc' );
}
}
// 月日の行を青色に戻す
range.offset(i, 0, 1, range.getNumColumns() ).setBackGrounds( '#9fc5e8' );
// Logger.log( range.offset(i, 0, 1, range.getNumColumns() ).getA1Notation() );
}
}
}
結構ハマった（半日くらい？）ため、簡単なテストコードも書いた。
因みにテストコードは春分の日を取得する目的でこんな感じ。
本当は連休や中日などがあるGWの方がテストには向いているんだけど、まだ表になっていないし面倒くさいw
function testPrintFormatHoliday() {
Logger.clear();
var range = sh.getRange( "A57:I64" );
var date = new Date( '2018-03-18' );
printFormatHoliday( range, date );
}
最後に
あとはイベントトリガーをセットして発火を待つだけ…(* ´艸｀)ｸｽｸｽ
念のため監視もつけておこう(* ´艸｀)ｸｽｸｽ
え？エラー率高いのに結合テストはしないのかだって？？(´･ω｀･)ｴｯ?
個人的なやつだしめんどくさいので全部ぶっつけ本番で(｀・ω・´)
失敗したら続きに書きますw