AppSheetでバーコードを利用した医薬品棚卸しをする

病院の院内SE時代、当時はAppSheetなんて便利なものは無かったのでAccessで色々作ったり、GASでフルスクラッチにてアプリは作っていました。その当時のやり残した1つに、薬剤師さん向けの医薬品の棚卸しツールをAppSheetで作成しました。

今回のアプリは難易度が高い部分がありますが、それ故に得られた知見もたくさんありました。以下にアプリの構築と使い方をまとめてみました。

今回利用するファイル等

• 医薬品棚卸しツール - AppSheet
• お薬棚卸しスプレッドシートファイル
• バーコード画像保存用のGoogle Apps Script
• 医薬品一覧テンプレート

今回のアプリは医薬品の登録→棚卸しツールを実行、バーコード一覧のPDF出力までを行っています。バーコードの生成については外部APIを利用しており、生成された画像はドライブに保存して参照する為にGASを利用しています。

今回のバーコード一覧印刷は、例えば従業員一覧カードや宛先印刷、商品カタログ印刷、ワークフローの承認者の印鑑を表示など複数レコードを縦横に並べて印刷したい場合に非常に有効な手段です。

事前準備

GASのコードの割当

スタンドアローンのGASの準備

AppSheetテンプレートをコピーした後はGASへの参照が切れています。故に自身でスタンドアローンコンテナのGASファイルを作成して繋げてあげる必要があります。ここではそのGASのファイルの作成手順です。

1. Google Driveを開いて、左上の新規をクリック
2. その他 => Google Apps Scriptをクリック
3. スクリプトエディタが直接開いて警告文が出るのでそのまま作成を続行する
4. ドライブにはスタンドアローンファイルが生成される
5. スタンドアローンスクリプトをダブルクリックしてスクリプトエディタを開く
6. 左上のプロジェクト名がファイル名になるので、無題のプロジェクトではなくきちんと名前を付けましょう。（今回は緯度経度変換スクリプトという名前にしています）。
7. 後述のGoogle Apps Scriptをコピー&ペーストして保存する

見つからない場合には、Chrome Webstoreから拡張機能インストールしましょう（Google Apps Scriptで検索すると出てくる）。

図：新規から作成することが出来る

参照先スプシのIDの変更

AppSheetのテンプレートをコピーするとスプレッドシートも一緒に複製されてコピーされます。そのスプシのIDをGASの中に記述する必要があります。以下の手順でコピーした直後のスプシを開き、IDをGAS内に記述して上書きしましょう。

1. AppSheetの左サイドバーからData→適当なテーブル→View data sourceをクリックする
2. 参照先スプシが開かれるので、URLの中からID部分をコピーする
   
   https://docs.google.com/spreadsheets/d/ここがスプシのID/edit?gid=0#gid=0

   1	https://docs.google.com/spreadsheets/d/ここがスプシのID/edit?gid=0#gid=0

3. 前述で作ったGASのコンテナを開く
4. 上部にある変数ssidに対してスプシのIDを記述して上書き保存
   
   JavaScript

   //読み書き先スプシのID var ssid = "ここにスプシのIDを記述する";

   1 2	//読み書き先スプシのID var ssid = "ここにスプシのIDを記述する";

5. 一度適当な関数を実行して認証を適当に実行しておきます。

これでGASのコンテナファイルの準備が完了しました。

GASコードの参照を修正する

AppSheet側に入り、Automationに於いてGASを参照してる場所を修正します。

1. AppSheetのアプリ開発画面に入る
2. 左サイドバーよりAutomationをクリックし、医薬品マスタの中にあるバーコード生成を開く
3. バーコード生成トリガーの次にある画像生成をクリックする
4. Apps Script Projectの中をクリックし、前述で作成したスタンドアローンコンテナファイルを指定する
5. Authorizeというボタンをクリックする
6. 認証を実行して許可を与える
7. Function Nameや与える引数については変更は無いのでそのまま利用する
8. 右上のSAVEボタンを押して保存する

今回GASを利用してるAutomationはここの１箇所のみなのでこれで完了になります。

図：GASの参照を再設定する

バーコード画像保存フォルダ

データソースのスプシと同じフォルダ内に、医薬品新規登録時に自動生成するバーコード画像を保存する為のフォルダを作成しておきます。このフォルダ内にAPIリクエスト結果のバーコード画像が保存されてAppSheetで利用されます。

単純に「barcode」というフォルダを自分の場合作成しておきました。

このフォルダを開いて、フォルダのIDを取得し、こちらについてもGASのコンテナ内にある生成先フォルダのIDの場所に記述すると良いでしょう。

但しスプシから見ての相対パスとなるので、例えば今回の事例で言えば「/barcode/」を入れることになります。

PDF用テンプレートの割当て

今回のアプリでは登録した医薬品のバーコードを一覧で印刷する為のPDF生成機能を備えています。このPDFを生成する為の特別なテンプレートはプロジェクトコピー時にランダムな名前でマイドライブ以降のフォルダに格納されます。故に基本変更せずとも使えますが、後で自身で改良する場合を考えて、上部にあるリンク先からコピーしたものに差し替えておくと良いでしょう。

1. AppSheetのアプリ開発画面に入る
2. 左サイドバーよりAutomationをクリックし、PDF作成をクリックする
3. 真ん中のProcessにあるPDF作成をクリックする
4. Create a new fileとなっており、「Template」の部分をDocId=の横のファイルアイコンをクリックする
5. Google Drive Filesをクリックする
6. 自分が用意した医薬品一覧テンプレートを指定する
7. 右上のSAVEをクリックして保存する

図：雛形ファイルを指定し直す

技術的なポイント

バーコードの生成

GASのスクリプト

今回、医薬品マスタに対して編集や新規追加を行うと、自動的に外部のAPIにアクセスしてGoogle Driveのバーコード保存先フォルダに対してバーコード画像を保存するようにしています。直接AppSheetのFormulaにAPIへのURLを記述してリアルタイム表示でも良いのですが

• その都度、ビューを開くなどでレコードの数だけ相手のサーバにリクエストが飛ぶので負荷が掛かる
• スマフォの場合当然通信パケットの消費が増えることになる
• バーコード表示が都度都度ですと表示までのタイムラグが結構大きい。ドライブ保存であればそこまででない。

といった理由からです。

今回利用した外部APIサービスは無償で利用が出来る「bwip-js // Barcode Writer in Pure JavaScript」を利用しています。膨大なバーコードタイプに対応していますので、医薬品ならばJAN（EAN13）だけじゃなく、GS1コード（EAN14がGS1-14です）にも対応してるみたい。こちらのサイトでも生成は出来ます。ただ後者のサイトの場合、AppSheetのPDF印刷時に使ったら何故かエラーで出力されなかった為、bwip-jsを今回利用しています（Drive保存ならばどちらでも行けます）。

非常に多彩なオプションがあるので細かな調整をしたい場合はオプション項目をよくみて指定しましょう。

※今回はJANをバーコードに変換しています。

Automationの作成

前述まででGASを用意することが出来ました。このGASを呼び出す為のAutomationは医薬品マスタに新規追加された時と修正された時に発動するようにします。GAS側でスプレッドシートの画像パスへの書き込みまではさせているので、以下のように作成します。

• Automationで医薬品マスタをトリガーとしてイベントを作成する
• プロセスの画像生成にて、バーコード作成スクリプトを参照し、barcodegenerator関数を指定する
• その際の引数として、JANコードとレコードのID列を引き渡し、Return ValueはBooleanとする

これで医薬品の新規追加や内容の修正時にバーコードが生成されてフォルダに保存され、画像パスにバーコード画像へのパスが書き込まれてスマフォ等でも表示されるようになります。

バーコード一覧印刷

ビューの表示

PDF作成ビューはuserシートにメアドがありロールが「管理者」の人にだけ表示するようにshow ifで制御しています。PDF作成ビューのDisplayにあるShow ifに対して以下の数式を入れています。

よって管理者以外の人には使わせないように制御することが可能です。応用としてメアド登録していない人には棚卸しも使わせないようにしておくと良いでしょう（棚卸は同じuserシートの病院名を必要としているので）。

図：表示非表示の制御をしてる

アクションを作る

まずは次項のフォームでSAVEボタンを押した時に発動するアクションを3つ作成します。

1. userシートにメアドとfilter、更新フラグ、PDFパスという4列で構成したものを用意する
2. AppSheetでuserシートを取り込む
3. 左サイドバーのActionsを開いて、userテーブルに対して新規にActionを追加
4. まず1つ目は「値更新」。Do thisでは「Data: set the value of some column in this row」を選択し、更新フラグ列に対してUNIQUEID()を入れるというものを作成
5. 2つ目は「元のビューに戻る」。Do thisは「App: go to another view within this app」を選択し、送信後に医薬品マスタ画面に戻す為に「LINKTOVIEW("医薬品マスタ")」を数式として入れる
6. 3つ目は「PDF作成」。Do thisは「Grouped: execute a sequence of actions」を選択し、上記の値更新、元のビューに戻るの順番で登録してアクションをまとめて実行するようにします。
7. このPDF作成のアクションを次項で利用します。

図：値更新用のアクション

図：ビュー移動用のアクション

図：まとめて複数アクションを実行させる

選択した値を保存する仕掛け

現在のAppSheetは例えば分類ビューで現在選択してるレコードのIDを取得してる状態で、その値を元に例えばスライス等でフィルタするといったような事が出来ません。またその選択値をUserSettingsに入れて位置時保存みたいな事も出来ません。

特定の分類に属してる医薬品の一覧だけをバーコード印刷したいので、どうしてもダイアログ的なものが必要ですが、ダイアログ的なものもない為、その部分を自分でロジックを組む必要があります。以下のような手順でその為の仕掛けを作成します。

1. userテーブルのfilterは分類テーブルへRefを張り、ドロップダウン選択とします。
2. userテーブルを元にビューを作成し、View Typeをフォームに変更する（単一の入力フォームに変更される）
3. Row KeyにはUSEREMAIL()の数式を入れておく。これで自分のメアドの行が自動で選択された3.のフォームが表示されます。
4. Event Actionsでは前述で作成した「PDF作成」アクションをForm Savedで指定しておく
5. 右上のSAVEボタンを押して保存

このビューでは自分のメアドの行にヒットしたものだけが単一フォームとして開かれて、filterは分類テーブルへRefを張ってるドロップダウンにしています。分類を選んでSAVEをクリックするとアクションが実行されて、アクションの結果としてAutomationが発火するという仕掛けです。

図：このテーブル設計にする

図：View TypeとRow keyがポイント

動的な連番生成対応

AppSheetにはすべてのテーブルに「_RowNumber」という行番号が入ってるフィールドがデフォルで必ず用意されます。そのまま使う場合にはユニークなキーや連番として活用出来ます。しかしRow Filter Conditionやスライスを使ってフィルタをした場合この値は当然「飛び飛びの値」になってしまいます。こちらの元ネタはAppSheetコミュニティにて語られています。

後述のバーコード一覧用テンプレートは元々、連番を期待してる_RowNumberの値を計算値に使って印刷するように作られていました。しかし今回は医薬品分類毎に出力するということで、分類名でフィルタが掛けられる為、_RowNumberは飛び飛びになります。

そこで、医薬品マスタに対して以下の仕掛けをし、フィルタを掛けても動的に連番を生成してくれるようにしました。フィルタ後の状態でも1から順番に連番を取ります。

1. 予め医薬品マスタを元にスライスを作成し、以下の条件でフィルタするようにしておきます。前述の作業でfilter列に保存された値を利用します（スライス名は医薬品スライスとしました）
   
   [分類名] = LOOKUP(USEREMAIL(),"user","mail","filter")

   1	[分類名] = LOOKUP(USEREMAIL(),"user","mail","filter")

2. 医薬品マスタに仮想列を１つ追加する（名前は連番）
3. 連番のFormulaをクリックして以下の数式を入れます。
   
   COUNT(Select(医薬品スライス[ID],[_RowNumber]<[_ThisRow].[_RowNumber]))+1

   1	COUNT(Select(医薬品スライス[ID],[_RowNumber]<[_ThisRow].[_RowNumber]))+1

4. 右上のSAVEボタンをクリックして保存する

通常時はこの列は非表示のままで良いですが、PDF印刷時には医薬品スライスを元に処理をするので、その際の計算要素として必須の項目です。フィルタが掛かっていても必ず1からの連番で始まる値が採番出来ます。

実際に医薬品スライスのPreview Dataを見てみるとRowNumberは飛び飛びですが、連番列の値はしっかり1からの連番になっています。

図：RowNumberと連番列の比較

バーコード一覧用テンプレート

今回のバーコード一覧はこれまでのような縦にずらっとレコードを並べてであったり、請求書のように請求+明細といったようなものでもありません。関数を用いて非常に特殊な構造にしたテンプレートを用意する必要があります。こちらの元ネタはAppSheetのコミュニティで語られています。

このテンプレートですが請求明細で使ったStartとEndを二重の入れ子にして作る必要性があります。

このテンプレートはuserテーブルでの発火で利用するので?_THISROWがどのテーブルに対してなのか？を意識して構築します。

1. Googleドキュメントを作成し、ページ設定にて左右0.5、上下0の余白に設定する
2. 分類を指定してフィルタして印刷となるので、まずは外側の構造を作ります。以下のような数式をGoogle Documentに記述します。
   
   << Start: SELECT(分類[ID],[ID]=[_THISROW].[filter])>> <<[分類名]>> ・・・ここに次項でさらに追加する・・・ <<End>>

   1 2 3 4 5 6

   << Start: SELECT(分類[ID],[ID]=[_THISROW].[filter])>> <<[分類名]>>   ・・・ここに次項でさらに追加する・・・   <<End>>

   
   分類テーブルのIDに対して、現在のuserのfilter列の値が一致するものとしてフィルタをここで掛けています（SELECT関数）。これで指定の分類のものだけの塊という外側の構造が出来ました。この時、startの次の行で<<[分類名]>>と入れておくと、分類テーブルの分類名がそこに表示されます。
3. 入れ子として上記の「ここに次項でさらに追加する」の部分に、以下の要件で表を作る
   
   ・列は5列構成 ・行は1行のみで構成 ・1つのセルの中に単独の1x1の表をさらに追加しておく ・1x1の表の中にはバーコードを表示する ・1x1の表の上下でStart / Endの数式を記述していく

   1 2 3 4 5

   ・列は5列構成 ・行は1行のみで構成 ・1つのセルの中に単独の1x1の表をさらに追加しておく ・1x1の表の中にはバーコードを表示する ・1x1の表の上下でStart / Endの数式を記述していく

4. 前述で作成した表データの中のセルに以下のような数式を構築する。これが内側の構造になります。
   
   <<Start:ORDERBY(SELECT(医薬品スライス[ID], AND([_THISROW-1].[ID] = [分類名], MOD([連番],5)=1)),[_RowNumber])>> ・・・ここにバーコード等を表示する・・・ <<End>>

   1 2 3 4 5

   <<Start:ORDERBY(SELECT(医薬品スライス[ID], AND([_THISROW-1].[ID] = [分類名], MOD([連番],5)=1)),[_RowNumber])>>   ・・・ここにバーコード等を表示する・・・   <<End>>

   
   この数式がこのテンプレートの最も難解な部分で肝になる部分。ORDERBY関数を使ってSELECT関数で引っ張った医薬品スライスを対象に並び替えをした後に条件式を書いています。
   [_THISROW-n]は、親テーブルのIDを参照する式で、この場合2.の分類テーブルが親になります。これが自身の分類名と一致するもの且つ、連番を5で割った余りの数値が1のものという条件式になります。
   この式によって、連番を使って確実に左揃えでバーコード表示が出来、尚且つ分類テーブルの条件と一致するものだけが、医薬品スライスから抽出されていきます。この為に連番と医薬品スライスが必要なのです。
5. 4.で入力した内容を他の4セルにも反映し、「MOD([連番],5)=1」の1の部分を1,2,3,4,0と順番に書き換える。この数式の5の部分が1行のセルの数（5列）と同義です。
6. 保存して前述の1x1のセルの中には、医薬品スライステーブルにある「画像パス」「医薬品名」を表示するように追記する
   
   <<[画像パス]>> <<[医薬品名]>>

   1 2	<<[画像パス]>> <<[医薬品名]>>

7. 保存してAutomationで指定します。

数式の理解が非常に重要なテンプレートで、これまでのようなPDFの生成とはちょっとハードルの高さが違います。

※今回の事例だと医薬品スライスで分類をしっかりフィルタ出来てるので、4.の関数のANDにて2つ条件を指定していますがこれを以下のようにMODのみの条件にしても動作しました。スライスやRow filter conditionでできる事はそちら側で処理しておくと関数がシンプルになって良いです。

図：複雑な数式で構築したテンプレート

図：横に並べてバーコード一覧

AppSheetで申請書等のPDFを生成してみた

Automationの作成

テンプレートが用意できたら、以下の手順でPDF出力の処理を実装します。

1. userテーブルに対してAutomationをトリガーします。
2. Data Change TypeはUpdateのみにチェックします。
3. Conditionに対する数式は以下のように更新フラグを判定させて発火させます。
   
   [_THISROW_BEFORE].[更新フラグ] <> [_THISROW_AFTER].[更新フラグ]

   1	[_THISROW_BEFORE].[更新フラグ] <> [_THISROW_AFTER].[更新フラグ]

4. PDF作成プロセスでは、Create a new fileにて。前述のテンプレートを指定。File name prefixは「"医薬品一覧" & TEXT(NOW(), "YYYYMMDDHHMM")」という数式にしました。
5. File folder pathは「/PDF/」としています。
6. Disable Timestampはオンにします。
7. 次のプロセスを作って、Run a conditionとし、set these columnsではPDFパスに対して以下の数式で書き込みをさせます。
   
   "PDF/" & "医薬品一覧" & TEXT(NOW(), "YYYYMMDDHHMM") & ".pdf"

   1	"PDF/" & "医薬品一覧" & TEXT(NOW(), "YYYYMMDDHHMM") & ".pdf"

8. 最後にメール通知のプロセスを作ってToはUSEREMAIL()で自分自身宛て。
9. Attachments settingsではOther Attachmentsにて、「PDFパス」を指定する

これにより、分類選んでSAVEをクリックすることでアクション実行→Automation発火→PDF生成→メール通知までが一気に行われます。データ量によりますがメール到着までは暫く掛かります。

図：PDF作成→メール送信まで一括で行う

アプリの使い方

分類

医薬品マスタで使う為のマスタです。PDF作成時のフォームのドロップダウンとしても利用しています。ただ現在はRead Onlyとしています。

一般薬、麻薬、劇薬といった分類のみを登録しています。より詳細に分類分けしたい場合にはテーブルの権限としてAddやUpdatesを加えて編集できるようにしましょう。

各分類を開くとその分類に属してる医薬品がサブフォームとして表示されます。

図：リレーションで医薬品がリストアップ

医薬品マスタ

今回のアプリで最も重要なデータベースになります。分類を選び、JANコード入力（スマフォでもスキャン可能）、メーカーなどのサブ項目を選び、保存をクリックします（最後のコードは特に何もしなくて良いです）。

暫く更新状態になった後に自動的にバーコードが生成されて表示されるようになります。これで棚卸しやPDF生成で使う元データが完成しますので、スマフォ片手にじゃんじゃん登録していくと良いでしょう。

図：登録中の様子

図：バーコードが生成された

棚卸し

前述で登録しておいたデータベースを用いて、棚卸を現場でスマフォ片手にできるようにと考えて構築しています。棚卸しにて新規にレコードを追加します。

1. 新規レコードを追加したら再度開く
2. 病院名はuserシートから自動的に引っ張ってきていますのでuserシートをしっかり整備しておきましょう。
3. そのままサブフォーム下のAdd（追加）ボタンをクリックする
4. JANコードはカメラでスキャンすればオッケー。
5. 在庫数量を入力する
6. チェックオッケーならばOKをクリックする
7. 最後に保存をクリックする
8. 続ける場合は、3.から繰り返す

病院別に1枚のシートにデータが蓄積していくので複数の病院担当者が棚卸報告することで自動的に集計されてる状態となります。棚卸データはスライスにて、自分の所属病院のデータのみが表示されるようになっているので他の病院データが表示されることはありません。

定期的な棚卸数値は病院名とタイトルなどから絞り込みは出来るので、事前に運用ルールを決めておくと良いでしょう。

図：スマフォのみで作業出来る

PDF作成

今回のアプリで最も構築難易度の高かった部分。といってもユーザが使う分には手順は驚くほど単純です。

1. 自動的に自分のメアドのレコードフォームが表示される
2. 選択分類で分類を選択する
3. 保存をクリックすると2.で絞った分類の医薬品のバーコード一覧がPDF生成されて自身にメールで届く。
4. 医薬品マスタの画面に戻る

これだけです。必要な分だけ分類を選んで実行するだけです。

図：分類を選んでSAVEをクリックするとアクション開始

参考動画

今回のバーコード一覧PDF生成で参考になった「イルカのえっちゃんさん」の動画です。今回のサンプルはかなり手を入れてしまってるので違う面が多々ありますが、難易度高いとは言え各関数の挙動の学習や、目的を達成する仕組みの学習で確実に一段レベルアップ出来ました。

他にも他のチャンネルには無いような高度で変わったテクニックなども豊富なので腕を上げたい人は必見です。

関連リンク

• Create a Workflow Template using a "start" expression to look the same way as a "gallery" view
• Create Barcodes In Google Sheets In 2 Easy Steps
• Googleスプレッドシートでバーコード作成のススメ
• データをバーコード化｜Googleスプレッドシートで簡単に生成する方法
• 【世界はバーコードに満ちている】Vue.jsでバーコード生成プラグインを使ってみた。
• Barcode API
• 【AppSheet】Tips & Tricks
• Method to filter Related Records
• MOD() - AppSheet
• AppSheetのファイル作成時に、明細に自動で連番を作成する方法
• I have a View, and I want the row numbers to start from 1, down to the number of rows in that view.
• シーケンシャル キー
• Auto-generate Transaction No with security filters
• GTINとは何か、JANとの関係は？ そしてGS1とはいったいどんな組織か