大ピンチ！！熟練のパートさんが長期間来れなくなってしまった！？

農産担当者でなくても発注できる仕組みを作りたい——スプレッドシートとGASで作る農産発注支援ツール

ちょっと自慢

9月16日に着任し、現場の課題を把握。19日にはプロトタイプを完成させました。わずか4日で、現場入力から発注判断までを支援するツールの運用を開始。手前味噌ですが、このスピード感はちょっと自慢です😎

※本記事は完成したシステムの紹介ではなく、現場で使える形を探しながら作っている試作過程の記録です。

---

現場で起きていたこと、課題

異動先の店舗では、欠員の影響で、農産出身の店長が発注判断を行い、私や応援者が売場作業を担当する場面がありました。応援者は皆、担当部門は様々です。

発注者と、実際に売場や冷蔵庫を確認する人が分かれている状態です。

そのため、発注前には次のような情報を伝える必要がありました。

• 売場在庫
• 冷蔵庫在庫
• 加工原料の残数
• 欠品しそうな商品
• 売場で多すぎる、または少なすぎる商品

この情報を毎回メモして伝える方法では、確認する人にも発注する人にも負担がかかります。

また、書き方や確認項目が人によって異なるため、必要な情報が抜ける可能性もあります。

問題は、「農産発注が難しいこと」だけではありません。

発注判断に必要な情報が整理されず、店長の頭の中や手書きメモに分散していることが大きな課題でした。

---

最初に決めた対象品目

最初から農産の商品すべてを対象にすると、マスタ作成や換算設定が大きくなりすぎます。

そこで、まずは加工や原料換算が必要な次の5品目から試作することにしました。

• 白菜
• キャベツ
• レタス
• 大根
• かぼちゃ

ただし、運用を始めると対象品目が増えることは明らかです。

そのため、5品目だけを固定表示するのではなく、マスタにない野菜名も手入力で追加できる設計に変更しました。

最初から完璧な商品マスタを作るのではなく、現場で必要になった品目を追加できる余白を残しています。

---

発注量を考えるために必要な情報

発注量を計算するため、必要な情報を次のように整理しました。

現場で入力する情報

• 品目名
• 売場在庫
• 冷蔵庫在庫
• 原料在庫
• 必要に応じた補足情報

マスタや実績から取得したい情報

• 基準在庫
• 販売点数
• 原料1ケースから商品化できる個数
• 発注単位
• 商品名
• JANコード(商品名末尾に下3桁を入力で識別・例：スプラウト123)

発注の基本的な考え方は、次の形です。

必要数 ＝ 販売見込み数 ＋ 基準在庫 － 現在庫

ただし、農産では「ケース数」と「商品数」がそのまま一致しません。

例えば、原料1ケースから何個の商品を作れるかによって、実際に必要な発注ケース数が変わります。
(写真の白菜のように、1株から白菜1/4が4点。)

そこで、原料ごとの換算数をマスタに持たせ、最終的には次のように発注提案数を計算する設計にしました。

発注提案数 ＝
（販売見込み数 ＋ 基準在庫 － 売場在庫 － 冷蔵庫在庫）
÷ 原料換算数

計算結果は、そのまま発注数として確定するのではなく、発注判断を支援する参考値として扱います。

---

最初はAppSheetを考えた

現場でスマートフォンから入力するため、最初はAppSheetを使うことを考えました。

AppSheetには、次のメリットがあります。

• スマートフォン用の入力画面を作りやすい
• スプレッドシートと連携できる
• 写真やバーコードを扱える
• 入力履歴を残せる
• ノーコードで試作できる

一方、今回必要なのは、単なる在庫記録アプリではありません。

入力された在庫と販売実績、基準在庫、原料換算を組み合わせて、発注提案数を表示する必要があります。

また、品目の追加や計算方法の変更も、試しながら頻繁に行うことが予想されました。

そこで、入力画面と計算処理を自由に変更しやすい方法として、Google Apps Script（GAS）でスマートフォン用のWebアプリを作る方向へ変更しました。

---

AppSheetではなくGASを選んだ理由

AppSheetが使えないという判断ではありません。

今回の試作段階では、次の理由からGASのほうが適していると考えました。

1. 計算ロジックを自由に変更できる
2. スプレッドシートのマスタと直接連携できる
3. 入力画面に必要な項目だけを表示できる
4. 発注提案結果の表示方法を調整しやすい
5. 将来、販売実績データとの連携を追加しやすい

ツール選定で重要だったのは、機能の多さではありません。

現在の試作段階で、現場の変更に追従できるかどうかでした。

発注の組み立てがまだ固まっていない段階で大きな仕組みを作ると、現場で使いながら修正することが難しくなります。

そのため、まずスプレッドシートでデータ構造と計算を作り、GASで最低限の入力画面を用意する構成にしました。

サンプルコード

const CONFIG = Object.freeze({
  ITEM_SHEET: '品目マスタ',
  LOG_SHEET: '発注判断',
  SPREADSHEET_ID_KEY: 'SPREADSHEET_ID',
});

const ITEM_HEADERS = [
  '品目ID', '品目名', '原料単位', '1原料あたり商品数',
  '標準基準在庫', '発注単位', '使用中', '備考',
];

const LOG_HEADERS = [
  '記録ID', '記録日時', '対象日', '野菜名', '品目ID', '売れ数目安', '基準在庫',
  '売場在庫', '製品冷蔵在庫', '製造必要数', '1原料あたり商品数',
  '必要原料数', '原料冷蔵在庫', '入荷予定原料数', '発注単位',
  '発注提案数', '判断', 'メモ',
];

function doGet() {
  return HtmlService.createHtmlOutputFromFile('Index')
    .setTitle('農産 発注判断')
    .addMetaTag('viewport', 'width=device-width, initial-scale=1');
}

/**
 * スプレッドシートに紐づけた状態で最初に1回実行する。
 * シートと見出しを整え、Webアプリ用にスプレッドシートIDを保存する。
 */
function setup() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  if (!ss) throw new Error('GoogleスプレッドシートからApps Scriptを開いて実行してください。');

  PropertiesService.getScriptProperties().setProperty(CONFIG.SPREADSHEET_ID_KEY, ss.getId());
  const itemSheet = ensureSheet_(ss, CONFIG.ITEM_SHEET, ITEM_HEADERS);
  const logSheet = ss.getSheetByName(CONFIG.LOG_SHEET) || ss.insertSheet(CONFIG.LOG_SHEET);

  if (itemSheet.getLastRow() === 1) {
    itemSheet.getRange(2, 1, 5, ITEM_HEADERS.length).setValues([
      ['ITEM001', '白菜', '株', 4, '', 1, true, '1株から4商品。基準在庫を入力'],
      ['ITEM002', 'キャベツ', '玉', '', '', 1, true, '商品化数と基準在庫を入力'],
      ['ITEM003', 'レタス', '玉', '', '', 1, true, '商品化数と基準在庫を入力'],
      ['ITEM004', '大根', '本', '', '', 1, true, '商品化数と基準在庫を入力'],
      ['ITEM005', 'かぼちゃ', '玉', '', '', 1, true, '商品化数と基準在庫を入力'],
    ]);
  }
  upgradeLogSheet_(logSheet, itemSheet);
  return '初期設定が完了しました。';
}

function getItems() {
  const sheet = getSpreadsheet_().getSheetByName(CONFIG.ITEM_SHEET);
  if (!sheet || sheet.getLastRow() < 2) return [];

  return sheet.getRange(2, 1, sheet.getLastRow() - 1, ITEM_HEADERS.length)
    .getValues()
    .filter(row => row[0] && row[1] && row[6] !== false)
    .map(row => ({
      id: String(row[0]),
      name: String(row[1]),
      unit: String(row[2] || ''),
      yieldPerRaw: nullableNumber_(row[3]),
      defaultBaseStock: nullableNumber_(row[4]),
      orderUnit: nullableNumber_(row[5]) || 1,
      note: String(row[7] || ''),
    }));
}

function calculateDecision(payload) {
  const item = findItem_(payload.itemId);
  return calculate_(payload, item);
}

function saveDecision(payload) {
  const item = findItem_(payload.itemId);
  const result = calculate_(payload, item);
  const lock = LockService.getScriptLock();
  lock.waitLock(10000);
  try {
    const now = new Date();
    const sheet = getSpreadsheet_().getSheetByName(CONFIG.LOG_SHEET);
    sheet.appendRow([
      Utilities.getUuid(), now, now, item.name, item.id,
      result.salesEstimate, result.baseStock, result.salesFloorStock,
      result.finishedColdStock, result.productionNeeded, item.yieldPerRaw,
      result.requiredRaw, result.rawColdStock, result.incomingRaw,
      item.orderUnit, result.orderSuggestion, result.judgment,
      String(payload.memo || '').trim(),
    ]);
  } finally {
    lock.releaseLock();
  }
  return result;
}

function addItem(payload) {
  const name = String(payload.name || '').trim();
  const unit = String(payload.unit || '').trim();
  const yieldPerRaw = requiredPositiveNumber_(payload.yieldPerRaw, '1原料あたり商品数');
  const defaultBaseStock = requiredNonNegativeNumber_(payload.defaultBaseStock, '標準基準在庫');
  const orderUnit = requiredPositiveNumber_(payload.orderUnit, '発注単位');
  if (!name) throw new Error('品目名を入力してください。');
  if (!unit) throw new Error('原料単位を選んでください。');

  const ss = getSpreadsheet_();
  const sheet = ss.getSheetByName(CONFIG.ITEM_SHEET);
  const existing = getItems().find(item => item.name.toLowerCase() === name.toLowerCase());
  if (existing) throw new Error('同じ品目名がすでに登録されています。');

  const id = `ITEM-${Utilities.getUuid().slice(0, 8).toUpperCase()}`;
  const lock = LockService.getScriptLock();
  lock.waitLock(10000);
  try {
    sheet.appendRow([id, name, unit, yieldPerRaw, defaultBaseStock, orderUnit, true, String(payload.note || '').trim()]);
  } finally {
    lock.releaseLock();
  }
  return { id, items: getItems() };
}

function calculate_(payload, item) {
  if (!item.yieldPerRaw || item.yieldPerRaw <= 0) {
    throw new Error(`${item.name}の「1原料あたり商品数」を品目マスタに入力してください。`);
  }
  const salesEstimate = requiredNonNegativeNumber_(payload.salesEstimate, '売れ数目安');
  const baseStock = requiredNonNegativeNumber_(payload.baseStock, '基準在庫');
  const salesFloorStock = requiredNonNegativeNumber_(payload.salesFloorStock, '売場在庫');
  const finishedColdStock = requiredNonNegativeNumber_(payload.finishedColdStock, '製品冷蔵在庫');
  const rawColdStock = requiredNonNegativeNumber_(payload.rawColdStock, '原料冷蔵在庫');
  const incomingRaw = requiredNonNegativeNumber_(payload.incomingRaw, '入荷予定原料数');

  const productionNeeded = Math.max(0, salesEstimate + baseStock - salesFloorStock - finishedColdStock);
  const requiredRaw = Math.ceil(productionNeeded / item.yieldPerRaw);
  const shortage = Math.max(0, requiredRaw - rawColdStock - incomingRaw);
  const orderSuggestion = Math.ceil(shortage / item.orderUnit) * item.orderUnit;
  let judgment = 'そのまま';
  if (orderSuggestion > 0) judgment = '足す';
  else if (rawColdStock + incomingRaw > requiredRaw + item.orderUnit) judgment = '減らす';

  return {
    itemName: item.name,
    unit: item.unit,
    salesEstimate,
    baseStock,
    salesFloorStock,
    finishedColdStock,
    productionNeeded,
    yieldPerRaw: item.yieldPerRaw,
    requiredRaw,
    rawColdStock,
    incomingRaw,
    orderUnit: item.orderUnit,
    orderSuggestion,
    judgment,
  };
}

function findItem_(itemId) {
  const item = getItems().find(row => row.id === String(itemId || ''));
  if (!item) throw new Error('品目を選択してください。');
  return item;
}

function getSpreadsheet_() {
  const id = PropertiesService.getScriptProperties().getProperty(CONFIG.SPREADSHEET_ID_KEY);
  if (!id) throw new Error('先にApps Script画面で setup() を実行してください。');
  return SpreadsheetApp.openById(id);
}

function ensureSheet_(ss, name, headers) {
  const sheet = ss.getSheetByName(name) || ss.insertSheet(name);
  const current = sheet.getRange(1, 1, 1, headers.length).getValues()[0];
  if (current.join('|') !== headers.join('|')) {
    sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  }
  sheet.setFrozenRows(1);
  return sheet;
}

/**
 * 旧版の「品目IDだけ」の履歴を、野菜名が見える新版へ安全に移行する。
 * 表示は A:D と P:R のみにし、中間計算 E:O を非表示にする。
 */
function upgradeLogSheet_(logSheet, itemSheet) {
  if (logSheet.getLastRow() === 0) {
    logSheet.getRange(1, 1, 1, LOG_HEADERS.length).setValues([LOG_HEADERS]);
  } else {
    const headerWidth = Math.max(logSheet.getLastColumn(), LOG_HEADERS.length);
    const currentHeaders = logSheet.getRange(1, 1, 1, headerWidth).getValues()[0];
    const hasItemName = currentHeaders.includes('野菜名');
    const oldLayout = currentHeaders[3] === '品目ID' && !hasItemName;
    if (oldLayout) logSheet.insertColumnBefore(4);
    logSheet.getRange(1, 1, 1, LOG_HEADERS.length).setValues([LOG_HEADERS]);
  }

  const itemNameById = {};
  if (itemSheet.getLastRow() >= 2) {
    itemSheet.getRange(2, 1, itemSheet.getLastRow() - 1, 2).getValues()
      .forEach(row => { if (row[0]) itemNameById[String(row[0])] = String(row[1] || ''); });
  }
  if (logSheet.getLastRow() >= 2) {
    const ids = logSheet.getRange(2, 5, logSheet.getLastRow() - 1, 1).getValues();
    const existingNames = logSheet.getRange(2, 4, logSheet.getLastRow() - 1, 1).getValues();
    const names = ids.map((row, index) => [existingNames[index][0] || itemNameById[String(row[0])] || '未登録品目']);
    logSheet.getRange(2, 4, names.length, 1).setValues(names);
  }

  logSheet.setFrozenRows(1);
  logSheet.setFrozenColumns(4);
  logSheet.showColumns(1, Math.min(18, logSheet.getMaxColumns()));
  logSheet.hideColumns(5, 11); // E:O（品目ID～発注単位）
  logSheet.autoResizeColumns(1, 4);
  logSheet.autoResizeColumns(16, 3);
}

function nullableNumber_(value) {
  if (value === '' || value === null || typeof value === 'undefined') return null;
  const number = Number(value);
  return Number.isFinite(number) ? number : null;
}

function requiredNonNegativeNumber_(value, label) {
  const number = Number(value);
  if (!Number.isFinite(number) || number < 0) throw new Error(`${label}は0以上の数字で入力してください。`);
  return number;
}

function requiredPositiveNumber_(value, label) {
  const number = Number(value);
  if (!Number.isFinite(number) || number <= 0) throw new Error(`${label}は0より大きい数字で入力してください。`);
  return number;
}

本記事のコードおよびデータは、公開用に一般化したサンプルです。実際の店舗情報・在庫数・発注実績などは含みません。

---

現在の構成

現在検討している構成は、次のとおりです。

売場・冷蔵庫を確認
        ↓
スマートフォンのGAS画面へ入力
        ↓
スプレッドシートへ記録
        ↓
基準在庫・販売点数・原料換算数と照合
        ↓
発注提案数を表示
        ↓
事務所の「生鮮MD」で正式発注

GASの画面から、会社の発注システムへ直接発注するわけではありません。

現場ではスマートフォンを使って在庫を確認し、事務所へ戻ってから、パソコンの発注サイト「生鮮MD」に正式な発注数を入力します。

この運用にした理由は、会社の正式な発注システムを変更せず、その直前にある情報収集と判断部分だけを支援するためです。

---

スプレッドシートの役割

スプレッドシートは、大きく分けて次の役割を持たせます。

1. 品目マスタ

• 品目名
• 1原料当たりの商品数
• 基準在庫
• 発注単位

2. 現場入力データ

• 品目名
• 売れ数目安
• 売場在庫
• 冷蔵庫在庫
• 原料在庫

3. 発注提案・履歴

• 販売見込み数
• 現在庫
• 発注提案数
• 足すor減らす

入力用の列と計算用の列を同じ画面にすべて表示すると、確認しづらくなります。

そのため、計算に使用する中間列は非表示にし、通常は次の情報だけを確認できる形を目指しました。

• 野菜名
• 発注提案数
• 実際の発注数

計算過程は残しつつ、利用者には必要な結果だけを見せる設計です。

---

JANコードは読み取るのか、手入力にするのか

途中で、JANコードをスマートフォンのカメラで読み取る機能も検討しました。

JAN読み取りには、次のメリットがあります。

• 商品の選択ミスを減らせる
• 商品名を自動表示できる
• 販売実績と結びつけやすい

一方、今回対象としている農産商品には、原料と商品化後のJANが一致しないケースがあります。

例えば、1ケースの原料から複数の商品を作る場合、原料の在庫確認と販売点数を単純にJANコードだけで結びつけることはできません。

そのため、最初の段階ではJAN読み取りを必須にせず、品目を選択または手入力できる形を優先しました。

販売点数との連携

在庫だけを見て発注しても、売れる量が分からなければ発注提案にはなりません。

そこで、販売実績を記録するシートを追加し、JANコードや商品名を使って品目マスタと結びつけることを考えました。

ただし、ここでも農産特有の問題があります。

原料1種類から複数の商品が作られる場合、1つのJANコードだけを見ても、その原料全体の販売量は分かりません。

そのため、将来的には次のような対応表が必要になります。

原料	販売商品	JANコード	原料使用量
キャベツ	キャベツ1玉	JAN-A	1玉
キャベツ	キャベツ1/2	JAN-B	0.5玉
キャベツ	キャベツ1/4	JAN-C	0.25玉

この対応表を使えば、それぞれの販売点数を原料使用量へ換算できます。

原料使用量 ＝ 販売点数 × 商品ごとの原料使用量

この換算結果を合計することで、原料として何ケース必要だったかを計算できます。

単純な販売点数ではなく、商品化後の販売数を原料単位へ戻すことが、農産発注では重要になります。

---

自動化しすぎない

このツールの目的は、発注者を不要にすることではありません。

天候、気温、売場変更、特売、催事、品質、納品状況など、数値だけでは判断できない要素があるためです。

また、農産の商品化作業には熟練が必要です。

ツールを使って発注数を表示できても、その数量を商品化できる人員や作業時間がなければ、在庫や廃棄を増やす可能性があります。

そのため、今回の設計では次の役割分担を意識しています。

ツールが担当すること

• 確認項目を統一する
• 在庫情報を記録する
• 販売実績と結びつける
• 原料換算を行う
• 発注数の目安を提示する
• 発注履歴を残す

人が担当すること

• 商品の品質を確認する
• 天候や催事を反映する
• 作業量と人員を判断する
• 売場の変化を捉える
• 最終的な発注数を決定する

判断をすべて機械へ渡すのではなく、判断に必要な情報を揃えることを優先しています。

---

目標は「誰でも同じ判断」ではない

このツールを作り始めた当初、「農産担当者でなくても発注できる状態」を目標にしました。

ただし、農産担当者とまったく同じ判断を、誰でもすぐにできるようにすることはできません。

そこで、目標を次のように整理しました。

担当者の経験を完全に置き換えるのではなく、担当者以外でも重大な確認漏れを防ぎ、一定の根拠を持って発注案を作れるようにする。

熟練者の暗黙知を一度にすべて数式化するのではなく、まずは次の情報を共通化します。

• 何を確認するか
• どの単位で数えるか
• 何を基準在庫とするか
• 何個売れたか
• なぜ発注数を変更したか

この履歴が蓄積されれば、担当者がどのような状況で発注提案数を修正したのかも分析できます。

---

作ってみて分かったこと

今回の試作で分かったのは、現場の判断を記録できるデータ構造だということです。

販売実績や天候データがあっても、現場在庫や商品化の換算数が整理されていなければ、適切な発注提案は作れません。

また、機能を増やすことよりも、次の点が重要でした。

• 入力項目を増やしすぎない
• 品目名を分かりやすく表示する
• 対象外の商品も追加できるようにする
• 計算用の列は利用者から隠す
• 正式な発注システムは変更しない
• 現場確認から正式発注までの動線を崩さない

「どこまで自動化できるか」よりも、現在の作業のどこへ差し込めば負担を減らせるかを考える必要がありました。

---

今後追加したい機能

今後は、実際の運用を確認しながら次の機能を検討します。

• 曜日別の販売実績
• 天候や気温の反映
• 特売・催事情報の反映
• 品目別の適正在庫の見直し
• AIを使った発注提案と理由の表示
  　
  最終的には、次のサイクルを作りたいと考えています。

在庫確認
  ↓
発注提案
  ↓
人が補正
  ↓
正式発注
  ↓
販売結果を記録
  ↓
次回の発注基準へ反映

---

まとめ

今回作っている農産発注支援ツールは、発注を完全自動化するシステムではありません。

目的は、担当者の頭の中にある判断材料を分解し、担当者以外でも確認・記録・判断できる形にすることです。

これからも、実際の発注と販売結果を確認しながら改善していきます。