Google Apps Script（以下、GAS）を初めてちゃんといじってみました。意外なほど簡単！ 備忘録も兼ねて書いておきます。
やりたいこと
備品の在庫管理→発注までをGoogleスプレッドシートでやりたいなと。４種類の備品（仮にA、B、C、D）の在庫がなくなってきたら、それぞれ何個注文するかをスプレッドシートに入力して、「送信」ボタンを押せばメール送信したい。
スプレッドシートで管理しているので、月単位・年単位で何個発注したかを帳票と突き合わせするときも楽ちんかなって思いました。
できあがったもの
こんな感じです。
最終行の内容を抽出して整形し、メール文面にしたためています。
送信するメールはこんな感じにしました。
株式会社▲▲▲▲ ●●様
いつもお世話になっております。
下記の通り■■■■をお願いします。
A：1個
C：1個
以上です。
よろしくお願いします。
コード
function sendOrderMail() { //最終行の内容を取得 var tgtSheet = SpreadsheetApp.getActiveSheet(); //シートを取得 var lastRow = tgtSheet.getDataRange().getLastRow();//最終行を取得 //発注品目と個数を入れる変数、文面用の変数 var order = {"A":0, "B":0, "C":0, "D":0}, temp = ""; var myBody = "株式会社▲▲▲▲ ●●様\n\nいつもお世話になっております。\n下記の通り■■■■をお願いします。\n\n"; //最終行から発注数を抽出 order["A"] = tgtSheet.getRange(lastRow,2).getValue(); order["B"] = tgtSheet.getRange(lastRow,3).getValue(); order["C"] = tgtSheet.getRange(lastRow,4).getValue(); order["D"] = tgtSheet.getRange(lastRow,5).getValue(); //抽出したデータから送信文字列に発注個数を追加 for (key in order) if (order[key]) temp += key + "："+order[key]+"個\n"; //送信チェック if (confirm("以下の内容で送信します。よろしいですか？\\n\\n"+temp.replace(/\n/g, "\\n"))){ //文面の最後 myBody += temp; myBody += "\n以上です。\nよろしくお願いします。" tgtSheet.getRange(lastRow,6).setValue("済"); MailApp.sendEmail("hoge@foo.jp","メールタイトル",myBody); } } function confirm(mes){ var res = Browser.msgBox(mes, Browser.Buttons.OK_CANCEL); // it returns "ok" or "cancel" if (res === "ok") return true; else return false; }
蛇足ですが、三項演算子は使えないようです。
フローチャート
ざっくりこんな感じです。
- スプレッドシートの最終行から発注したい個数を割り出す
- 発注したい備品の種類（A、B、C、D）と個数を紐付けて変数とする
- 確認のためのダイアログを出し、個数を改めて確認する
- OKが押されたら、メール送信欄に「済」を記入する
- メールを送信する
スクリプトの説明というか覚書
まずは関数の名前を決めます。これをあとから呼び出すので、わかりやすいものにしましょう。
続いて、ドキュメントオブジェクトモデルのホストとして、ExtendScriptでいえばappのような存在としてSpreadsheetAppを利用します。現在アクティブなシートを取得するにはgetActiveSheet()と、ESとは違ってメソッドを利用します*1。
var tgtSheet = SpreadsheetApp.getActiveSheet(); //シートを取得
対象となるシートを特定したら、今度はデータの最終行とその内容を調べます。
セルは無限にありますが、データが入っているエリアは限られます。その「データが入っている範囲」を取得するのがSheet.getDataRangeメソッドです。
これでデータが入っている範囲を特定できるので、そこから最終行を取得するgetLastRowメソッドで「データ範囲の最終行」を指定します。
var lastRow = tgtSheet.getDataRange().getLastRow();//最終行を取得
続いて、発注する備品の種類をkeyにした連想配列`orderを用意しました。この辺はお好みだと思いますので適当に。
さて、最終行の何列目のデータを、連想配列orderに格納するか。セルを指定し、その中身を取得するのがこれです。
order["A"] = tgtSheet.getRange(lastRow,2).getValue();
ESであればTable.cells[2].contentsとでもする感じでしょうか。こちらはもっと手っ取り早くて、getRangeメソッドに行・列と２つの引数を渡せばそのセルが取得でき、実際のセルに入力されている値はgetValueメソッドで取得できます。分かりやすい！
セルに入力されている値が取得できたら、メール文面用にちょっとだけ整形します。空だったり0が入力されていた場合はわざわざメールの文面に書く必要がないので除外したいので、こんなふうにしてみました。
for (key in order) if (order[key]) temp += key + "："+order[key]+"個\n";
この後は、ダイアログを出して送信の確認し、実際にメールを送ります。
「確認はconfirmでいいかな〜♪」とか思ってたらconfirmメソッドがない！w
仕方ないから自前でconfirmメソッドを作ったのが最下部の関数です。
function confirm(mes){ var res = Browser.msgBox(mes, Browser.Buttons.OK_CANCEL); // it returns "ok" or "cancel" if (res === "ok") return true; else return false; }
ダイアログを出すには、BrowserオブジェクトからmsgBoxメソッドを利用するようです。引数は「表示する文字列, ボタンの種類」になります。詳細はGASのドキュメントオブジェクトモデルを確認してください（後述）。
Browser.Buttons.OK_CANCELこのタイプのダイアログは、OKが押されると"ok"が、キャンセルが押されると"cancel"という文字列が返ってきます。それを判別して、関数としては真偽値を返します。使い勝手として、普段から使っているESのconfirmに倣ったような感じです。
ただここにハマりポイントがあって、普通にやったら改行ができなかったんですよね。なんでかっていうとmsgBoxに表示させる文字列に\があるときはエスケープさせる必要があるからです。知らなかったので5分くらい悩んだ。
そこで、スクリプトでこの関数を呼ぶときにreplaceメソッドを使って\nを\\nに置換しています。
temp.replace(/\n/g, "\\n"))
この部分ですね。
さぁ、ダイアログを出してOKを押しました。そしたらメール送信欄に「済」を入れましょう。セルの値を取得するgetValueがあれば、セルに値を入力するsetValueメソッドがあります。分かりやすい！
tgtSheet.getRange(lastRow,6).setValue("済");
というわけで、これで最終行の6列目に「済」を入力できます。
最後にメールの送信です。
MailApp.sendEmail("hoge@foo.jp","メールタイトル",myBody);
sendEmailメソッドには4つの引数を渡すことができ、順に「宛先, メールタイトル, メール本文, {オプション}」になります（詳しくはドキュメントオブジェクトモデルを参照してください）。今回はオプションを省いて最低限のものだけ設定しました。
ボタンの設定
メニューバーから「挿入」＞「図形描画...」から適当に図形を書きます。できた図形に対して、「スクリプトを割り当て...」をクリックし、最初に定義した関数名を入力します。今回のケースでは「sendOrderMail」です。
これでできあがりです！ ほんとうに簡単ですね。
資料
ドキュメントオブジェクトモデルは以下にあります。
Calendar Service | Apps Script | Google Developers
左にあるメニューから、どのアプリケーションのオブジェクトモデルを見たいか、ツリーを展開させれば調べることができます。
というわけで、以上が初めてのGASでした。もうちょっといろいろ遊べそうです。
*1:ESではapp.activeDocumentというふうにDocumentオブジェクトを指定できるので、この辺の感覚がちょっと違います。
