この記事はGoogle Apps Script Advent Calendar 2022の6日目の記事です
半導体部品の在庫数を知りたい
電機系のメーカーにて、組み込みのエンジニアをしている中で、現状の半導体不足の影響をもろに受けています。
ですので、製品を開発するときにこの部品って調達可能だっけ？とか、とりあえずこの部品ってどれくらい在庫あるのかな？
と、部品の調達ができるサイトを調べることがあります。
在庫数によっては、より安定的に調達が可能なものに振り替える必要があるので、割と大切な作業です。
僕はソフトウエア側のエンジニアなので、頻度は多くありませんが、半導体不足の影響もあり、昨日あったものが今日はない。なんてことが結構あって、特にマイコンが調達できるかは、組み込みエンジニアとしては開発に直結してきます。
必要ならさっさと買ってしまえば良いのですが、使うかどうかも未定だったり、予算の問題もあるのでそういうわけにはいきません。
ですので、必要そうな部品を定期的に確認したりしているのですが、これが結構面倒だったりします。
再度検索するのも面倒ですし、一々ブックマークするのも少し面倒ですし、そこの管理がそもそも面倒です。
なので、とりあえず現状ってどうなってるっけ？という確認がしたいなーと思っていました。
(また、部品の在庫状況の遷移がわかれば、唐突になくなったときに「いや、この日まではあったんだよ！」という言い訳にも使える)
というわけで今回はGoogle Apps Script(以下GAS)を用いて、部品調達ができるサイトから在庫数をスクレイピングして、そのデータをGoogleSheets(以下スプレッドシート)に記入し、その在庫状況を確認できるようにしたいと思います。
やりたいこと
実現したい機能としては大きく分けて3つあります。
① 監視したい部品のサイトを登録
② 監視したい部品の在庫数を定期的(1日単位など)に取得し、スプレッドシートに記録
③ 記録したデータを、グラフ等に表示して外からみれるようにする
この機能を、以下のように実装しようと思います。
完成品
スプレッドシートからAPI経由で在庫数を取得し、グラフ表示することができました！
一部データは、表示するためのダミーデータを入れています。
① 監視したい部品のサイトを登録
API経由でURLを送信し、スプレッドシートに監視部品のURLを追加します。
追加が完了すると、画面がリロードし、一番上に追加した部品のグラフが表示されます。
② 監視したい部品の在庫数を定期的(1日単位など)に取得し、スプレッドシートに記録
監視したい部品のURLから在庫数を取得する。
定期実行に指定した時間に、スプレッドシートに部品の在庫数が記録されます。
（下は1時間ごとに実行しています）
③ 記録したデータを、グラフ等に表示して外からみれるようにする
フロント側にて、監視部品のURLの追加と、部品の在庫状況をグラフにて確認できます。
グラフ表示の詳細は以下です。
実装方法
今回、部品の在庫状況を監視するサイトとしてマルツオンラインさんを使わせていただきました。
他にもいつくつ部品調達ができるサイトがあるのですが、サイトへの対応に時間が掛かりそうだったので、ひとまずマルツさんを使わせて頂いて、一通りの機能を作成しました。
構成としては、サーバ-側をGASで実装し、フロント側をReactにて実装しました。
React側の実装に関しての細かい手順は省いています。
環境
-
Google Apps Script
-
Paser v8
-
dayjs v1
-
react v18.2.0
-
react-hooks v1.0.1
-
react-chartjs-2 v5.0.1
-
axios v1.2.0
***** 詳しい実装方法は以下に記載しているので、知りたい方はクリックしてください *****
①-1 監視したい部材のサイトを登録(GASでサーバー側を実装)
以下赤字部分の実装をします。
この部分で必要な機能は以下となります。
- GASにてスプレッドシートにURLを記録する機能
- アクセスするAPIの作成
GASにてスプレッドシートにURLを記録する機能
今回は監視する部品のURLと、その在庫数を1つのシートで管理しました。
従って、監視する部品が増えるとシートが1つ増える設計となっています。
以下が、監視部品のシートを作成するコードとなります。
今回は初回作成時に、在庫数を取得し記録するようにしたので、取得時間と在庫数を同時に記録しています。
function addZaikoSheet(url="sample.html",zaiko_suu=3,title="title"){
// シートの取得
const sheet = SpreadsheetApp.getActiveSpreadsheet();
let newSheet;
try {
// シートの追加
newSheet = sheet.insertSheet();
// シート名を記入
newSheet.setName(title);
// セルにデータを記入
newSheet.getRange(1,1).setValue("登録URL");
newSheet.getRange(1,2).setValue(url);
// 時刻はライブラリのdayjsを使用
newSheet.getRange(2,1).setValue(dayjs.dayjs().locale('ja').format('YYYY/MM/DD hh:mm'));
newSheet.getRange(2,2).setValue(zaiko_suu);
}
catch(e) {
console.log(e);
// 同じシート名があると失敗する。
// 失敗してもシートが作成されるので、そのシートを削除する処理をいれておく
newSheet.setName("失敗シート");
// const sheet = SpreadsheetApp.getActiveSpreadsheet();
const trashSheet = sheet.getSheetByName("失敗シート"); //削除するシートの名前を引数に記入
sheet.deleteSheet(trashSheet); //スプレッドシートを削除
return false;
}
return true;
}
こちらはAPIアクセスがあったときに実行される大元の処理になります。
まず、スクレイピングしてから、シートの追加を実行しています。
// 登録API処理
function registerMoniData(url){
// 登録完了フラグ
let hasRegister = false;
try {
// 一度、監視部品URLをスクレイピングして情報を取得する
const {topic_zanSuu,topic_title} = scraping(url);
// シートを追加
hasRegister = addZaikoSheet(url,topic_zanSuu,topic_title);
}
catch(e){
console.log(e);
}
//JSONを定義
const result = {
name: `在庫数チェック`,
hasRegister: hasRegister,
}
// console.log(e);
//JSONを返答
return result;
}
シート追加参考URL：https://auto-worker.com/blog/?p=3209
シート削除参考URL：https://daily-coding.com/deletesheet/
アクセスするAPIの作成
APIを作成し、getのクエリパラメータにてURLを受け取る処理としました。
GASファイルにdoGet(e)関数を作成することで、getリクエスト時の処理を実装できます。
また、今回は登録処理に加えて、在庫数を取得する処理もあるため、クエリパラメータにて判定できるようregisterの有無で判定するようにしています。
function doGet(e){
let dataResult;
if (e.parameter.register){
// registerがTRUEならば登録処理
dataResult = registerMoniData(e.parameter.url);
}
else {
// 在庫数返答処理を追加
}
//JSONを定義
const result = {
name: `在庫数チェック`,
grafDatas: dataResult,
// アクセスをALL許可(ローカルアクセス時のための処理)
'headers': {
"Access-Control-Allow-Origin": "*",
"Access-Control-Allow-Methods": "POST,GET,PUT,DELETE",
"Access-Control-Allow-Headers": "Content-Type"
},
}
// //JSONを配信
return ContentService.createTextOutput(JSON.stringify(result))
.setMimeType(ContentService.MimeType.JSON);
}
doGet参考URL：https://qiita.com/riversun/items/c924cfe70e16ee3fe3ba
①-2 監視したい部材のサイトを登録(フロント側の実装)
以下の部分の実装をします。
APIへのアクセスはaxiosを用いました。
// 登録処理
const registerMoniBuhin = async () => {
// クエリパラメータを付与して、登録するURLをGASへ送る。REACT_APP_GAS_API_URLに作成したGASのAPIのURLが登録されています。
const results = await axios.get(`${process.env.REACT_APP_GAS_API_URL}?register=true&url=${inputUrl}`,
{
headers: {
'Accept': 'application/json',
'Content-Type': 'application/x-www-form-urlencoded',
}
});
}
axios参考URL：https://qiita.com/s_taro/items/30114cfa370aac6c085f
②監視したい部材の在庫数を定期的に取得し、スプレッドシートに記録
以下赤字部分の実装をします。
この部分で必要な機能は以下となります。
- 登録されたシートから、URLを読み取り、そのURLから在庫数をスクレイピングする機能
- 定期実行処理
登録されたシートから、URLを読み取り、そのURLから在庫数をスクレイピングする機能
登録されたシートから、URLを読み取り、そのURLから在庫数をスクレイピングする処理を実装しました。
取得した在庫数は、最終列の次の列に時刻と在庫数の順で記録されます。
スクレイピングはPaserというGASのライブラリを使用しました。
Paserに関しては以下を参照ください。
function regular(){
//A,使用するスプレッドシートの定義
const activeSheet = SpreadsheetApp.getActiveSpreadsheet().getSheets();
// シート数分forを回す(サンプルシート分は除いている)
for (let i = 1; i < activeSheet.length-2; i++){
const sheet = activeSheet[i].getRange(1,2);
Logger.log(activeSheet[i].getSheetName());
// シートからURLを取得
const values = sheet.getValues();
Logger.log(sheet.getValues()[0][0]);
// スクレイピング実行結果
const {topic_zanSuu,topic_title} = scraping(values[0][0])
Logger.log(topic_zanSuu);
Logger.log(topic_title);
// 最後の行数を取得
const lastRow = activeSheet[i].getLastRow();
// 取得時間と、個数を記録
activeSheet[i].getRange(lastRow+1,1).setValue(dayjs.dayjs().locale('ja').format('YYYY/MM/DD hh:mm'));
activeSheet[i].getRange(lastRow+1,2).setValue(topic_zanSuu);
}
}
全シート情報取得参考URL：https://daily-coding.com/getsheetname/
続いてスクレイピングの処理ですが、こちらはまず提携先在庫数を探して、それがなければ自社在庫数を確認するようにしています。
そのどちらもはなければ、納期待ち状態となっていると判断し、在庫数は0とするようにしました。
// マルツ(https://www.marutsu.co.jp/)から在庫データをスクレイピング
function scraping(url="https://www.marutsu.co.jp/pc/i/2351760/"){
// URLを指定
const response = UrlFetchApp.fetch(url);
// 指定した文字コードで読み込む
const text = response.getContentText("utf-8");
console.log(text);
// 提携先在庫が記載されているブロックを読み出す
let topic_zanSuu = Parser.data(text).from('class="digiKeyStockNum">').to('</span>').build();
// 部品名が記載されているブロックを読み出す
const topic_title = Parser.data(text).from('class="modelNo">').to('</span>').build();
// 在庫が提携先のパターンと自社在庫のパターンがあるので、提携先がないときは自社在庫のブロックを読み出す
// 判定には取得したデータの文字列数で判定する。提携先がない場合はすべてのデータが読み出させるので文字列長が長くなる
console.log(topic_zanSuu.length);
if (topic_zanSuu.length >= 1000){
topic_zanSuu = Parser.data(text).from('class="stockNum">').to('</span>').build();
}
if (topic_zanSuu.length >= 1000){
topic_zanSuu = 0;
}
console.log(topic_zanSuu);
console.log(topic_title);
// 部材名と在庫数を返答する
return {topic_zanSuu,topic_title};
}
Paserライブラリ参考URL：https://auto-worker.com/blog/?p=2460
定期実行処理
定期実行はGASのトリガ―画面にて「トリガーの追加」から設定することができます。
関数regularを選択し、間隔を1日ごととしました。
やり方は以下を参照ください。
GAS定期実行参考URL：https://auto-worker.com/blog/?p=6383
③-1 記録したデータを、グラフ等に表示して外からみれるようにする(GASでサーバー側の実装)
以下赤字部分の実装をします。
APIの作成は②にて実施しています。
全シートからシート名(部品名)と在庫数を取得し、APIでjsonにて返答できるようにします。
また、返答するjsonの構造は、グラフ表示する際に用いたnpmライブラリであるreact-chartjs-2のグラフのデータ構造となるようにしています。
// グラフデータ取得
function getChartData(){
//A,使用するスプレッドシートの定義
const activeSheet = SpreadsheetApp.getActiveSpreadsheet().getSheets();
// 全グラフデータ
let grafDatas = [];
// 最初のシート以外、全シートの情報を取得する(最初のシートは、無題シートして残しています)
for (let i = 1; i < activeSheet.length; i++){
// データセット構造
let dataSet = {
label: "",
data: [],
borderColor: "rgb(75, 192, 192)",
};
// ラベル構造
let labels = [];
// グラフデータ構造
let graData = {
labels:[],
datasets:[],
}
// シート情報を取得(2列目の1行目から、在庫数が記録されている最終列の2行目まで)
const sheet = activeSheet[i].getRange(2,1,activeSheet[i].getLastRow()-1,2);
// グラフのラベル名に、シート名(部品名)を入力
dataSet.label = activeSheet[i].getSheetName();
Logger.log(activeSheet[i].getSheetName());
// 取得したセルデータを全て確認する
const values = sheet.getValues();
for (let row in values) {
for (let col in values[row]) {
Logger.log(values[row][col]);
// セルデータから、取得日時か在庫数か判別し、グラフデータへ追加
if (col % 2 == 0){
const date = dayjs.dayjs(values[row][col]).locale('ja').format('YYYY/MM/DD hh:mm');
console.log("date".date);
labels.push(date);
}
else {
dataSet.data.push(Number(values[row][col]));
}
Logger.log(col);
}
}
console.log(dataSet);
// グラフデータを作成
graData.labels = labels;
graData.datasets.push(dataSet);
// 返答グラフデータにグラフを追加
grafDatas.push(graData);
}
console.log(grafDatas);
return {grafDatas};
}
jsonの例は以下をご覧下さい。
***** 返答jsonデータ *****
{
"name": "在庫数チェック",
"grafDatas": {
"grafDatas": [
{
"labels": [
"2022/12/06 09:11"
],
"datasets": [
{
"label": "24LC512-I/P",
"data": [
3
],
"borderColor": "rgb(75, 192, 192)"
}
]
},
{
"labels": [
"2022/12/05 11:08",
"2022/12/05 11:21",
"2022/12/06 12:21",
"2022/12/06 05:25"
],
"datasets": [
{
"label": "1L0534V24H0CA003",
"data": [
27,
27,
27,
27
],
"borderColor": "rgb(75, 192, 192)"
}
]
},
{
"labels": [
"2022/12/05 09:40",
"2022/12/06 09:40",
"2022/12/07 09:40",
"2022/12/08 09:40",
"2022/12/05 10:22",
"2022/12/05 11:22",
"2022/12/06 12:22",
"2022/12/06 05:25"
],
"datasets": [
{
"label": "1L0531Y22C0CA204",
"data": [
60,
61,
20,
60,
60,
60,
60,
60
],
"borderColor": "rgb(75, 192, 192)"
}
]
},
{
"labels": [
"2022/12/05 07:39",
"2022/12/05 07:40",
"2022/12/05 08:22",
"2022/12/05 09:22",
"2022/12/05 10:21",
"2022/12/05 11:21",
"2022/12/05 12:21",
"2022/12/05 01:22",
"2022/12/05 02:22",
"2022/12/05 03:22",
"2022/12/05 04:21",
"2022/12/05 05:22",
"2022/12/05 06:22",
"2022/12/05 07:22",
"2022/12/05 08:22",
"2022/12/05 09:22",
"2022/12/05 10:22",
"2022/12/05 11:22",
"2022/12/06 12:22",
"2022/12/06 05:25"
],
"datasets": [
{
"label": "0500DP44A1215E",
"data": [
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
20,
0,
0,
0,
0,
0,
0,
0,
0,
0
],
"borderColor": "rgb(75, 192, 192)"
}
]
},
{
"labels": [
"2022/12/05 07:39",
"2022/12/05 07:41",
"2022/12/05 08:22",
"2022/12/05 09:22",
"2022/12/05 10:22",
"2022/12/05 11:22",
"2022/12/05 12:22",
"2022/12/05 01:22",
"2022/12/05 02:22",
"2022/12/05 03:22",
"2022/12/05 04:22",
"2022/12/05 05:22",
"2022/12/05 06:22",
"2022/12/05 07:22",
"2022/12/05 08:22",
"2022/12/05 09:22",
"2022/12/05 10:22",
"2022/12/05 11:22",
"2022/12/06 12:22",
"2022/12/06 05:25"
],
"datasets": [
{
"label": "RN4870-I/RM130",
"data": [
2513,
2513,
2513,
2513,
2513,
2513,
2513,
2513,
2513,
2513,
2513,
2513,
2513,
2513,
2513,
2513,
2513,
2513,
2513,
2513
],
"borderColor": "rgb(75, 192, 192)"
}
]
},
{
"labels": [
"2022/12/05 07:40",
"2022/12/05 07:41",
"2022/12/05 08:22",
"2022/12/05 09:22",
"2022/12/05 10:22",
"2022/12/05 11:22",
"2022/12/05 12:22",
"2022/12/05 01:22",
"2022/12/05 02:22",
"2022/12/05 03:22",
"2022/12/05 04:22",
"2022/12/05 05:22",
"2022/12/05 06:22",
"2022/12/05 07:22",
"2022/12/05 08:22",
"2022/12/05 09:22",
"2022/12/05 10:22",
"2022/12/05 11:22",
"2022/12/06 12:22",
"2022/12/06 05:25"
],
"datasets": [
{
"label": "BGM11S12F256GA-V2R",
"data": [
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0
],
"borderColor": "rgb(75, 192, 192)"
}
]
},
{
"labels": [
"2022/12/05 07:40",
"2022/12/05 07:41",
"2022/12/05 08:22",
"2022/12/05 09:22",
"2022/12/05 10:22",
"2022/12/05 11:22",
"2022/12/05 12:22",
"2022/12/05 01:22",
"2022/12/05 02:22",
"2022/12/05 03:22",
"2022/12/05 04:22",
"2022/12/05 05:22",
"2022/12/05 06:22",
"2022/12/05 07:22",
"2022/12/05 08:22",
"2022/12/05 09:22",
"2022/12/05 10:22",
"2022/12/05 11:22",
"2022/12/06 12:22",
"2022/12/06 05:25"
],
"datasets": [
{
"label": "BM71BLES1FC2-0B02AA",
"data": [
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0
],
"borderColor": "rgb(75, 192, 192)"
}
]
},
{
"labels": [
"2022/12/04 04:33",
"2022/12/05 04:33",
"2022/12/06 04:33",
"2022/12/07 04:33"
],
"datasets": [
{
"label": "title",
"data": [
3,
4,
0,
6
],
"borderColor": "rgb(75, 192, 192)"
}
]
},
{
"labels": [
"2022/12/04 04:15",
"2022/12/05 04:15",
"2022/12/06 04:15",
"2022/12/07 04:15",
"2022/12/08 04:15"
],
"datasets": [
{
"label": "GAS追加シート",
"data": [
4,
5,
6,
7,
8
],
"borderColor": "rgb(75, 192, 192)"
}
]
}
]
},
"headers": {
"Access-Control-Allow-Origin": "*",
"Access-Control-Allow-Methods": "POST,GET,PUT,DELETE",
"Access-Control-Allow-Headers": "Content-Type"
}
}
取得セルの範囲指定参考URL：https://caymezon.com/gas-cell-range/#toc7
上記処理をAPIの受信処理に追加します。
if (e.parameter.register){
// registerがTRUEならば登録処理
dataResult = registerMoniData(e.parameter.url);
}
else {
// 在庫数返答処理を追加
getChartData();
}
③-2 記録したデータを、グラフ等に表示して外からみれるようにする(フロント側の実装)
以下赤字部分の実装をします。
APIからデータを取得します。
hooksのuseEffect()にてデータ取得処理を実行するようにします。
次にuseState()を用いて、grafChartに、取得したデータからグラフ用の在庫データを入れます。
// 起動時に初期値でグラフが表示されます
const [grafChart, setGrafChart] = useState([
{
labels: ["1 月", "2 月", "3 月", "4 月", "5 月", "6 月"],
datasets: [
{
label: "A社",
data: [65, 59, 60, 81, 56, 55],
borderColor: "rgb(75, 192, 192)",
},
// chartData[0],
],
}
]);
// 起動時にfech()を呼び出す
useEffect(() => {
fetch();
}, [])
// 在庫データ取得処理
const fetch = async () => {
// GASAPIにクエリパラメータなしでアクセスすると、在庫データが取得できる
const grafresult = await axios.get(process.env.REACT_APP_GAS_API_URL);
// grafChartにデータを代入
setGrafChart(grafresult.data.grafDatas.grafDatas);
console.log("grafChart : ", grafChart);
}
React hooks参考URL：https://qiita.com/seira/items/f063e262b1d57d7e78b4
あとは、grafChartをreact-chartjs-2のLineのdataプロパティに設定すればグラフが表示されます。
grafChartは配列となって複数の部品のデータがあるので、複数のグラフ表示をするためにmap関数を使用します。
{
grafChart.map((graf, index) => {
console.log(graf);
return (
<div className="App" style={divStyle}>
<Line
height={300}
width={300}
data={graf}
options={options}
id={"chart-key" + props.index}
/>
</div>
)
})
}
react-chartjs-2の参考URL：https://dev.classmethod.jp/articles/react-chartjs-2/
react-chartjs-2を使用するときに、参考URL通りにしてもエラーが出ます。
モジュールの読み込みが最低限のみ読み込まれるようになっているようで、一部モジュールが足りないのでエラーとなるようです。
下記記事にて、エラーを解消しました。
同僚に見てもらった
同僚に、今回の部品監視を見てもらいました！
良い点
- 在庫数をまとめて管理できるのは良い
- 部品情報を追加がやり易くて良い
- 定期実行の時間も変更可能なので、良い
- 部品ごとにグラフが分かれていて見やすい
改善点
- 同じような部品の情報を同一グラフにまとめられると良い（ソート機能）
- 他のサイトにも対応できたら、使えそう
- 納期情報などもあれば良い
- 監視している部品のURLにアクセスできるようにしてほしい(すぐ購入できるように)
- スマホの画面に合わせてみられると良い
グラフとして表示できる点は、結構好評をいただきました。
ただ、現状だとマルツさんしか対応していないので、他のサイトにも対応できたら使えそう、というところと、せっかくだから在庫数だけではなく、納期とかの情報もほしいよね。という話になりました。
この辺り、今後改善していけたらと思います！
最後までお読みいただきありがとうございました！