QRコードを読み取って商品管理集計するプログラムをスプレッドシートとGoogle App Scriptで作成

IT

QRコードを読み取って商品管理集計するプログラムをスプレッドシートとGoogle App Scriptで作成

概要

製造した商品の数を簡単にカウントするようなことができないかと頼まれました。
商品を紙で管理していたので、その紙にカウント用のQRコードを追加することで実現できないかと考え、QRコードを読み取ることで商品をカウントして集計するプログラムを、スプレッドシートとGAS（Google App Script）で構築しました。
Googleアカウントさえあれば無料で作成するので、バーコードリーダーなど購入する必要がないのがいいですね。
QRコードは色々と応用できるかと思うので、何かの参考になればと思います。

必要な知識・構成

必要な知識

  • Google スプレッドシート
  • GAS（Google App Script）
  • HTML
  • JavaScript

構成

構成としては、スプレッドシートで必要なデータとQRコードを作成し、QRコードを読み込んで表示させるウェブサイトをGASで作成します。

ウェブサイトは下記のような画面にしました。

商品の個数を設定し、「登録する」をタップすることで商品を登録させます。
「登録する」をタップされたときに、ウェブサイトのプログラムからGASの処理を実行させ、スプレッドシートの該当の商品データを操作します。

次からその構築方法の詳細を説明します。

構築方法の詳細

スプレッドシートでデータ管理用のシートを作成する

プロジェクト用のスプレッドシートを新規作成し、シート「items」として商品データとQRコードを作成します。

シート「items」

項目内容
商品ID商品を識別するためのID。
プログラムなどで参照されるため、他の商品IDと重複しないようにします。
商品名商品がわかりやすいように記入。他から参照されないため自由に入力してOK。
枚数商品IDのサブカテゴリ枠として使用。
名称は自由に変更してOK。商品のバリエーションを追加する際に利用する。
商品ID 且つ 枚数が重複した商品は作らないようにする。
QRコードQRコードの生成列。
GoogleスプレッドシートでのQRコードの作成には、Google Chart APIとIMAGE関数を利用します。
例) ２行目の場合
=image("http://chart.apis.google.com/chart?chs=200x200&cht=qr&chl="&ENCODEURL(E2))
上記Google Chart APIは廃止されたようなので、今は代わりに下記APIを利用しています。（2024/06/24修正）
=IMAGE("https://api.qrserver.com/v1/create-qr-code/?size=200x200&margin=20&data="&ENCODEURL(E2))
コピペする際の注意点として、上記「E2」の箇所はその行のE列を参照するようにしてください。
詳しく知りたい場合は、「スプレッドシート　QRコード」などで検索してみてください。
URLQRコード生成用のURL。
ウェブサイトのURLと、アクセスした際に参照するオプションのパラメータを記入します。
ウェブサイトのURLは他のシートに記載しているため、そちらを参照してします。
例）２行目の場合
=info!$B$2&"?id="&$A2&"&num="&$C2
上記の「info!$B$2」で、シート「info」のB列２行目を参照させており、こちらにQRコードを読み込んだ際に表示させるウェブサイトのURLを記入しています。URLの作成方法は後ほど紹介します。
「"?id="&$A2&"&num="&$C2」は、URLを表示した際にプログラムで読み込むパラメータを付加させています。具体的には、A列（商品ID）とC列（枚数）になります。
売上個数商品の売上カウント項目。
QRコードを読み込んだ後に、GASのプログラムでカウントを増やします。
売価自由枠。商品で必要なデータなどを自由に追加して下さい。
原価自由枠。商品で必要なデータなどを自由に追加して下さい。
利益自由枠。商品で必要なデータなどを自由に追加して下さい。

QRコードを読み込んだ際に表示させるURLの作成方法

スプレッドシートのシート「info」に、QRコードを読み込んだ際に表示させるウェブサイトのURLを記入します。

シート「info」

今回ウェブサイトはGASのデプロイという機能を使用して作成します。
デプロイは、GASを使って作成したウェブアプリをGoogle Workspaceを使って公開することです。 デプロイすると、Google Workspaceでウェブアプリ用のURLが作成され、他のユーザーもアクセスできるようになります。
デプロイのやり方については、調べると分かりやすく説明されているサイトが多数あるので参考にされて下さい。

デプロイでは本番用と開発用を作成することができるため、テスト段階では開発用URLを利用していただくのがいいと思います。

デプロイの本番用の運用時の注意点として、プログラムを更新する際はデプロイの管理から既存のデプロイを更新するようにして下さい。
自分はこれに気づくまで、毎回新しいデプロイを作成してURLを更新して運用していました。。。

日付毎のデータ用シートの作成

こちらは月毎のデータを集計するために作成したシートになります。

シート「sales」

インデックスだけ記入し、後は基本的に操作することはありません。
QRコードで表示したウェブサイトから商品をカウントされた時に、GASからこのシートにデータを追加していきます。

GASのプロジェクトの作成

スプレッドシートの拡張機能からApp Scriptを選択してGASと連携させます。

GASのプロジェクトは、下記２つのファイルを使用します。

  • コード.gs
  • index.html

「コード.gs」では、QRコードを読み込んだ際の処理とウェブサイトの表示や、スプレッドシートのデータ操作を行います。
「index.html」には、ウェブサイトの表示内容を記載します。

コード.gs

function doGet(e) {
  const id = e.parameter.id;
  const num = e.parameter.num;
  const itemName = getItemName(e.parameter.id, e.parameter.num);

  const template = HtmlService.createTemplateFromFile('index');
  template.id = id;
  template.num = num;
  template.name = itemName;
  console.log('QR読み込み : ' + id + ' / ' + num + ' / ' + itemName);
  const htmlOutput = template.evaluate();
  return htmlOutput;
}

// 商品IDの商品名を取得する。該当商品が見つからない場合は0を返す。
function getItemName(itemId, num) {
  let ss = SpreadsheetApp.getActiveSpreadsheet(); //アクティブなスプレッドシートを取得
  let itemsSheet = ss.getSheetByName('items');
  let itemRow = findRow(itemsSheet, itemId, 1, num, 3);

  if(itemRow == 0){
    return 0;
  } else {
    return itemsSheet.getRange(itemRow,2).getValue();
  }
}

// idとnumに該当する行を取得する。見つからない場合は0を返す。
function findRow(sheet, id, idCol, num, numCol){
  var dat = sheet.getDataRange().getValues(); //受け取ったシートのデータを二次元配列に取得
  for(var i=1;i<dat.length;i++){
    if(dat[i][idCol-1] == id && dat[i][numCol-1] == num){
      return i+1;
    }
  }
  return 0;
}

function registerItem( jsonString ) {
  const date = Utilities.formatDate(new Date(), 'JST', 'YYYY/MM/dd');//QRを読み込んだ日付を取得
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const jsonObj = JSON.parse(jsonString);

  // シート:salesへ売上商品情報を追加する
  const salesSheet = ss.getSheetByName('sales');
  const numLastRow = salesSheet.getLastRow();
  salesSheet.getRange(numLastRow + 1, 1).setValue(jsonObj[0].id);
  salesSheet.getRange(numLastRow + 1, 2).setValue(jsonObj[0].num);
  salesSheet.getRange(numLastRow + 1, 3).setValue(jsonObj[0].cnt);
  salesSheet.getRange(numLastRow + 1, 4).setValue(date);

  // シート:items の該当商品へ売上個数を追加する
  const itemsSheet = ss.getSheetByName('items');
  const updateRow = findRow(itemsSheet, jsonObj[0].id, 1, jsonObj[0].num, 3);
  if(updateRow === 0) {
    console.log('該当商品が見つかりませんでした。');
  } else {
    var amount = itemsSheet.getRange(updateRow, 6).getValue();
    if(amount === ''){
      amount = '0';
    }
    console.log('商品ID : ' + 'jsonObj[0].id / 売上個数' + amount + '+' + jsonObj[0].cnt);
    amount = Number(amount) + Number(jsonObj[0].cnt);
    itemsSheet.getRange(updateRow, 6).setValue(amount);
  }  
}

index.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>

  <body>
    <div style="padding: 2.0rem;">
      <h1 style="font-size:4.0rem;">読み込んだ商品を登録しますか？</h1>
      <p style="font-size:2.0rem;">商品ID : <?=id ?></p>
      <p style="font-size:2.0rem;">商品名 : <?=name ?></p>
      <p style="font-size:2.0rem;">枚数 : <?=num ?></p>
      <div style="font-size:2.0rem;margin-bottom:5.0rem;">
        <span>個数 : </span>
        <button style="font-size:2.0rem;"  class="button" id="down">－</button>
        <input style="font-size:2.0rem;margin:0rem 3rem;width:3em;" type="number" value="1"　step="1" id="itemNum">
        <button style="font-size:2.0rem;"  class="button" id="up">＋</button>
      </div>
      <button style="font-size:4.0rem;" id="register">登録する</button>
      <button style="font-size:2.0rem;margin-left:8rem;" id="cancel">キャンセル</button>
    </div>
  </body>
  
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script>
  <script>
    //HTMLのid値を使って以下のDOM要素を取得
    const downBtn = document.getElementById('down');
    const upBtn = document.getElementById('up');
    var itemNumTxt = document.getElementById('itemNum');
    const registerBtn = document.getElementById('register');
    const cancelBtn = document.getElementById('cancel');

    //ボタンが押されたらカウント減
    downBtn.addEventListener('click', (event) => {
      //1以下にはならないようにする
      if(itemNumTxt.value >= 2) {
        itemNumTxt.value--;
      }
    });

    //ボタンが押されたらカウント増
    upBtn.addEventListener('click', (event) => {
      itemNumTxt.value++;
    })

    //ボタンが押されたらタブを閉じる
    cancelBtn.addEventListener('click', (event) => {
      window.location.reload();
      window.top.close();
    })

    //ボタンが押されたら個数を登録・画面リロード
    registerBtn.addEventListener('click', (event) => {
      const jsonObject = {
        id: <?=id ?>,
        num: <?=num ?>,
        cnt: itemNumTxt.value
      };
      const jsonString = JSON.stringify(jsonObject);
      google.script.run
        .withSuccessHandler( Success )
        .withFailureHandler( Failure )
        .registerItem( jsonString ); 
    })

    function Failure(){
      alert("\r\n\r\n登録に失敗しました");
      window.location.reload();
      window.top.close();
    }

    function Success(res){
      alert("\r\n\r\n登録されました");
      window.location.reload();
      window.top.close();
    }
  </script>
</html>

QRコードで表示されるウェブサイト

スマホでQRコードを読み込んで、下記画面が表示されたら成功です。

個数の項目から、その商品の個数を増減させることができるようにしています。
「登録する」ボタンをタップすることで、GASのプログラムが実行され、スプレッドシートの該当商品の個数を変更させます。

エラーが表示される場合

初めてQRコードを読み込んでウェブサイトを表示させた時に、下記のようなエラー画面が表示されました。

こちらはブラウザのキャッシュが原因で表示されることがあるようで、使用しているブラウザのSafariのキャッシュを消去すると直りました。
消去は、スマホの「設定」>「Safari」>「履歴とWebサイトデータを削除」　から行えます。

まとめ

個人の趣味で作ったので、あくまでも自己責任でお願いします。
QRコードは色々と応用できそうで、イベントなどの集計とかにも使えるかなと思ったりも。
また何か使えそうなものができたら記事にしたいと思います。

参考サイト

作成するにあたり、下記サイトを参考にさせていただきました。ありがとうございます。

QRコードを読み込んで、宿題提出状況を管理できるスプレッドシートを作ってみた【GAS】｜わきた #教育 #Web3

GASでQRコード受付システムが出来た〜〜〜！

GAS

Posted by noramyao3