どうも。つじけ（tsujikenzo）です。このシリーズでは「棚卸データの突合」についてお届けしています。全3~4回の今日は2回目です。
前回のおさらい
前回はバーコード（突合するデータの基礎になります）の作りを簡単にご紹介しました。
今回は「現場で拾ってくるデータとデータベースのデータ」のお話です。
現場で拾ってくるデータ
実際に倉庫でリーダーを使ってバーコードのデータを拾っていくわけですが、今までExcelだったのを今回はスプレッドシートに変更してみたいと思います。まず、空のスプレッドシート[01_棚卸君]を用意して1行目に見出し行を設定します。
セル[A2]にカーソルを合わせてバーコードリーダーでバーコードをスキャンします。
このように入力されて改行が挿入されてます。
リーダーから送られてくるデータに改行データも含まれているので、入力されると同時にセルが下の行に移動するのは便利ですね。これは設定で横に移動（TAB押下と同様）にも変更できます。
バーコードを解析する
前回お伝えした通り、弊社のバーコードは「商品ID」「賞味期限」「納品書番号」から成り立っています。なのでA列に入力されたデータを再び3つのデータに分解して、B列、C列、D列に展開してみましょう。コンテナバインドで書きます。
まずはいつものように全体をガバっと取得して、見出し行を削除しておきます。
const values = SpreadsheetApp.getActiveSheet().getDataRange().getValues();
values.shift();
弊社のバーコードは先頭から6桁が商品を示す「商品ID」、末尾12桁が「納品書番号」、残りが「賞味期限」という仕様になっています。
先頭6桁と末尾12桁は文字数のみで抽出できますので、.slice()メソッドでやっつけます。
先頭6文字・・・A列.slice(0, 6)
末尾12文字・・・A列.slice(-12)
中央はA列から先頭と末尾を削除（空文字に置換）したものと言えますので、.replace()メソッドでやっつけます。
中央？文字・・・A列.replace(value[0].slice(0, 6),'').replace(value[0].slice(-12)
.replace()メソッドの戻り値は文字列なので、このようにreplaceを数珠つなぎに記述できますので覚えておきましょう。（.replaceごとに改行する書き方もよく見ます。）
3種類の値の定義ができたので、後はfor文でまわすと思いきや.map()メソッドで一発です。コードが横長になってしまいますが💦可読性が落ちてますかね。すみません。。。
const convertedValues = values.map(value => {
return [value[0],value[0].slice(0,6),value[0].replace(value[0].slice(0,6),'').replace(value[0].slice(-12),''),value[0].slice(-12)];
}
);
2次元配列に分解できているようです。スプレッドシートに張り付けてみましょう。
スプレッドシートに出力
できあがったコードはこちらです。
function myFunction() {
const values = SpreadsheetApp.getActiveSheet().getDataRange().getValues();
const subjects = values.shift();
const convertedValues = values.map(value => {
return [value[0],value[0].slice(0, 6),value[0].replace(value[0].slice(0, 6),'').replace(value[0].slice(-12),''),value[0].slice(-12)];
}
);
convertedValues.unshift(subjects);
SpreadsheetApp.getActiveSheet().getRange(1,1,convertedValues.length,convertedValues[0].length).setValues(convertedValues);
}
このようなシートが、
このように変化しますね。
[商品ID]を抽出したことで、後々マスタテーブルと照合すると商品を特定することができるので便利ですね。VLOOKUPや様々な照合の方法がありますが、今回は割愛します。
在庫表（データベース）のデータ
このような在庫表は良くあると思いますが、[部品ID]（商品IDと呼んでいたフィールドです）と[賞味期限]と[納品書番号]があれば、データの突合ができそうですね。※画面の都合で一部です。
ただし、どのテーブルにも言えることですが、テーブル全体をvaluesとして取得した時、values[行][列]（例：values[i][2]が部品ID、values[i][5]が納品書番号）のように列を指定して値を取得してしまうと、列の削除や移動があった場合にメンテナンスが大変です💦最初の頃は覚えてますしデータ量も少ないのでいいですが、必ずこの問題にぶち当たるものです。次回、解決しましょう。
まとめ
さて、今回は「現場で拾ってくるデータとデータベースのデータ」についてまとめました。必要なデータは出揃ったように見えますが、データの取得をする方法について「列を配列のインデックスで指定すると列の移動や削除があった時に管理が大変💦」という問題が浮かび上がりましたね。
次回は「テーブルを連想配列で取得する」についてお届けします。
