【初心者向け】AppSheetで仕入・販売・在庫を管理！Refで自動入力＆連動する方法

AppSheetで商品管理が出来るアプリが作れないかって色々調べてたんですけど、販売と仕入れは管理出来るやり方はあるんだけど、販売と仕入れと在庫が全部管理出来る方法が無い感じだった。

なので、自分でシンプルな販売・仕入れ・在庫の管理が出来るアプリを作ってみました！

良かったら参考にして頂けると幸いです。

早速今回のアプリ制作ですが、在庫管理の観点から、販売・仕入れ・在庫の関係をAppSheetの参照（Ref）機能を使って、小計や単価をAppSheetの参照（Ref）機能で自動計算・表示させることは可能です。

アプリケーションの構成

アプリケーションの構成

構成としては商品マスタをメインに、販売と仕入れを管理していく感じにしていきました。

1. テーブル構成

まず、以下の3つのテーブルを考えます。

この小計は、販売テーブルや仕入れテーブルで数量と単価を掛け合わせることで計算します。

商品マスタ (元テーブル)
- 商品ID (キー)
- 商品名
- 単価
- 仕入単価
- 在庫数（最初は入力しない）
販売 (別テーブル)
- 販売ID (キー)
- 商品ID (Ref型)
- 商品名 (自動入力)
- 販売単価 (自動入力)
- 数量
- 販売小計 (自動計算)
- 販売日
仕入れ (別テーブル)
- 仕入ID (キー)
- 商品ID (Ref型)
- 商品名 (自動入力)
- 仕入単価 (自動入力)
- 数量
- 仕入小計 (自動計算)
- 仕入日

こちらのテーブルはスプレッドシートで作成します。

一つ目のシート名を「商品マスタ」にして、画像の表を作成します。

A1から「商品ID」「商品名」「単価」「仕入単価」を入力します。
※単価は販売単価となります。

こんな感じです。分かりやすく数字を入れてます。

二つ目のシートを追加して名前を「販売」にして販売の項目を表にします。

次に新しいシートを追加して「仕入れ」を作ります。

ここまでスプレッドシートで作成したら上の「拡張機能」から「AppSheet」の「アプリを作成」をクリックします。

下の状態で30秒ほど待ちます。

この状態になりましたら、軽くアプリケーションとして出来てます。

このままだと、上手く動かないので中を触っていく感じになります。

まずは左上側にある三段のタンスのようなアイコン「Data」をクリックしてください。

そうすると、データベースになってる状況が確認出来ますが「商品マスタ」だけしか表示してないので、「販売」と「仕入れ」もアプリに認識させる必要があります。

これを手動で行います。

下の画像のプラスのところをクリックしてください。

下の表示が出ますので、そしたらまずは「Add Table “販売”」をクリックしてください。

次もそのまま赤い丸の部分で囲ってる「Add to app」をクリック。

下のように表示されたら成功です！

そうしましたら同じように「仕入れ」を認識させていきましょう。

やり方は先ほどと同じようにするだけです。

2. リレーションシップの構築

販売テーブルと仕入れテーブルは、それぞれ商品マスタを参照します。

販売テーブル: 商品ID列のTypeを「Ref」にし、Source tableを「商品マスタ」に設定します。
仕入れテーブル: 同様に、商品ID列のTypeをRefにし、Source tableを商品マスタに設定します。

変更しましたら右上の「Done」をクリック。

この設定により、販売や仕入れの入力画面で商品IDを選択すると、AppSheetは商品マスタから関連する情報を取得できるようになります。

仕入れのテーブルも同じようにしてください。

3. Initial value（初期値）による自動入力

リレーションシップを基に、販売と仕入れの入力画面で商品IDを選択した際に、関連情報を自動で入力するように設定します。

`販売`テーブルでの設定

商品名列のInitial valueに、[商品ID].[商品名] と設定します。
販売単価列のInitial valueに、[商品ID].[単価] と設定します。

販売小計列: この列は、**数量と販売単価**を掛け算する計算式を設定します。

TypeをNumberに設定します。
Formulaに[数量] * [販売単価]と記述します。この列は、ユーザーが手動で入力するのではなく、自動で計算されるためEditable?のチェックを外しておくのが一般的です。

`仕入れ`テーブルでの設定

仕入単価列のInitial valueに、[商品ID].[仕入単価] と設定します。
商品名列のInitial valueに、[商品ID].[商品名] と設定します。

仕入小計列: Formulaに[数量] * [仕入単価]と記述します。同様にEditable?のチェックを外します。

[商品ID].[仕入単価]

※販売テーブルと同じように入力してください。

4. 在庫数の更新反映

販売や仕入れの際に、商品マスタの在庫数を更新するには、商品マスタの在庫数列に以下のVirtual Column（仮想列）を追加するのが一般的です。

`商品マスタ`テーブルでの設定

在庫という列をVirtual Columnで新たに作成します。これは、販売テーブルと仕入れテーブルの数量を基に計算されるVirtual Columnにすることが多いです。
在庫数列のFormulaに、以下の計算式を記述します。
SUM(SELECT(仕入れ[数量], [商品ID]=[_THISROW].[商品ID])) - SUM(SELECT(販売[数量], [商品ID]=[_THISROW].[商品ID]))
- SUM(SELECT(仕入れ[数量], [商品ID]=[_THISROW].[商品ID])): 仕入れテーブルから、この商品の全仕入れ数量の合計を計算します。
- SUM(SELECT(販売[数量], [商品ID]=[_THISROW].[商品ID])): 販売テーブルから、この商品の全販売数量の合計を計算します。
- 仕入れの合計から販売の合計を引くことで、現在の在庫数が算出されます。

SUM(SELECT(仕入れ[数量], [商品ID]=[_THISROW].[商品ID])) - SUM(SELECT(販売[数量], [商品ID]=[_THISROW].[商品ID]))

Virtual Columnは右上の「＋」から作成出来ます。

名前は「在庫」にして、App Formulaに先ほどの関数を入力してください。左下に緑のチェックマークが出たらOKなので、右下のセーブした後「Done」でそのウィンドウを閉じてください。

ここまで設定したら、View data sourceで商品マスタのシートを開きます。

商品マスタのシートが開いたら最後の列に「在庫数」を追加します。

在庫数を作成したらAppsheetのDataの商品マスタの画面に戻りまして、Regenetate schemaで更新します。

バーチャルコラム（Virtual Column）で計算された在庫をスプレッドシートに反映させるには、直接はできません。バーチャルコラムはAppSheetアプリ内でのみ存在する「仮想的な」列であり、元となるスプレッドシートには物理的に存在しないためです。

次の以下の2つの方法で、バーチャルコラムの値をスプレッドシートに書き込むことができます。

アクション (Actions) の設定方法

この方法では、ユーザーが特定の操作（ボタンのクリックなど）を行うことで、バーチャルコラムの値をスプレッドシートの既存の列に書き込みます。

設定手順

スプレッドシートの準備: 商品マスタテーブルのスプレッドシートに、在庫数という物理的な列を新しく追加します。この列は空のままで構いません。※これは先ほどのテーブル設定です。
アクションの作成:
- Behavior > Actions に移動し、新しいアクションを作成します。
- Action name: 在庫をスプレッドシートに反映 など、分かりやすい名前にします。
- Do this: Set the values of some columns in this row を選択します。
- For a record of this table: 商品マスタ を選択します。
- Set these columns:
  - Column to set: 在庫数（スプレッドシートに追加した物理的な列）
  - Column value: [在庫数]（バーチャルコラム）
アクションの表示: 作成したアクションを、ビュー（例：商品マスタのデックビュー）に表示させます。ユーザーはボタンを押すだけで、リアルタイムな在庫数をスプレッドシートに書き込めます。

上の説明に合わせて入力していきます。

最後にセーブで保存してください。

これでアクションでスプレッドシートの在庫数に反映出来ます。

反映する場合は画像上にある青い部分をクリックすると反映出来ます。

※オートメーション機能は無料だと使用制限があり、このような作業は出来なかったです。

5. 表示ビューの設定

アプリでの表示設定をしていきます。

ここでは基本的な設定で行います。

仕入れ・販売の今月のみ表示設定

今月のみ表示する場合は、SUM関数で可能になります。

まず下準備の設定を行います。

仕入れテーブルからVirtual Columnを新しく追加、Column nameに「今月の仕入」、App formulaにSUM関数を入れます。

SUM(
  SELECT(
    仕入れ[仕入小計],
    AND(
      YEAR([仕入日]) = YEAR(TODAY()),
      MONTH([仕入日]) = MONTH(TODAY())
    )
  )
)

TypeはNumberを選択してください。

販売テーブルも同様、Virtual Columnを新しく追加、Column nameに「今月の販売」、App formulaにSUM関数を入れます。

SUM(
  SELECT(
    販売[販売小計],
    AND(
      YEAR([販売日]) = YEAR(TODAY()),
      MONTH([販売日]) = MONTH(TODAY())
    )
  )
)

これをコピーしてペーストすると良いですよ。

こちらのTypeもNumberを選択してください。

この二つが出来ましたら次に行きます。

今度はスライスを追加します。

仕入れテーブルの右側の+をクリック。

下のように名前は「今月の仕入スライス」、テーブルは「仕入れ」、Row filter conditionに「EOMONTH([仕入日], 0) = EOMONTH(TODAY(), 0)」を入力して保存。

EOMONTH([仕入日], 0) = EOMONTH(TODAY(), 0)

販売も同じように、名前を「今月の販売スライス」、テーブルは「販売」、Row filter conditionに「EOMONTH([販売日], 0) = EOMONTH(TODAY(), 0)」を入力して保存。

EOMONTH([販売日], 0) = EOMONTH(TODAY(), 0)

次に商品マスタに在庫スライスを作成します。

名前「今月の在庫スライス」、テーブル「商品マスタ」、Row filter conditionｍには下記を入力してください。

OR(
  IN([商品ID], SELECT(販売[商品ID], EOMONTH([販売日], 0) = EOMONTH(TODAY(), 0))),
  IN([商品ID], SELECT(仕入れ[商品ID], EOMONTH([仕入日], 0) = EOMONTH(TODAY(), 0)))
)