AppSheetとGASでスマホでJANコードを読み取る在庫管理Appを作ってみた

これは シーエー・アドバンス Advent Calendar2022 24日目の記事です。

前回の記事は @arakawa_moriyuki さんの 「AIペアプログラマーGithub Copilotが書くコードは実際どのくらい使えるのか」 でした。
明日の記事は @azama_yasuhiro さんの「BurpSuite からの Christmas Present」です。

はじめに

こんにちは。
シーエー・アドバンス（CAAD）技術統括本部セキュリティチームの @asami-H-Ishi と申します。
社内（グループ会社含む）サービスの脆弱性診断をサポートする業務を行っています。
開発未経験で異業種から入社して7年目に入りました。

普段の業務ではあまり開発をすることがないので、せっかくだから今年のアドベントカレンダーでは開発っぽいことをやりたいなと思っていました。
ある日、たまたまQiitaで「AppSheetと20行のGoogle Apps Scriptで作るバーコード蔵書管理アプリ」 と 「バーコード蔵書管理アプリに手を加えてみた」 という記事を見かけたので、参照して手を動かしてみたところ、AppSheetで蔵書管理アプリをかんたんに作れてしまいました。
うまくいったことに味を占めて 応用して、蔵書のみならず家の在庫管理アプリが作れたらな！　と思って取り組んでみたので、その内容をアドベントカレンダー記事にしたいと思います。

なお、上記2記事と同様に参照したのがこちらのブログでした。

シリーズになっていて、ところどころ参考にさせていただいております。

目的

Googleのスプレッドシートから拡張機能であるApps Script(GAS)およびAppSheetを利用し、Yahoo！ JAPANが提供する商品検索APIを用いて、スマホのAppSheetアプリからJANコードを読み取って商品登録をすることができる在庫管理アプリを作成します。

手順

1. 準備するもの

• Googleアカウント
  • スプレッドシート
• スマホ
  • AppSheetアプリ
• Yahoo! JAPAN ID

2. Yahoo!JAPAN IDでClient IDを発行する（事前準備１)

1. Yahoo! JAPAN IDを取得し、 Yahoo!デベロッパーネットワークトップ にアクセスします。
2. 「API・OSS」の「アプリケーションの管理」をクリックし、「新しいアプリケーションを開発」に遷移します。
3. 「Web APIを利用する場所」では、デフォルトで「ID連携を利用しない」が選択されているのを確認します。
4. 「アプリケーションの利用者情報（契約者情報）」では、ご自身の情報等を入力してください。必須項目のみで大丈夫です。
5. 「アプリケーションの基本情報」ではアプリケーション名が必須となっていますが、判別用なので自分でわかる名前を入れておけばOKです。わたしは「InventoryApp」と入力しました。
6. 「ガイドライン」で同意するにチェックを入れて「確認」ボタン押下→入力情報に間違いがないか確認したのち「登録」ボタン押下 で、Client IDが発行されます。

3. GoogleアカウントにログインしたChromeでスプレッドシートをDBにする(事前準備2)

1. スプレッドシートを新規作成します。名前はご自分がわかるものにしていればOKです。わたしは「インベントリ」にしました。
2. シート名を「Inventory」 にします（※後半で紹介するコードをそのままコピペされる方はこの名前にしておいてください。読み替えて書き換えられる方は、ご自分で任意のシート名にした上で、コード上で該当箇所を書き換えてくださればと思います！）。
3. カラム設計は下記のとおりです。
   今回の要件は下記で考えています。

• JANコードを読み取って商品検索APIで最低限の情報を商品検索APIで取得してくる
• APIで取得した情報をシートに自動で書き込む
• いつ登録したかをCreated at欄に yyyy/mm/dd 00:00:00 の形式で書き込む
• 商品検索APIでヒットしなかった情報については、ID/JANコード/登録日時のみを書き込む（アプリまたはスプレッドシート上で手動で入力できるようにする）

4. AppSheetでアプリを作成する(事前準備３)

1. スプレッドシートから「拡張機能 > AppSheet > アプリを作成」に進みます。
2. 初回はアプリ利用の同意確認画面が出ますが、内容をよく読んで問題なければ「Accept」で進みます（自分以外にアクセスさせない前提なので、ここではそのまま許可してください。他ユーザにもアクセスさせる場合については触れません）。
3. 左側メニューの「Data」を選ぶと、Inventoryシート(スプレッドシート)から作成されたInventoryDBができているので、選択してTableを確認します。各設定は下記スクショを参考にしてください。
4. 設定ができたら、右上の「Save」ボタンを押下します。

5. AppSheetアプリをスマホに入れる

1. iPhoneではAppStore、AndroidではGoogle Play Storeで「AppSheet」と検索して、アプリをインストールします。
2. アプリを開いて、手順３でログインしたのと同じGoogleアカウントでサインインします。
3. 表示された画面上の「Shared with me」横のハンバーガーメニューを開いて、「Owned by me」を開くと、そこにスプレッドシート名のアプリが表示されていると思います。わたしはスプレッドシート名を「インベントリ」にしたので、「インベントリ」という名前のアプリをタップして開きます。
4. 下記のとおり表示されていればOKです。

6. GASを書く

1. スプレッドシート「インベントリ」 の「拡張機能 > Apps Script」でGASのコード画面を開きます。
2. 下記コードを書いて、「プロジェクトを保存」ボタンを押下します。

コード.gs

// Yahoo商品検索APIを利用するためのClient ID
const appid= '手順２で発行したClient ID';

function fetchInventoryIndex(janCode) {
  const url = 'https://shopping.yahooapis.jp/ShoppingWebService/V3/itemSearch?appid=' + appid + '&jan_code=' + janCode;
  const res = UrlFetchApp.fetch(url,{muteHttpExceptions: true});

  const json = JSON.parse(res.getContentText());
  if (json.totalResultsReturned === 0) return {"name":"", "image":{"medium":""}, "brand": {"name":""}};
  return json.hits[0];
}

// Created atに日本時間で「2022/12/24 09:15:30」の形式で登録日時を返す
function getNowDate(){
  let d = new Date();
  return Utilities.formatDate(d, "Asia/Tokyo", "yyyy/MM/dd HH:mm:ss");
}

// シートが更新された時に呼び出される関数として定義
function onChangeSheet(e) {
  const sheet = SpreadsheetApp.getActive().getSheetByName('Inventory');
  
  sheet.getDataRange().getValues().forEach((row, i) => {
    const janCode = row[1], name = row[2];
    if (!janCode || name) return;
    const s = fetchInventoryIndex(janCode);
    sheet.getRange(i + 1, 1, 1, row.length).setValues([[i, janCode, s.name, s.image.medium, s.brand.name, getNowDate()]]); 
  }); 
}

7. GASのトリガーを設定する

1. GAS画面左の目覚まし時計みたいなアイコン （トリガー)をクリックします。
2. 右下にある「＋ トリガーを追加」ボタンをクリックします。
3. 実行する関数を選択: onChangeSheet を選択します。
4. イベントの種類を選択: 変更時 を選択します。
5. エラー通知設定については、ご自身の好みで設定してください。メールで届きます。
6. 「保存」ボタンを押下 します。

うまく動くかテストします。

たまたま極細ポッキーが近くにあったので、AppSheetアプリを導入済みのスマホでJANコードを読み取ってみました。

GIF画像にしてみたんですがちょっとわかりづらいので、一応、アプリの操作手順を書いておきます。

1. AppSheetアプリ起動します。
2. 右下の ＋ ボタン押下します。
3. janCode 欄右端のアイコンをタップ（カメラが起動します)。
4. 登録したい商品のバーコードを読み取ります。
5. 右下の「Save」ボタンを押下します。
6. 画面が変わって、読み取ったJANコードが一覧に表示されます。
7. 右上の丸矢印に同期中のバッジがつくので、それが消えたら丸矢印アイコンをタップします。
8. うまいことGASが動いてYahoo!の商品検索APIが動き、DBであるスプレッドシートにデータが書き込まれたものが反映され、アプリ上でも商品情報が自動で反映されます。

どうでしたか？　お手元でもうまく商品情報を読み取っていけたでしょうか？
ちなみに、スプレッドシートのjanCode欄にJANコードを直打ちしても同様にアプリにも反映されます！

おわりに

今回は、スマホアプリのカメラ機能でJANコードを読み取って在庫管理アプリに登録していく という最低限の動きができるところまで実装することができました。
できれば今後、 賞味期限を登録したら、任意の日時にリマインドしてくれる ようにしたり、同じ商品を登録したら購入回数と登録日時から頻度を算出して、消耗品などの買い置きサイクルを予測してリマインドしてくれる みたいな機能をつけられたりすると大変便利だなぁと妄想しています。

とはいえ、普段まったく開発をしないため、JavaScriptの読み替え、書き換えがうまく行かず、同僚の @syk_nakayama さん、@miya_ken さんに泣きついてコードが動くようになったので、本当におふたりには感謝しかありません｡ﾟ(ﾟ´Д｀ﾟ)ﾟ｡

次回なにか項目や機能を増やすときには、自力でJavaScriptを書けるように精進してまいります！

それではみなさん、健康第一で、よいクリスマスを過ごされ、よい新年をお迎えくださいませ！