こんにちは。Shiftallブログに度々登場しております、佐藤です。
Shiftallの設立から約1年の月日が経とうとしていますが、 時間が経つのは早いものですね。
Shiftallでは現在にかけて様々なプロジェクトを走らせていますが、開発を進めていくなかで非常に大事になってくるのがMACアドレスやシリアルナンバー（個体製造番号）などの管理です。
弊社ではMACアドレスやシリアルナンバーは、製造・調達部門が発行、管理を行っています。
製品を実際に工場で組み立てていくときに、 MACアドレスやシリアルナンバーは「MAC」や「S/N」という表記でバーコード状にします。そのバーコードは製品本体に貼られ、組立工程の中でバーコードをスキャンして、各製品のソフトにそれぞれのMACアドレスとシリアルナンバーを書き込んでいきます。
これらの情報は、DVT や PVT などと呼ばれる試作から、そしてMPと呼ばれる量産のときに発行・管理するのですが、いちいち手打ち管理するのは非常に大変。そして当然ですがミスは許されません。人の手によらない自動でアドレスが発行されるシステムを導入する必要があります。しかし社内で管理するものなので無駄に豪華なシステムである必要もありません。
そのシステム、Google Apps Scriptでサクっと作っちゃいましょう。
Googleスプレッドシートを使えば、どこでも誰でもクラウドで閲覧することができますし便利です。Googleのサービスに組み込めるGoogle Apps Script（以下、GAS）で自動化のスクリプトを書けば、たいていの実務作業の自動化は解決します。
私は非エンジニア職のバックオフィスメンバーですが、昨年からGASを勉強し始め、会社を実務を回していくためのシステムをいくつか作ってきました。専門的なことになると難しいですが、ちょっとしたシステムなら作ることができます。GAS最高です。
今回はMACアドレスを自動で発行するシステムを例に、GASでどういうふうにシステムを作っていくのかざっくりご紹介していきます。
考えた仕組み
今回作ったシステムのざっくりとした仕組みです。
- まずGoogleフォームにMACアドレス発行に必要な情報を記入する
- フォームの内容に応じて、「MACアドレスの連番範囲を管理するシート」に自動でMACアドレスの情報が記入される
- 別の「MACアドレスとシリアルナンバーを一覧で管理するスプレッドシート」に、プロダクト別にMACアドレスが連番で記入される
こんな感じです。文字にするとわかりにくいので、図にしてみました。
今回はあくまでShiftallの例なので、すべてがみなさんの参考になるかはわかりませんが、ちょっとした手間のかかっている事務作業はGoogle Apps Scriptで自動化できるということが分かれば幸いです。
まず必要項目を記入するフォームを作ろう
まずはシステムの起点となるフォームを作成します。これはアンケートなどをGoogleフォームを作るのと同じ要領で作成します。
このような感じで、「必要数」「プロダクトコード」「開発ステージ」「発行者名」を記入できるようにしました。
次にこのフォームで回答が入力された時に、自動でスプレッドシートに行が追加され、回答をまとめて管理できるようにシートを作成します。
「回答」というタブに切り替えると、右側に「回答をスプレッドシートに表示」というボタンがあります。それをクリックすると回答一覧のスプレッドシートが作成されます。
その作成されたシートを見やすく整えたのがこちら。
黄色で囲った部分はフォームが入力されると既に自動記入されれる部分です。赤で囲った「MACアドレス」に関しては何かしらロジックを立てて自動で追加されるコードを作成する必要があります。
ここからGASを使ってコードを書いていきます。
MACアドレスを自動で付与するコードを書いていこう
MACアドレスというのは、ネットワーク機器やアダプタに付いている固有の識別番号のことです。原則すべての製品にそれぞれ違う番号が付与されています。1桁もしくは2桁ずつ16進数の番号で表記されます。
またMACアドレスは前半部分と後半部分に分けられており、前半部分についてはメーカー固有の番号で、後半は製品の個々のアドレスと決められています。
そのため、赤の部分で示したような個々の後半の番号を連番で付与できればよい、ということになります。数字が被らないようにロジックを組んであげることが重要です。
このシートで必要とされる文字列は、フォームで回答された必要数量分の「MACアドレスの連番の『最初』と『最後』」です。
MACアドレスは前半は事業者コードで固定できまっていますので、計算が必要なのは後半の部分になります。そうした前提をふまえて以下のロジックを立ててみました。
- 前回最後に発行したMACアドレスを取得
- そのMACアドレスの後半部分を10進数に変換し、何番目かを判断
- 「何番目」かの情報をもとに、必要な数の最初と最後のアドレスを発行（この時に10進数→16進数に変換する）
これもわかりにくいので、図に起こしてみました。
つまり最後に発行されたMACアドレス（16進数）を10進数に変換することで、連番でアドレスを作ることが可能になります。
ただ、このロジックは最後に発行されたMACアドレスがある場合なので、一番最初にMACアドレスを付与するケース、つまり「0から数える」場合には対応できません。条件分岐で0番目から付与するロジックも別途立てる必要があります。
コードを書いたら、フォーム回答時にスクリプトが動くようにトリガーを設定しよう
コードが仕上がったら、フォームを回答した時に自動でスクリプトが動くようにしましょう。その設定はGASの「トリガー」 機能で行います。
時計マークのアイコンをクリックするとトリガーの設定ページになるので、「イベントの種類を選択」を「フォーム送信時」にしておくことでフォームが回答されるとトリガーが走るようになります。
そうすると以下のような感じで、フォームを回答する度にスクリプトが動きMACアドレスが自動で付与されるようになります。
複数のスプレッドシートを連携させる方法
ここまで、さきほどの図でいうと、「１」と「２」の作成が完了しました。
次は「３」を作っていくことになります。
「３」のシートの作成とスクリプト自体は作れるかと思いますが、以下のような壁にぶち当たるかと思います。
- 「１」でユーザーが記入した情報や「２」で生成したMACアドレスを、「３」のシートはどのようにして受け取るか？
- そもそも、どのようにして 「３」のスクリプトを動かすか？
つまり「２」と「３」のシートをどのように連携させていくのか？という話になりますが、ここでは「WebアプリケーションURL」を活用する方法で解決していきます。
ざっくり図でまとめてみました。
１．事前にWEBアプリURLを発行する
「１」のシートに紐付いたGASのコードを動かすには、何かしらのトリガーとなるものを作る必要があります。しかし、今回は「フォームが回答されたら」というようなGoogleが用意しているトリガーは使えません。
そのため今回は「WEBアプリURL」というものを発行し、URLを叩いたら動くという仕組みを採用します。
手順としては、まずURLを叩いた時に動かしたい「３」のシートのスクリプト内のfunction名を「doGet(e)」にしておきます。
続いて、「公開」から「ウェブアプリケーションのとして導入…」という項目をクリック
するとこのような画面が出てくるので、「アプリケーションにアクセスできるユーザー」という項目を「全員（匿名ユーザーを含む）」にしておきます。
すると、doGet内のスクリプトを動かすことができるURLが発行されます。
２．トリガーとなるスクリプトにHTTPリクエストの処理を入れる
先ほど発行したURLをブラウザで叩くと、「３」のシートのスクリプトが動くようになります。その叩く処理を「２」のスクリプトの中に入れます。ちょっとむずかしい言葉ですが、これを「HTTPリクエスト」と呼びます。
「UrlFetchApp.fetch()」の引数に、先ほど発行したURLを入れればいいのですがリクエストを叩くときに「２」のシートで作ったMACアドレスなどの情報を送りたいので、その情報をパラメータとしてURLに組み入れることでリクエストを叩いた時に送れるようにしておきます。
var url = "https://script.google.com/a/xxxxx/macros/xxxxxx/exec?[パラメータ名]=[パラメータの値]/;
UrlFetchApp.fetch(url);
URLの「/exec」以下に、「?」をつけて「パラメータ名＝パラメータの値」を入れるだけです。複数のパラメータを送りたい場合は、それぞれの間に「&」をつけます。
３．パラメータを受け取るコードを仕込んでおく
「２」のシートでパラメータを送れるようにしましたが、「３」のスクリプト内で変数を定義して受け取ったパラメータを格納する仕組みを作る必要があります。
ちょっとわかりにくいですが、
e.parameter.［「２」のシートで指定したパラメータ名］
これでURLの中のパラメータを受け取ることができます。これで「２」から「３」に情報をおくることができるようになりました。
GASのWebアプリに関する詳しい内容については、以下の記事を参考にしてみてください。
■ Google Apps Script（GAS） で小さな Web アプリを作る – Qiita
文系バックオフィス職でもシステムは作れる！
おすすめリファレンスサイトまとめ
以上が、今回私作ったMACアドレスを自動で付与するシステムを作った大まかな流れでした。
「日々面倒だと思っている作業、やり方はわからないけど自動化できないだろうか…？」
という方、多くいらっしゃるかなと思います。バックオフィス職である私も1年前はそんな人間でした。GASに出会ってから自分の手で自動化を実現できるようになりました。
面倒な作業の一つ一つの手順を具体的にイメージできるのであれば、あとはそれを「実現化する手段としてのGAS」を覚えればよいだけです。
しかし、プログラマーでない人間がどのようにしてGASを身につけていくか？
これは残念ですが何度もググりながら覚えていくのが一番の近道かと思います。自分で能動的に情報を調べて実践していくほうが血肉となっていくはず。特にGASはいろんな人が情報をWeb上に公開しているので、調べればだいたい欲しい情報はでてきます。
最後に、初心者でも参考になるGASサイトを上げておきますので、もしよければご参考ください。
■【保存版】初心者向け実務で使えるGoogle Apps Script完全マニュアル｜いつも隣にITのお仕事
まずはこちらはタカハシノリアキさんという、初心者でもわかるGASの技術書を出されている方のサイトです。実務で使えるGoogle Apps Scriptの具体的な書き方をいろいろとまとめられているので非常にありがたい存在です。
■ Google Apps Scriptリファレンス
次に、このサイトだけに限らないですが、リファレンスサイトです。「ここどういう風に書けばいいんだろう？」と思った時に、ググると例が書かれているこうしたサイトにたどり着くと思います。こうしたサイトもかなり重宝します。
■ Qiita
技術者なら誰もが知っているユーザー投稿型サイト「Qiita」です。掲載されている内容は応用的なものが多いですが、ある程度GASになれてくるとこちらも参考になると思います。検索の時に「tag:GoogleAppsScript」と追加しておくと、GASに関連した記事に絞って検索できるのでおすすめです。
ちょっとプログラミングをかじってみたい人こそ Google Apps Script はおすすめです。ぜひお試しください！