【GAS】Google Apps Scriptで丸1日掛りだった作業を5分に短縮した話　その2

前回の続きです。行う作業の目的や注意点を　Browser.msgBox　で表示します。僕が今現在勤めている部署の同僚は、EXCELというかSpreadsheetが全く分からない人が多いです。

ボタン一つで、作業が完了しないように分けたのには、スクリプトを書く上で管理上の問題もありますが、今、何を目的として、その作業を行なっているかを明確にし、もし万が一ミスがあったとしても、作業者に気づいてもらうためです。（←ここ多分すごく重要）

var result = Browser.msgBox("1.　メニューからファイル > インポートの順に進み、CSVをインポートしてください。\\n2.　インポート時は新しいシートを挿入するを選択してください。\\n3.　L列の作業開始日時、M列の作業終了日時の日付が正しいかどうかを確認してください。\\n（例）作業時が11月であれば、日付が10月のデータかどうかを確認してください。\\n4.　RAWデータを加工し、稼働時間を算出します。",Browser.Buttons.OK_CANCEL);
 if(result == 'ok'){

//具体的な処理内容

}

シートが暗くなり、上記のようなメッセージが表示されます。ちなみにmsgBoxの改行は、\\n　で出来るようです。

sheet.getRange(2,1,sheet.getLastRow()).setNumberFormat('[h]:mm');

setValue() 、setFormula()、に続いて表示形式を指定する事も出来るのですね！！全スタッフの合計稼働時間の場合、1000とか5000で表示して欲しいので、[h]:mm形式にしています。[h]:mm形式で表示していないデータで報告したり、給与計算したら、とんでもない大災害になります。

spreadsheet.insertSheet(lastMonth + '集計表',4);

変数と文字列を組み合わせて新しいシートを作成する事も出来ます。createじゃないんだ。なるほど....。シートを生成する位置も指定できます。流石....そして、セルの右端をドラッグして関数を引き延ばす、あの動作、マクロを記録がなかったら絶対に書けなかった。​

var destinationRange = sheet.getRange(2,7,targetRow,1);
sheet.getRange(2,7).autoFill(destinationRange, SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);

renge(関数が入った始点となるセル).autoFill(終点となるセルの指定, SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES)
理屈はよく分かりませんが、こう書けばいいらしい.....。ちなみにARRAYFORMULA関数を使わなかったのは、途中に小計を挟みたかったためです。

ちなみにCOUNTIF関数にARRAYFORMULA関数を組み合わせて使う事が出来ますが、両方とも範囲になるので、ちょっと分かりづらいですね。多分、COUNTIFSとかになると最悪....。一体何を指してるの？ってなる。間違いなく.....。

=ARRAYFORMULA(COUNTIF(A2:A,B2:B))

COUNTIF関数を最終行にドラッグコピーする際にも、項目名が存在する最終行を判定する際、.filter(String).length;　が役立ちます。

var alreadySetValues = sheet2.getRange(1,1,sheet2.getLastRow()-1).getValues();
var targetRow = alreadySetValues.filter(String).length;

なんか、既に値が入っているセルみたいなイメージで、alreadySetValuesって変数名にしたのですが、良い変数名の候補があったら、教えてください。やっぱ、LastRowあたりかな？でも厳密に言うと最終行じゃないし.....。

今回、GAS化した作業では、日本全国の案件から東京都で行われたサービスを抽出する作業がありました。QUERY関数で Where C ='東京都'みたいな感じで指定するのですが、1万5000行近くあり処理が遅く、最終行の取得がうまくいかないという場面に遭遇しました。そんなとき、ノンプロ研のとある神みたいな方から教わった秘伝の記述がコレ。

SpreadsheetApp.flush();

関数の処理を強制的に起動させるみたいな感じらしく、これで最終行の取得がうまく出来るようになりました。

基本的に書いたスクリプトは、前月のデータで使用する部分は値貼り付けで残す、不要なシートは全て削除、シートの取得、範囲の取得、関数埋め込み、最終行の取得、シートを新規に作成し、特定の名前をつけるなどが中心的な内容で、長いけれども、やっている事は基礎中の基礎ばかりでシンプルです。

シート数が10枚くらいあるために記述量が長くなってしまいました。配列の処理がうまくできれば、もう少し記述量を減らせそうな感じもしますが、まぁ動いているし、直すのも根気がいるし、これでひとまず終わりにして休憩しようかなと思います。