※本稿は連載の第4回目です［最初から読む］
今回は、スプレッドシートを使った在庫の監視を2段階でパワーアップしていきます。
- 条件付き書式による「見た目でわかるアラート設定」
在庫数をリアルタイムで色分けし、注意が必要な商品をひと目で把握できるようにします。 - Gmail による「自動メール通知の仕組み」
在庫が一定数以下になったタイミングで、自動的にメール通知を送る仕組みを構築。件名や本文のカスタマイズ、通知頻度の制御も可能です。
まずは「見た目」のアラートを整え、次に「通知」の自動化へとステップを踏んでいきます。
条件付き書式で背景色を変更
QUERY関数で在庫一覧を作成
第3回 で使用したQUERY関数を使って、商品別の在庫一覧（商品名、在庫数）を作成します。 以下のようなQUERY関数を、例としてE2セルに入力します。
=QUERY(
{
IMPORTRANGE($B$3, $B$4&"!C:G"),
ArrayFormula(IF(IMPORTRANGE($B$3, $B$4&"!F:F")="入庫", VALUE(IMPORTRANGE($B$3, $B$4&"!E:E")), 0)),
ArrayFormula(IF(IMPORTRANGE($B$3, $B$4&"!F:F")="出庫", VALUE(IMPORTRANGE($B$3, $B$4&"!E:E")), 0))
},
"SELECT Col2, SUM(Col6)-SUM(Col7)
WHERE Col3 is not null
GROUP BY Col2
LABEL SUM(Col6)-SUM(Col7) '在庫数'",
1
)Ｂ３セル：入出庫データがあるスプレッドシートの URL
Ｂ４セル：該当のシート名
結果：Ｅ列に商品名、Ｆ列に在庫数が表示されます
条件付き書式の設定（在庫が10以下で黄色背景）
表示形式メニューで「条件付き書式」を選択します。
- 範囲：E3:F1000（商品名と在庫数の範囲）
- ルールタイプ：カスタム数式 「=AND($F3<=10, $F3<>””)」
F列＝在庫数が「10以下かつ空でない」セルにのみ書式を適用します。
”$” をつけることで列は固定、行は相対移動しながら適用されます。 - 書式スタイル：背景色を黄色に設定
このように設定すると、在庫数一覧は以下のように色付けされ、ひと目で在庫が少なくなっているものが分かります。
在庫が3以下になったら、さらに強調
同じ範囲 E3:F1000 に対して、以下のルールを追加します。
- 範囲：E3:F1000（商品名と在庫数の範囲）
- ルールタイプ：カスタム数式 「=AND($F3<=3, $F3<>””)」
- 書式スタイル：背景色を赤色に設定
ルールの適用順序を「3以下（赤）」→「10以下（黄）」の順に変更してください。
一覧表に複数の色付きの「在庫アラート」が反映され、瞬時に状況が把握できるようになります。
在庫不足を自動メール通知
一覧を見るだけでは、重要な変化を見逃してしまうこともあります。特に一覧を常にチェックしていなければ、気付かないリスクが高いですよね。そこで、Gmailで自動的に通知を飛ばす方法を2通りご紹介します。
スプレッドシートの「条件付き通知」機能を活用
「ツール」メニューから「条件付き通知」を選び、設定は以下の手順で行います。
- 条件：セルの変更時 → Ｆ列（＝在庫数）に変更がある
- 条件追加：「以下 10」
- 通知先にメールアドレスを入力
これで、在庫数が10以下になると、登録したメールアドレスに自動で通知メールが届くようになります。
件名や本文はシステムによって固定されており、カスタマイズ性は限られますが、まずは在庫不足の検知に使える手軽な仕組みとして役立ちます。
この機能で十分な場合もありますが、件名や本文をもっと柔軟に変更したい場合は、Google Apps Scriptを使って通知のカスタマイズをしましょう。
Apps Scriptを使って柔軟な自動メール通知
Google Apps Scriptを使えば、次のような柔軟な条件を設定できます。
- 件名／本文を自由にカスタマイズ
- 在庫 10 以下 → 担当者に通知
- 在庫 3 以下 → 管理者に通知
- 初回の通知なのか、初回に通知をしてから3日以上経過しているかどうか
スクリプト内で「通知済みかどうか」を記録できるため、無駄な重複通知を防ぐ設計も可能です - メール通知のログをとる
- 定時（例：毎朝1回）にチェック＆通知
// ——————————————
// 担当者には、閾値WARNING 以下の場合通知
// 管理者には、閾値CRITICAL 以下の場合通知
// 設定
// > シートの名称およびヘッダ情報
// >> （在庫一覧）
const SHEET_NAME_LIST_STOCK = "在庫一覧シート";
// >> （メール通知ログ履歴）
const SHEET_NAME_LOG_SENDMAIL = "ログ_メール通知履歴シート";
const HEADERS_NAME_LOG_SENDMAIL = ['送信日時', '送信先', '区分（MANAGER or ADMIN）', '件名', '本文', '送信者'];
// >> （商品別の初回通知時刻履歴）
const SHEET_NAME_LOG = "ログ_商品別初回通知時刻履歴シート";
const HEADERS_NAME_LOG = ['商品', '区分（MANAGER or ADMIN）', '初回通知日時'];
// > アラートの閾値
const CELL_THRESHOLD = {WARNING: 'B10', CRITICAL: 'B11'};//在庫一覧シートの セルに入力 WARNINGには10、CRITICALには3をセット
// > メール送信先
// >> （担当者（注意＋警告のみ送信））
const CELL_EMAIL_TO_MANAGER = 'B6';//在庫一覧シートの セルに入力
// >> （管理者（警告メールのみ送信））
const CELL_EMAIL_TO_ADMIN = 'B7';//在庫一覧シートの セルに入力
// > アラート情報補完
let Alerts = [];
// ——————————————
// 在庫チェック＆通知
function checkInventoryAndNotify() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
// ●在庫情報の取得
const sheet = ss.getSheetByName(SHEET_NAME_LIST_STOCK);
// E列（5列目）：商品名、F列（6列目）：入庫合計、G列（7列目）：出庫合計、H列（8列目）：在庫数
let data = sheet.getRange(3, 5, sheet.getLastRow()-1, 8).getValues();//sheet.getRange(1, 1); // A1セル
// getRange(row, column, numRows, numColumns) — 複数セル指定 sheet.getRange(2, 3, 6, 4); // C2:F7 の範囲
Logger.log(data);
// ●オブジェクト配列に変換
data = data
.filter(([itemName, , , numStock]) =>
itemName?.trim() && // itemName が空でなく、
!isNaN(numStock) && // numStock が数値（NaNでない）か？
numStock !== '' // 空文字列ではないか？
)
.map(
([itemName, numIn, numOut, numStock]) => ({itemName,numIn, numOut, numStock})
);
Logger.log(data);
// ●初回通知時刻ログ
const logSheet = _ensureSheet(ss, SHEET_NAME_LOG, HEADERS_NAME_LOG);
const logMap = _buildLogMap(logSheet, HEADERS_NAME_LOG);
// ●アラート情報の初期化
_resetAlert();
// ●アラート情報のセット
data.forEach((d) => {
// CRITICAL
if (d.numStock <= (sheet.getRange(CELL_THRESHOLD.CRITICAL).getValue() ?? 0) ) {
_setAlert("CRITICAL", d);
// WARNING
} else if (d.numStock <= (sheet.getRange(CELL_THRESHOLD.WARNING).getValue() ?? 0) ) {
_setAlert("WARNING", d);
}
});
//通知
if (Alerts.length > 0) {
// 担当者へのメール
_sendEmailAlert('MANAGER', ['CRITICAL', 'WARNING'], logMap);
// 管理者へのメール
_sendEmailAlert('ADMIN', ['CRITICAL'] , logMap);
}
}
// ——————————————
// メール送信
function _sendEmailAlert(kbn, levels, logMap) {
// 処理日時の取得
const now = new Date();
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName(SHEET_NAME_LIST_STOCK);
// 対象の通知情報を取得
const mailAlerts = Alerts.filter(obj => levels.includes(obj.level));
if (!mailAlerts || mailAlerts.length == 0 ) {
return;
}
// 閾値
const thresholdMaxLevel = levels.reduce((bestLevel, level) => {
// 初期値 bestLevel が null のときは level を採用
if (bestLevel === null) return level;
const levelThreshold = sheet.getRange(CELL_THRESHOLD[level]).getValue() ?? 0;
const bestLevelThreshold = sheet.getRange(CELL_THRESHOLD[bestLevel]).getValue() ?? 0;
return levelThreshold > bestLevelThreshold ? level : bestLevel;
}, null);
const threshold = sheet.getRange(CELL_THRESHOLD[thresholdMaxLevel]).getValue() ?? 0;
// 送信先メールアドレス
const emailTo = sheet.getRange(kbn == 'ADMIN' ? CELL_EMAIL_TO_ADMIN : CELL_EMAIL_TO_MANAGER).getValue() ?? "";
if (!emailTo || emailTo == "") {
return;
}
const maxSubjectItemCnt = 1;
let subject = `入出庫管理【在庫アラート】【` + mailAlerts.length + `件】`;
let body = `在庫数 が しきい値 ${threshold} 以下 のものがあります。\n`;
mailAlerts.forEach((d, idx, array) => {
// > タイトル
if ( idx == 0 ) {
subject += d.itemName;
} else if ( idx < maxSubjectItemCnt ) {
subject += `, `+ d.itemName;
} else if ( idx == maxSubjectItemCnt ) {
subject += ` 他`;
}
// > 注釈
let note = "";
const loggedDate = logMap[d.itemName] && logMap[d.itemName][kbn] ? logMap[d.itemName][kbn] : false;
if (!loggedDate) {
note = "※NEW";
} else {
const diffDays = Math.floor((now - loggedDate)/(1000*3600*24));
if (diffDays >= 3) note = "※３日以上経過";
}
// > 本文
body += `\n・【${d.itemName}】在庫数： ${d.numStock} ${note}`;
});
// メール送信
GmailApp.sendEmail(emailTo, subject, body);
// メール送信ログ記載
_writeLogSendEmail(emailTo, subject, body, kbn);
// 初回通知時刻ログ更新
const logSheet = _ensureSheet(ss, SHEET_NAME_LOG, HEADERS_NAME_LOG);
_remakeLog(logSheet, HEADERS_NAME_LOG, kbn, mailAlerts ,now);
}
// ——————————————
// メール送信ログ記載
function _writeLogSendEmail( emailTo, subject, body, kbn){
const ss = SpreadsheetApp.getActiveSpreadsheet();
const maillogSheet = _ensureSheet(ss, SHEET_NAME_LOG_SENDMAIL, HEADERS_NAME_LOG_SENDMAIL);
// 記録 データ領域の最後に行を追加
const from = Session.getActiveUser().getEmail();
maillogSheet.appendRow([
Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd HH:mm:ss'), //new Date(),
emailTo,
kbn,
subject,
body,
from
]);
}
// ——————————————
// 初回通知時刻ログより、既通知品を日付付きでマップ化
function _buildLogMap(sheet, headers, checkKbn="") {
// データ行は２行目から
if (sheet.getLastRow() <= 1 ) {
return {};
}
return sheet.getRange(2, 1, sheet.getLastRow()-1, headers.length).getValues()
.reduce((m, [itemName, kbn, date]) => {
if ( checkKbn != "" && checkKbn != kbn ) return m;
if (itemName && kbn && date instanceof Date) {
if (m[itemName]) {
m[itemName][kbn] = date;
} else {
m[itemName] = {};
m[itemName][kbn] = date;
}
// m[itemName][kbn] = date;
}
return m;
}, {});
}
// Alert情報より初回通知時刻ログの書き換え
function _remakeLog(sheet, headers, kbn, mailAlerts, now) {
// Alertsに設定されていない場合はログから削除
const data = sheet.getDataRange().getValues();
for (let r = 1; r < data.length; r++) {
if (data[r][1] == kbn) {
const exists = mailAlerts.some(alert => alert.itemName === (data[r][0] ?? "") );
if (!exists) {
sheet.deleteRow(r+1);
}
}
}
// Alertsに設定されているものは、なければ追加
const map = _buildLogMap(sheet, headers, kbn);
mailAlerts.forEach((alert, idx, array) => {
Logger.log(alert, kbn);
if ( !map[alert.itemName] || !map[alert.itemName][kbn]) {
Logger.log([alert.itemName, kbn, now]);
sheet.appendRow([alert.itemName, kbn, now]);
}
});
}
// ——————————————
// シートがなければ確保する
function _ensureSheet(ss, sheetName, headers) {
let sheet = ss.getSheetByName(sheetName);
if (!sheet) {
sheet = ss.insertSheet(sheetName);
}
// ヘッダ行がない場合は追加
const firstRow = sheet.getRange(1, 1, 1, headers.length).getValues()[0];
if (!firstRow.every((val, idx) => val === headers[idx])) {
sheet.insertRowBefore(1);
sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
}
return sheet;
}
// ——————————————
// アラート情報の初期化
function _resetAlert() {
Alerts = [];
}
// アラート情報のセット
function _setAlert(level, obj) {
// レベル
obj.level = level;
// アラート情報にセット
Alerts?.push(obj);
}このスクリプトに「時間主導トリガー（例：1日1回）」を設定することで、自動メール通知ができ、在庫を自動で監視し続けられます。
まとめ
- 一覧に色付けをすることで視認性がアップ、ひと目で要注意アイテムが分かる
- 条件付き通知で簡易アラートが実現できる
- Google Apps Script で柔軟かつ高度な通知処理ができる
最後に一言
本連載を通して、Google Workspaceだけで、リアルタイムに、かつ手軽に在庫管理ができる手ごたえを感じていただけたかと思います。
- バーコード読み取りによる自動化
- 発注依頼書や納品書などの帳表自動作成
- 月別レポートのPDF化
などなど、今回の機能をベースに、さらに便利な使い方に広げることも可能です。
「気になる！」という方は、いつでもお気軽にお問合せください。
また、在庫管理に限らず、
「もっと便利にしたい」
「紙やExcelから脱却したい」
「こんなことはできないかな？」
といった思いをお持ちの方へーーあなたの “できるかも！” を、全力でサポートいたします！
在庫管理の新しいカタチ の記事一覧
- 第1回：紙やExcelから卒業！Googleだけでできる、在庫管理の新しいカタチ
- 第2回：フォームで入出庫記録、スプレッドシートで自動集計！Googleだけでできる在庫管理の第一歩
- 第3回：自動集計と在庫一覧をスプレッドシートで可視化！記録を“見える化”して活かす仕組み
- 第4回：Gmailで在庫不足を自動通知！Apps Scriptでアラートを飛ばす方法
コメント