GoogleフォームとQRコードで在庫管理表を作る方法｜関数で在庫数を自動計算

以前紹介した物品管理ツールの別バージョンとして、GoogleフォームとQRコードを組み合わせた在庫管理表を作りました。

QRコードをスマートフォンで読み取り、「入庫」「出庫」と数量を送信すると、スプレッドシートの在庫数が自動で変わります。

今回は架空の商品を例に、AIへ相談しながら自分で設定した手順を紹介します。

小規模な物販や備品管理などにも応用できる仕組みです。

※この記事の画面には架空の商品だけを使用しています。公開用画像では、実際に使えるQRコードとURLを隠しています。

今回作ったもの

今回の仕組みでは、次のことができます。

• 商品ごとにQRコードを作る
• QRコードを読むと、その商品のIDが入力されたフォームを開く
• 入庫または出庫と数量を登録する
• 入出庫の履歴を自動で残す
• 現在庫を自動計算する
• 在庫切れ、要補充、在庫ありを自動判定する

補充リストの作成や在庫不足のメール通知は、今回は含めていません。まずはQRコードから在庫を増減できる基本部分までを作りました。

作成手順

ここから、実際に行った設定を順番に紹介します。

1．商品一覧を作る

最初に、Googleスプレッドシートへ商品の基本情報をまとめました。

まずA1〜I1に以下の見出しを入力します。

①A1〜I1へ見出しを入力する

見出しセル	入力する見出し	列の型	入力方法	最初にすること
A1	商品ID	テキスト	手入力	A2から ITEM-001 などのIDを入力
B1	商品名	テキスト	手入力	B2から玄米、水などの商品名を入力
C1	カテゴリ	プルダウン	選択	C2から食品、飲料などを選ぶ
D1	現在庫	数値	あとで数式	D2以降は最初は空欄にする
E1	最低在庫数	数値	手入力	E2から補充を考え始める数量を入力
F1	補充目標数	数値	手入力	F2から補充後に戻したい数量を入力
G1	在庫状況	テキスト	あとで数式	G2以降は空欄。プルダウンにはしない
H1	QR用URL	テキスト	あとでURLと数式	H2以降は最初は空欄にする
I1	保管場所	テキスト	手入力	I2から棚1、棚2などを入力

②入力範囲をテーブルに変換する

テーブルに変換すると、各見出しの右側に「▼」が表示され、列の型を選べるようになります。

操作はGoogle公式ヘルプ「Google スプレッドシートでテーブルを使用する」でも確認できます。

次に、表の左上に表示されたテーブル名の「▼」を押し、「テーブル名を変更」を選びます。

今回は 商品一覧 と入力します。

この名前は、あとで数式の中でも使用します。

③列の型を設定する

見出しを入力したら、各列を「テキスト」「数値」「プルダウン」のいずれかに設定します。

列ごとに次の操作を行います。

1. 見出しの右側にある「▼」を押す
2. 「列の型を編集する」を押す
3. 表示された一覧から、設定したい型を選ぶ

今回は、次のように設定しました。

• A、B、G、H、I列：「テキスト」を選ぶ
• D、E、F列：「数値」を選ぶ
• C列：「プルダウン」を選ぶ

G列「在庫状況」にはあとで関数を入れるため、プルダウンではなく「テキスト」にします。

④C列のプルダウンを作る

C列を「プルダウン」にしたら、C2のプルダウンを開き、下部にある鉛筆のマークを押します。

選択肢の編集画面で、項目を1つずつ登録します。

今回は以下の項目に設定しました。

• 食品
• 飲料
• 日用品
• 文房具
• 衛生用品
• その他

選択肢を登録したら「完了」を押します。

以降は、C列の各セルで該当するカテゴリを選べます。

⑤2行目へ最初の商品を入力する

例えば、玄米を最初の商品として登録する場合は、2行目へ次のように入力します。

セル	入力例
A2	ITEM-001
B2	玄米
C2	食品
D2	空欄
E2	2
F2	3
G2	空欄
H2	空欄
I2	棚1

この時点で手入力するのは、A2、B2、C2、E2、F2、I2です。

D2、G2、H2は後で設定するため、空欄にします。

画像は完成後の状態なので、D列「現在庫」とG列「在庫状況」にも結果が表示されていますが、現段階では、D2とG2は空欄で問題ありません。

商品IDは、商品ごとに別のものを付けます。

今回は ITEM-001、ITEM-002、ITEM-003 のように連番にしました。

最低在庫数は「この数以下になったら補充を考える基準」、補充目標数は「補充したあと、何個まで戻すか」です。

例えば、最低在庫数が2個、補充目標数が4個なら、現在庫が2個以下になったときに補充を考え、在庫を4個まで戻します。

そのため、補充目標数は最低在庫数より大きくしておきます。

画面下のシート名は シート1 のままでも動きます。

「表の名前」と「シート名」は別のものなので、間違えないように注意してください。

2．入出庫を記録するGoogleフォームを作る

次に、Googleフォームを作ります。

フォーム名は「在庫管理　入出庫記録」とし、質問は次の4項目です。

1. 商品ID：記述式・必須
2. 在庫処理：入庫または出庫・必須
3. 数量：記述式・必須
4. 備考：段落・任意

在庫処理の質問は選択肢が2つだけなので、プルダウンよりもラジオボタンのほうが操作しやすいと考えました。

商品IDは毎回入力するのではなく、あとでQRコードから自動入力します。

フォームを開いたときに正しい商品IDを確認できるよう、商品ID欄は表示したままにします。

違うIDが入っていた場合は、送信前に手入力で修正ができます。

3．数量に入力ルールを設定する

数量欄に文字やマイナスの数が入ると、在庫数が正しく計算できません。

そこで、「数量」の質問に回答ルールを設定します。

1. Googleフォームの編集画面で「数量」の質問を押す
2. 質問形式を「記述式（短文）」にする
3. 質問カード右下の「︙」を押す
4. 「回答の検証」を押す
5. 質問の下に表示された1つ目の欄で「数値」を選ぶ
6. 2つ目の欄で「次より大きい」を選ぶ
7. 基準の欄へ半角数字で 0 と入力する
8. エラーメッセージ欄へ「0より大きい数字を入力してください。」と入力する
9. 質問カード右下の「必須」をオンにする

Googleフォームでは、記述式の質問に数値などの回答ルールを設定できます。

詳しい手順はGoogle公式ヘルプ「フォームのルールを設定する方法」でも確認できます。

4．フォームの回答先をスプレッドシートへ接続する

フォームの回答は、手順1で商品一覧を作ったスプレッドシートへ保存します。

1. Googleフォーム上部の「回答」を押す
2. 緑色のスプレッドシートのマークを押す
3. 「既存のスプレッドシートを選択」を選ぶ
4. 右下の「選択」を押す
5. ファイルの選択画面で、手順1で作った「在庫管理シート」を選ぶ
6. もう一度「選択」を押して接続する

スプレッドシートのファイル名を変更している場合は、自分で付けた名前のファイルを選びます。

接続すると、最初に作った「在庫管理シート」のファイル内に、フォームの回答を記録するタブが追加されます。

この記事では、画面下に追加されたタブの名前を「入出庫履歴」に変更しました。

ここへ、フォームの回答が1件ずつ自動で記録されます。

接続方法は、Google公式ヘルプ「フォームの回答の保存先を選択する」でも確認できます。

5．フォームの設定を確認する

今回は回答者を特定する必要がないため、メールアドレスは集めない設定にしました。

また、QRコードを読むたびに何度も登録できるよう、「回答を1回に制限する」はオフにします。

「回答の編集を許可する」もオフにしました。

ほかの人と使う場合は、フォームの共有範囲も確認します。

6．入庫と出庫をテストする

数式を入れる前に、フォームからテスト回答を送信しました。

今回は、次の3件を登録しています。

商品ID	在庫処理	数量	備考
ITEM-001	入庫	4	初期在庫
ITEM-002	入庫	2	初期在庫
ITEM-001	出庫	1	動作確認

回答用の表に3行が追加されれば、フォームとスプレッドシートの接続は成功です。

画像の左上には、Form_Responses と自動で表示されています。

あとで現在庫を計算する関数へこの文字を入れるため、名前を確認しておきます。

自分の画面に別の名前が表示された場合は、その名前を使います。

7．現在庫を自動計算する

商品一覧のD2へ、次の数式を入力します。

=IF(A2="","",SUMIFS(Form_Responses[数量],Form_Responses[商品ID],A2,Form_Responses[在庫処理],"入庫")-SUMIFS(Form_Responses[数量],Form_Responses[商品ID],A2,Form_Responses[在庫処理],"出庫"))

この数式は、A2にある商品IDの現在庫を計算します。

例えばA2が ITEM-001 なら、入出庫履歴から ITEM-001 の記録だけを探します。

入庫が4個、出庫が1個なら、D2には「4－1」で3と表示されます。

A2に商品IDが入っていない場合は、D2も空欄になります。

数式を入力したら、D2を下の商品行までコピーします。

D3ではA3の商品ID、D4ではA4の商品IDというように、それぞれの現在庫が計算されます。

SUMIFSは、複数の条件に合う数値だけを合計する関数です。

構文はGoogle公式ヘルプ「SUMIFS関数」で確認できます。

8．在庫状況を自動表示する

在庫状況は、現在庫と最低在庫数から自動で表示されるようにします。

商品一覧のG2へ、次の数式を入力します。

=IF(D2="","",IF(D2<=0,"在庫切れ",IF(D2<=E2,"要補充","在庫あり")))

判定は次のとおりです。

• 現在庫が0以下：在庫切れ
• 現在庫が最低在庫数以下：要補充
• それより多い：在庫あり

もしG2に #REF! などのエラーが表示されたら、G2の数式が正しく入っているか確認し、必要であれば入力し直します。

9．商品IDを事前入力したフォームURLを作る

QRコードを読み取ったときに、商品IDが自動で入力されたフォームを開くようにします。

1. Googleフォームの編集画面を開く
2. 画面右上の「︙」を押す
3. 「フォームに事前入力する」を押す
4. 開いた画面の「商品ID」へ、商品一覧のA2と同じ ITEM-001 を入力する
5. 「在庫処理」「数量」「備考」は空欄のままにする
6. 画面下の「リンクを取得」を押す
7. 表示されたリンクをコピーする

取得したURLを、商品一覧のH2へ貼り付けます。

H3には次の数式を入力し、下の行までコピーします。

=SUBSTITUTE($H$2,$A$2,A3)

この数式は、H2のURLに含まれる ITEM-001 を、その行の商品IDへ置き換えます。

これにより、商品ごとのURLを一つずつ作る手間を減らすことができます。

Googleフォームの事前入力リンクの作り方は、Google公式ヘルプ「Publish & share your form with responders」の「Send a form with pre-filled answers」で確認できます。

URLが長いほど、QRコードの模様は細かくなります。

大きく表示すれば読み取れますが、ラベルを小さくするとスマートフォンで読み取りにくくなることがあります。

外部のURL短縮サービスを使えば、URLを短くしてQRコードの模様を簡単にできます。

ただし、短縮サービスが停止するとQRコードを開けなくなる可能性があります。

短縮URLを作成して管理する作業も増えるため、設定や貼り付けを間違える可能性もあります。

今回はURLを短縮せずにそのまま使い、QRコードを小さくしすぎないようにしました。

印刷する前に、実際の大きさでスマートフォンから読み取れるか確認します。

10．QRコードを一覧で作る

新しいシートを追加し、「QRラベル」としました。

見出しは次の3列です。

セル	見出し
A1	商品ID
B1	商品名
C1	QRコード

A2へ次の数式を入力します。

=INDEX(商品一覧[商品ID],ROW()-1)

B2には次の数式を入力します。

=INDEX(商品一覧[商品名],ROW()-1)

C2には次の数式を入力しました。

=IMAGE("https://quickchart.io/qr?text="&ENCODEURL(INDEX(商品一覧[QR用URL],ROW()-1))&"&size=400&margin=4",4,140,140)

3つの数式を入力したら、商品数の分だけ下へコピーします。

このQRコードは、外部サービスのQuickChartを利用して作っています。

数式を初めて入力したときは、セルに #REF! が表示され、画面上部に外部サービスとの通信を許可するための警告が出ることがあります。

これは、GoogleスプレッドシートのIMAGE関数が、QuickChartからQRコード画像を取得するためです。

数式内のURLが https://quickchart.io/ から始まっていることを確認し、「アクセスを許可」を押します。

許可すると、セルにQRコードが表示されます。

なお、QuickChartへ送られるのは、QRコードに変換する事前入力フォームのURLと商品IDです。

数量や入出庫履歴など、フォームから送信した回答は含まれません。

この警告は、初めて外部データへアクセスするときに表示されることがあります。すでにアクセスを許可している場合などは、表示されないこともあります。

詳しくは、Google公式ヘルプ「インポート関数の詳細」で確認できます。

QuickChartでは、size でQRコード画像の大きさ、margin で周囲の余白を指定できます。

また、URLなどの文字列は、数式内の ENCODEURL でQRコードに使用できる形式へ変換しています。

各項目については、QuickChart公式「QR Code API」で確認できます。

QRコードが表示されても、セルの幅や高さが足りないと端が切れてしまいます。

今回は、QRを表示する2行目以降をまとめて選択し、行の高さを160ピクセルにしました。

続いて、C列の幅を180ピクセルにしました。

最終的には、QRコードの上下左右に白い余白が見える状態にします。

小さくしすぎるとスマートフォンで読み取りにくくなるため、印刷後にも必ずテストを行いましょう。

完成したラベル一覧は、次のようになりました。

※記事の画像から実際のフォームへアクセスされないよう、上の画像ではQR部分を SAMPLE に置き換えています。

12．最後にスマートフォンで動作確認する

ラベルをすべて貼る前に、まず1枚だけ印刷して動作を確認します。

確認する内容は次のとおりです。

1. スマートフォンでQRコードを読み取れるか
2. 正しい商品IDが入力されたフォームが開くか
3. 入庫または出庫と数量を送信できるか
4. 入出庫履歴に新しい行が追加されるか
5. 商品一覧の現在庫が変わるか
6. 在庫状況が自動で変わるか

確認例として、現在庫が3個、最低在庫数が2個の ITEM-001 を使います。

QRコードから出庫を1個登録し、現在庫が3個から2個になり、在庫状況が「在庫あり」から「要補充」に変われば正常です。

動作を確認したら、実際の初期在庫もフォームから「入庫」として登録します。

ラベルは、使い切るたびに捨てる商品本体ではなく、その商品を保管する棚や箱へ貼ると管理しやすくなります。

QRコードだけでなく、商品名と商品IDも一緒に印刷しておくと、ラベルを見分けやすくなります。

在庫不足の確認方法

QRコードから出庫を記録すると「在庫状況」が自動で更新されるため、在庫不足の商品を確認しやすくなります。

ただし、この基本版にはメール通知がないため、商品一覧を適宜確認する必要があります。今回は、次の方法で確認します。

1. 発注や補充を確認するタイミングで、商品一覧を開く
2. G列「在庫状況」の絞り込みを開く
3. 「要補充」と「在庫切れ」だけを表示する
4. 表示された商品と補充目標数を見て、発注または補充する数量を決める
5. 商品の入荷後、QRコードから「入庫」と数量を登録する

つまり、この基本版では商品一覧を補充確認表として使います。

在庫状況はフォームを送信するたびに自動で変わりますが、確認するタイミングは自分で決めなければなりません。

メール通知も便利そうですが、出庫のたびに通知するとメールが増えすぎる可能性があります。

また、「要補充になった瞬間だけ送る」といった条件付き通知には追加の自動化設定が必要です。

まずは、発注前や週1回など、確認するタイミングを決めて運用してみてはいかがでしょうか。

確認を忘れることが多い場合は、GoogleカレンダーやToDoリストを活用することもおすすめです。

まとめ

Googleフォームとスプレッドシートを組み合わせ、QRコードから入庫・出庫を登録できる在庫管理を作りました。

フォームを送信すると履歴が残り、商品一覧の現在庫と在庫状況が自動で変わります。

商品ごとのQRコードもまとめて作れたため、あとはラベルを印刷し、棚や保管箱へ貼れば使い始められます。

設定がうまくいかない場合は、画面を確認しながら一つずつ原因を探すことが大切です。必要に応じて、個人情報を隠したスクリーンショットをAIへ見せて相談する方法もあります。

※この記事の画面には架空の商品だけを使用しています。公開用画像では、実際に使えるQRコードとURLを隠しています。