【GAS】Google Apps Scriptで丸1日掛りだった作業を5分に短縮した話 その1
GASを書けば、書くほど、こんな事が一瞬で出来るんだという事が分かり、「もしかして、こんな事も出来たりする？」
「実は、面倒だと思ったけど、仕方ないと諦めていた、あの作業を....」
最近、実務で、ほぼ丸一日掛りで月に１度発生する仕事を手順通りに行う事で、5分で片付けるスクリプトを作成しました。荒削りな部分はあって、700行近いスクリプトになってしまいましたが、完成にあたってポイントになった部分を振り返ってみたいと思います。
全ての処理をGASで行わず、関数が使える所は関数を使用する。GASで処理をした場合、間違いがあるかどうか気付くためには、GASが分かる人ではないと難しいという事で、指定したセルに適切な関数を埋め込むスクリプトにしました。
原型はマクロを記録するボタンで作成し、冗長に思える所は、手直しするといった具合で進めました。
使用したい前月のデータを値貼り付けする。
//1ヶ月前の日付を生成
var date = new Date();
var month = date.getMonth()+1;
date.setMonth(month-2);
var lastMonth = Utilities.formatDate(date, 'JST', 'MM月');
//データが存在する範囲を全てコピーして、値貼り付けをする
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName(lastMonth +'シート名');
var range = sheet.getDataRange();
var values = sheet.getDataRange().getValues();
range.copyTo(sheet.getDataRange(), {contentsOnly:true});
//使用したい列が決まっている場合、文字列がある最終行を取得
var alreadySetValues = sheet.getRange(1,1,sheet.getLastRow()-1).getValues();
var targetRow = alreadySetValues.filter(String).length;
var lastMonthRange = sheet.getRange(2,2,targetRow+1);
var lastMonthValues = sheet.getRange(2,2,targetRow+1).getValues();
sheet.getRange(1, 1, sheet.getLastRow()-1).getValues(); で最終行の取得は容易に出来ますが、実はこれ、シートに存在する空白行を含む最終行であって、項目名が存在する最終行ではありません。いやいや、そこじゃねぇよ！！みたいな....。
.filter(String).length; を書く事で、文字列がちゃんとある、僕が意図する最終行を取得する事が出来ます。
不要なシートはまとめて削除する
パクリました。（笑）残したいシートを指定して、不要なシートを全削除できます！！1から自力で考えるよりも似たような事を他の誰かがやろうとしていないか、有志が記事として書いてくれていないか検索してみましょう。
var sheet = spreadsheet.getSheetByName('index');
SpreadsheetApp.setActiveSheet(sheet);
spreadsheet.moveActiveSheet(1);indexというシートを選択して、左から一番目に移動させています。
var count = spreadsheet.getNumSheets();
for(var i = count; i >= 3; i--){
var sheets = spreadsheet.getSheets()[i-1];
spreadsheet.deleteSheet(sheets);
}残したいシートが2つある場合、シート番号が3以降のシートを削除していきます。残っているシート番号が2になるまで削除を繰り返し続けます。for文がこんな所でも使えるんだ！！と知った瞬間。すげぇ....。
関数を特定のセルに埋め込む
sheet.getRange(2,2).setFormula('=ARRAYFORMULA($M$2:$M-$L$2:$L)');1行目に見出しがあるイメージで、例えばB2セルに関数を埋め込みたい場合はこんな感じ。ARRAYFORMULA関数の場合は、1つのセルに埋め込めばいいだけだからめっちゃ簡単。
余談：マクロで記録すると必ずR1C1方式で記録されます。一見すると、なんじゃこりゃあって感じですが、RはRow、CはColumnと言われると、getRange(2,2)とかとやっている事が、そんなに変わらないじゃんみたいに悟りました。んーでも直感的には分かりにくいか....。
sheet.getRange(1,1).setFormulaR1C1('=QUERY('+'\''+lastMonth +'ALL\'!R[0]C[1]:C[15],"SELECT B,A,E,F,H WHERE I =\'東京都'\'")');関数の途中に、変数を挟みたいと思った時、めっちゃ苦労しました。どこからどこまでが文字列なんだか.....。そろそろ長くなってきたので、続きは続編にて。
この記事が気に入ったらサポートをしてみませんか？