前回のGoogle Apps Script（GAS）でスプレッドシートを外部から操作するではネットショップもどきと在庫管理のデモを作成しました。在庫がなくなったら商品の補充をおこなわなければなりません。そこで在庫がF列の「しきい値」以下になったらH列の「担当者」にG列の「補充数」分の発注を要請するメールを送信するようにします。
簡単メール送信
GASなら簡単にメールを送信することができます。
|
1 2 3 4 5 6 7 |
function SendMail() { const emailAddress = "XXXXX@YYY.jp"; // 送信先のメールアドレス const subject = "テスト配信"; const body = "これはテスト配信です。\n届きましたか？"; GmailApp.sendEmail(emailAddress, subject, body); } |
在庫のチェックと不足の解消
前回作成したToBuy関数のなかで在庫が基準を下回っていれば担当者に在庫不足を解消するようにメールを送信します。
前回はToBuy関数に全部の処理を詰め込みましたが、今回は長くなるので関数に分けました。Get～関数でセルの値を取得し、Set～関数で値をセットします。
ToBuy関数
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
function ToBuy(itemID){ let spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); let sheet = spreadsheet.getSheetByName("シート1"); let value = GetStockFromItemId(sheet, itemID); if(value > 0){ DecrementStockFromItemId(sheet, itemID); SetDayFromItemId(sheet, itemID); SetTimeFromItemId(sheet, itemID); SendMailIfNeed(sheet, itemID); return true; } return false; } |
在庫の確認
商品のIDからセルの位置を取得するために必要な行を取得する関数です。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
function GetRowFromItemId(itemID){ if(itemID == "item1") return 2; else if(itemID == "item2") return 3; else if(itemID == "item3") return 4; else if(itemID == "item4") return 5; else return 0; } |
商品のIDから在庫の数を取得する関数です。
|
1 2 3 4 5 6 7 |
function GetStockFromItemId(sheet, itemID){ let row = GetRowFromItemId(itemID); if(row > 0) return sheet.getRange(row, 2).getValue(); else return 0; } |
在庫を1減らす関数です。
|
1 2 3 4 5 6 7 8 9 10 |
function DecrementStockFromItemId(sheet, itemID){ let row = GetRowFromItemId(itemID); if(row > 0){ let value = sheet.getRange(row, 2).getValue(); if(value > 0){ value--; sheet.getRange(row, 2).setValue(value); } } } |
在庫が変動したときの日付を変更する関数です。
|
1 2 3 4 5 6 7 8 |
function SetDayFromItemId(sheet, itemID){ let row = GetRowFromItemId(itemID); if(row > 0){ let date = new Date(); let day = Utilities.formatDate( date, 'Asia/Tokyo', 'yyyy-MM-dd'); sheet.getRange(row, 4).setValue(day); } } |
在庫が変動したときの時刻を変更する関数です。
|
1 2 3 4 5 6 7 8 |
function SetTimeFromItemId(sheet, itemID){ let row = GetRowFromItemId(itemID); if(row > 0){ let date = new Date(); let time = Utilities.formatDate( date, 'Asia/Tokyo', 'HH-mm-ss'); sheet.getRange(row, 5).setValue(time); } } |
担当者にメールを送信
在庫数を確認して必要であれば担当者にメールを送信する関数です。GetNumberToOrder関数は在庫がしきい値を上回っている場合は0を返します。そうでないときは補充しなければならない数を返します。
GetNumberToOrder関数が0よりも大きい数を返したときは担当者にメールを送信しますが、担当者が対応してもすぐに在庫不足が解消されるわけではありません。商品が売れるたびに何度もメールが送信されないように、メールを送信したらJ列に「済」と表示させ、これ以降はメールの送信はしません。
GetNumberToOrder関数が0を返した場合は在庫は充分存在するということなので、その場合はJ列には「不要」と表示させます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
function SendMailIfNeed(sheet, itemID) { if(GetNumberToOrder(sheet, itemID) > 0){ if(GetCheckFromItemId(sheet, itemID) == "済"){ console.log("在庫不足：送信不要"); return; } const subject = "発注依頼"; let value = GetNumberToOrder(sheet, itemID); let name = GetManagerNameFromItemId(sheet, itemID); let emailAddress = GetMailAddressFromItemId(sheet, itemID); let itemName = GetItemNameFromItemId(sheet, itemID); const body = name + "さん。" + itemName + "を" + value + " 発注してください。"; GmailApp.sendEmail(emailAddress, subject, body); SetCheckFromItemId(sheet, itemID, "済"); console.log("在庫不足：送信完了"); } else{ SetCheckFromItemId(sheet, itemID, "不要"); console.log("在庫充分"); } } |
GetNumberToOrder関数は、在庫が不足したときに必要な数を返します。
|
1 2 3 4 5 6 7 8 9 10 11 |
function GetNumberToOrder(sheet, itemID){ let row = GetRowFromItemId(itemID); if(row > 0) { if(sheet.getRange(row, 2).getValue() - sheet.getRange(row, 6).getValue() > 0) return 0; else return sheet.getRange(row, 7).getValue(); } else return 0; } |
GetCheckFromItemId関数はJ列に書かれている文字列を取得し、SetCheckFromItemId関数はJ列に文字列を設定します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
function GetCheckFromItemId(sheet, itemID, str){ let row = GetRowFromItemId(itemID); if(row > 0) return sheet.getRange(row, 10).getValue(); else return ""; } function SetCheckFromItemId(sheet, itemID, str){ let row = GetRowFromItemId(itemID); if(row > 0) sheet.getRange(row, 10).setValue(str); } |
GetItemNameFromItemId関数は商品のIDから商品名を取得します。
|
1 2 3 4 5 6 7 |
function GetItemNameFromItemId(sheet, itemID){ let row = GetRowFromItemId(itemID); if(row > 0) return sheet.getRange(row, 1).getValue(); else return ""; } |
GetManagerNameFromItemId関数は商品のIDからメールを送信する相手の名前を取得します。
|
1 2 3 4 5 6 7 |
function GetManagerNameFromItemId(sheet, itemID){ let row = GetRowFromItemId(itemID); if(row > 0) return sheet.getRange(row, 8).getValue(); else return ""; } |
GetManagerNameFromItemId関数は商品のIDからメールを送信する相手のメールアドレスを取得します。
|
1 2 3 4 5 6 7 |
function GetMailAddressFromItemId(sheet, itemID){ let row = GetRowFromItemId(itemID); if(row > 0) return sheet.getRange(row, 9).getValue(); else return ""; } |
在庫の回復
さて担当者が対応をした場合、しばらくすると在庫不足が解消されます。この部分は2時間おきにJ列を確認して、在庫不足になっていたら補充数だけ在庫を増やすことにします。
RecoverInventoryAll関数はすべての在庫不足を解消します。
|
1 2 3 4 5 6 7 8 |
function RecoverInventoryAll(){ let spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); let sheet = spreadsheet.getSheetByName("シート1"); RecoverInventory(sheet, "item1"); RecoverInventory(sheet, "item2"); RecoverInventory(sheet, "item3"); RecoverInventory(sheet, "item4"); } |
RecoverInventory関数は指定された商品IDの在庫不足を解消します。そしてJ列の文字列を対応済みから対応不要に変更します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
function RecoverInventory(sheet, itemID){ let row = GetRowFromItemId(itemID); if(row > 0){ if(sheet.getRange(row, 10).getValue() == "済"){ sheet.getRange(row, 10).setValue("不要"); let old = sheet.getRange(row, 2).getValue(); let plus = sheet.getRange(row, 7).getValue(); sheet.getRange(row, 2).setValue(old + plus); SetDayFromItemId(sheet, itemID); SetTimeFromItemId(sheet, itemID); } } } |
トリガーを設定するには左のメニュー [トリガー]を選択します。時計のようなアイコンの部分です。
あとは[実行する関数を選択]を[]、実行するデプロイを選択は[Head]、[イベントのソースを選択]は[時間主導型]、[時間ベースのトリガーのタイプを選択]は[時間ベースのタイマー]、[時間の間隔を選択（時間）]は[2時間おき]を選択します。
