GAS完全理解チュートリアル：在庫管理ツールを作成してみよう。
日々の業務でどうしても単純作業発生してきますよね？
やりたくないなーとは思いながらもやらないと仕事が進まない、そんな作業はできるだけ自動化したいよね。ということでキャディアドベントカレンダー9日目、ここまでとは少し趣向を変え、GAS（特にスプレッドシートの操作）のチュートリアルを書きました。
最後まで完走すると、所要時間半日程度は必要になるかと思います。(長っ)
もし自動化、GASには興味ないけどキャディには興味あるよ！という方がこのnoteを開いてくださっていたら、目次からこのnoteの1番最後に飛んで頂けましたら、キャディの「会社紹介・採用説明資料」と「The letter from CTO」のスライドが置いてあります。
本チュートリアルでは手を動かしながら、初めてGASに触る方でもつまづかないように、できるだけ全ての解説に具体例をつけながら解説しているので、かなりとっつきやすくなっているのではないかな？と思っています。
この記事は、CADDi Advent Calendar 2021の9日目にエントリーしています。
直近、社内でGASをゴニョゴニョする機会があったのですが、日本語でGASに関する記事をググると沢山ヒットするものの、人におすすめできるものが少なかったので、この機会にチュートリアルを作ってみようと思いたち書いてみました。
（社内で必要だったという事情もあります笑）
本当は、さらに快適に開発していくためのTipsなども盛り込みたかったのですが、１から１０まで説明していくと分量がとんでもないことになるということに途中で気がつき、今回は断念しました。
しかし、このチュートリアルを完走していただけば、スプレッドシートのよく使う操作は一通りできるようになるのではないかなと思います。これをきっかけに、少しでも自動化などに興味を持ってくださる方が増えたら嬉しいです。
Google公式のチュートリアルも英語では存在しているので、英語読むのも苦じゃないよ、自分にはDeepLがついているよ、という方は公式チュートリアルをやってみてもいいかもしれません。
流石のGoogleさん、Gsuiteの機能を組み合わせて開発することができるテンプレートを20種類近くも用意されています。セールスレポート作成自動化ツールや、Google Formから送られてきたフィードバックに対して自動で返信メールのドラフトを作成ツールなど、ほぼそのまま流用できそうなものもあるので、この記事を読んでGASに興味を持ってくださった方は次のステップとして取り組んでみると面白いかもしれません。
対象読者
日々ひたすらスプシ間の転記を繰り返し仕事が嫌になりそうな方
大人数が触るカオスなスプレッドシートの管理者の方
事務作業は極力無くして本業に集中する時間を作りたい方
プログラミングはしたことがないけど、なんかかっこいいなと思っている方
GASってなに？
ここまでGAS GASと書いてきて今更なんだという感じですが、Googleさんによると
Apps Script は、Google Workspace の統合、自動化、拡張のためのビジネス ソリューションをすばやく簡単に構築するための唯一のローコード プラットフォームです。
ということです。（？）
流行りのワードも散りばめられつつかっこよく記載されていますが、要は「Googleが提供するサービスを自動化、サービス間で連携するために使えるツール」ということです。
正式にはGoogle Apps Script、略してGASです。Googleが用意した環境上のみで動く、エクセルでいうところのマクロ、プログラミング言語でいうところのJavaScriptやPythonなどのようなものと考えて頂けるとイメージしやすいかと思います。しかし、
JavaScriptやPythonほど機能が多くない
PCとネット環境、Googleのアカウントさえあれば誰でも前準備なしで使える
といった理由から、今までプログラミングの経験などない初心者の方でも比較的とっつき易い仕様になっています。
GASでは何ができるの？
では、具体的にGASでどんなことができるの？というと、
スプレッドシート関連
1日1回100個のスプレッドシートに何か同じものを書き込んでいく作業の自動化
関数では計算が難しい構造の取り扱い（この後チュートリアルで行う在庫管理など）
Google Drive関連
あるフォルダ内の全部のファイルのURLとファイル名一覧を作りたい
フォルダ分けができていなかったから、ファイル名のルールに則ってマイドライブにあるファイルをフォルダ分けしていく
Gmail関連
CRMのように、顧客リストに対して名前と商品名など、本文の一部だけを変更しながらメールを一斉に送信する
分析するために、受け取ったメールの本文を全てスプレッドシートに落としてくる
などなど、Googleが提供しているかサービスであれば、作業要件を明確に定義することができれば自動化できるものが多いです。その他にもHTTPリクエストを送ったり、BigQueryと連携したりといったことも可能なので、少し工夫すれば他のサービスと組み合わせて利用することも可能です。（そこまでGASするか？という問題は置いておいて。。。ｗ）
GASでできないことは？
ここまで書くと、完璧に思えて全てをGASで自動化したくなってくるところなのですが、もちろんGASではできないことも沢山あります。例えば、
処理のルールが決まっていない作業
数十万レコードを超える大量のデータを保持する作業（BigQueryなどと連携すればできなくはないですが）
などなどざっくりいうと、１から１０まで指示することができない業務、完全マニュアル通りにこなせばOKではない判断が入る業務は自動化することができません。
チュートリアル：在庫管理ツールを作ってみよう！
さて、前置きが長くなりましたが、ここからは実際に手を動かしながらGASを感じて頂ければと思います。
この記事で、「エクセルはスポーツ、ショートカットはスポーツ(原田, 2021)」と明言が書かれていますが、GASもスポーツです。関数の使い方を一つ一つドキュメントで読んでも使えるようにはなりません。手を動かすことが上達への近道です。
ということで、ここからは実際にECサイトなどの倉庫業務をイメージしながら在庫管理ツールを作成してみましょう。
今回はチュートリアルということで、初めてGASに触れる方が詰まりそうな部分も多少盛り込みつつ、分かりやすいように必要最低限の機能のみ実装することにしています。そのため、実際に在庫管理業務をする上で物足りない点も多々あるかと思いますが、その点はご了承ください。
チュートリアルが完成した際に、より興味が湧いた方は追加機能を実装してみたり、明日から業務で使えるツールを作ってみたりしていただけると嬉しいです。
完成イメージ
各種シート
＊コードの全体像は具体手順の後に記載しておきます。
全体像の解説
作り出す前に、まずはこれから作るものの全体像を簡単に把握していきましょう。
output>>>
アウトプットとして、最終的にユーザーがみることになるのが「在庫ダッシュボード」「出荷履歴」の２つのシートです。
「在庫ダッシュボード」
今倉庫に残っている在庫の総数を出しています。同一製品が複数回にわたって倉庫に入荷されてきたり、出荷されたりしていく中で、今倉庫には何がいくつあるのかを把握するためのダッシュボードです。
「出荷履歴」
これまでの出荷の履歴を出力しています。いつ、何を、何個出荷しているのか後から確認したくなった時にはこのシートを参照します。
data>>>
「入荷一覧」「受注一覧」の２つのシートが今回活用するデータとして存在しています。
「入荷一覧」
文字通り、倉庫に入荷されてくる製品の一覧です。スクショを見るとわかる通り、シートには同一製品が複数回に渡って入荷されていたりします。入荷数量の隣には費消数量が記載されており、これまでにいくつ出荷されているのかがわかるようになっています。
「受注一覧」
こちらも文字通り、顧客から注文が入った商品の一覧です。顧客から注文が入るたびに、スプレッドシートの1番下にデータが追加されていくイメージでしょうか。
このデータには、出荷済みフラグが付与されるようになっており、それぞれ受注製品と紐付けてどの製品が出荷済みで、どの製品がまだ出荷されていないのかがわかるようになっています。
input>>>
「出荷対象_注文番号」「出荷登録ボタン」と２つのシートがあります。これら２つのシートが、ユーザーがデータをインプットしたり、操作をしたりする際に活用するシートです。
「出荷対象_注文番号」
ユーザーが注文番号をインプットして、出荷する製品を登録していくためのシートです。
「出荷登録ボタン」
注文番号をインプットしたのちに、このシートからボタンを押して作成されたGASスクリプトを起動します。そうすることによって、各種シートの情報をもとに在庫ダッシュボード、出荷履歴シートをはじめとした各シートが更新されます。
具体手順
各種シートの役割が理解できたところで、ここからは具体的な手順を説明していきます。
1. 新規でスプレッドシートを作成
Google Driveの闇に飲まれても検索可能なわかりやすい名前が望ましいです。
ここでは、在庫管理ツールとしています。
2. シートを作成
この際のポイントは、output / data / inputをシート毎に明確にわけておくことです。日頃からやっている方からしたら、「今更そんな当たり前のこと言うなよ社会人１年目でできるわそんなん」案件かもしれませんが、そのぐらい大事です。データ分析をする時などはもちろんのこと、GASで何かツールを作る際には、ユーザーが触ることができる部分、基本的に触らずに見るだけの部分、管理者だけが触る部分でわけて、シートやセルの保護もかけておけると尚よしです。
3. データを作成
今回はサンプルツールなので、デモデータを用意しておきました。
入荷一覧と受注一覧のCSVを貼り付けておきましたので、必要であればダウンロードして活用して頂ければと思います。
dataの部分に関しては、弊社では
「社内システム->BigQuery->スプレッドシート」
間でデータを連携させて常にフレッシュなデータが入るようにしてツールを作っていたりします。
その他にも、私が所属するチームではCloud FunctionsやSchedulerなど、GCPの各機能、時にはRPAツール、時にはColabなど既存のツールをフル活用して定型業務を自動化していっています。
4. GASを書く
これで下準備は完了です。これからはGASを書いていきましょう。GASを書く際には、何かツールなどを別途用意する必要はありません。スクショの「<> スクリプトエディタ」をクリックしたら、エディタが開いてGASを書き始めることができます。
クリックして先に進むと、こんなページに行き着くはずです。ここにコードを書いていきます。
わかりやすいように「無題のプロジェクト」から任意のプロジェクト名に変更しておきましょう。
5. 各種定義
コードを書くためのファイルを作成していきます。赤枠で囲った部分の “+“ マークをクリックして、「スクリプト」を選択するとファイルを追加していくことができます。全部のコードを１ファイルにまとめて書くこともできるのですが、機能毎に分かれていた方が把握しやすいので今回は機能毎に分けて作成していきたいと思います。
まずはそれぞれが何を意味しているのか、簡単に説明していきましょう。
_define.gsファイルのコード
// このファイルには、各関数で繰り返し使う共通の定義を書いていく
// 操作対象のスプレッドシートを指定
// 今回はスプレッドシートに紐付けたGASを作成しているので以下方法で指定
const ss = SpreadsheetApp.getActiveSpreadsheet();
// それぞれのシートを１枚ずつ指定
// output>>>
const sheetDashboard = ss.getSheetByName("在庫ダッシュボード");
const sheetShipmentHistory = ss.getSheetByName("出荷履歴");
// raw_data>>>
const sheetStockList = ss.getSheetByName("入荷一覧");
const sheetOrderList = ss.getSheetByName("受注一覧");
// input>>>
const sheetOrderNumberInput = ss.getSheetByName("出荷対象_注文番号");// このファイルには、各関数で繰り返し使う共通の定義を書いていく
// 操作対象のスプレッドシートを指定
// 今回はスプレッドシートに紐付けたGASを作成しているので以下方法で指定「/**/」で囲んだ部分＆行内で「//」より右にくる部分はコメントとして扱うことができます。コメントはコードの動きには影響を与えないメモのような機能です。適切なコメントをつけておくことで、後々自分で見返した時や他の人がコードを見た際に、そのコードが何をしているのか分かりやすくすることができます。
「//」の使い分けに関するルールは特に決まっていませんが、ルールは統一しておくとパッとみて分かりやすくなるでしょう。今回は、関数の冒頭説明には「/**/」、その他のコメントは「//」を使うスタイルにしました。
const ss = SpreadsheetApp.getActiveSpreadsheet()GASでは何ができるの？で記載しましたが、プログラムで何かを操作する際には馬鹿馬鹿しいほどに１つ１つの操作を定義していく必要があります。そのため、まず最初に「今回はどのスプレッドシートを扱うのか」をここで定義しています。最初なのでもう少し詳しく説明していきます。
まず “=“ よりも左側（以下左辺と呼ぶ）の “const ss“ です。 これは「ss という定数に何かを割り当てていくよ」という宣言をしています。定数って何？など疑問が出てくるかもですが、ここでは「プログラム内の他の場所から “ss“と記載するだけで “SpreadsheetApp.getActiveSpreadsheet()” と記載した時と同じ効果が得られる。」と覚えておきましょう。
次に、 ”=” よりも右側（以下右辺と呼ぶ）の “SpreadsheetApp.getActiveSpreadsheet()” です。これも、詳細に入るとながーくなってきてしまうのでここでは「現在起動されているスプレッドシートを指定している」と思って頂ければと思います。
ちなみに、“SpreadsheetApp.getActiveSpreadsheet()”のようなものはGASにあらかじめ用意されている機能群でして、適宜呼び出して使うことができます。気になる方はこちらから全貌を覗きにいくことができます。開いた瞬間「うっ」となった方はそっとタブを閉じましょう。ここに載っている機能のうち、実際に今回使うのは一部だけなのでこれを全部覚える必要はありません。中にはほとんど登場機会がない機能も沢山ありますし、もし必要になった際には適宜ググればOKです。
const sheetDashboard = ss.getSheetByName("在庫ダッシュボード")さて、どのスプレッドシートを使うかは定義ができました。次はどのシートを使うかをそれぞれ定義していきます。今回も左辺は同じですね。sheetDashboardという定数に右辺を割り当てます。右辺ですが、先ほど定義した “ss“ が登場しました。”ss” に対してドットで繋がり “getSheetByName(“在庫ダッシュボード“)“が続いています。ここから少しややこしくなってくるかと思いますが、諦めずに読み進めてみてください。”getSheetByName()”は、”Spreadsheet”オブジェクトに対して使うことができる関数です。”Spreadsheet”オブジェクトってなんぞや？と思うかもしれませんが、先ほど作成した”ss” が持っている属性と考えていただけると分かりやすいでしょうか？ポケモンで例えると、水タイプのポケモンは「たきのぼり」を技として使うことができるけど、炎タイプのポケモンは「たきのぼり」を使うことができない。など、属性毎に使える技が固定されてくるかと思うのですが、GASでも同じです。全ての要素はオブジェクトという名の属性を持っており、オブジェクトの種類によって使える関数が変わってくるのです。
話を戻すと、以下のコードでは、”ss” に対して ”getSheetByName(“在庫ダッシュボード“)” を呼び出して、在庫ダッシュボードシートを “sheetDashboard“ 定数として定義しています。この時、”sheetDashboard” は “Sheet“ オブジェクトです。
6. 出荷履歴：インプット情報取得 / getTargetOrder()
ここまでで、使い回す定数の定義が完了しました。ここからは、定義した定数たちを使ってデータの操作をしていきます。いよいよ自動化ぽくなってきましたね。
出荷履歴を記録するための機能をこれから作成していきますが、全体としては以下の流れで進めていきます。
ユーザーがインプットした出荷対象注文番号の取得
１をもとに、受注一覧から対象製品を抽出する
２と入荷一覧とを比べて、出荷する数量が問題なく在庫として存在しているかどうかをチェックしていく。この時に入荷数量からどれだけ費消したのかわかるように費消数量を入荷一覧のデータに計算して入れていく。
出荷した製品がどれかわかるように、受注一覧に対して出荷済みフラグをつける
最後に、これまでの処理が問題なく完了していたら各種シートにデータを書き戻す。
まずは現在の “_define.gs“ ファイルから、”prepareShipment.gs” ファイルに移動します。ファイルを作成した時と同様、”prepareShipment.gs” ファイルをクリックしたら移動できます。移動したら、こんなコードだけが存在しているはずなので、これを削除します。
そして、以下の関数を”prepareShipment.gs”の中に書いてみましょう。新しい記述が出てきましたので、順番に説明していきましょう。
/**
* 出荷対象_注文番号シートに入力した値を取得するための関数
* @return {Array} targetOrderArray 出荷対象である注文番号をスプシから取得して返す
*/
function getTargetOrder() {
// 出荷対象_注文番号シートに入力されている情報を抜き出す
const dataTargetOrder = sheetOrderNumberInput.getDataRange().getValues()
// 二次元配列を扱いやすいように１次元配列に変換する
const targetOrderArray = dataTargetOrder.flat()
// console.log(hogehoge)はhogehogeに入れたものを出力して確認するのに使う
console.log(targetOrderArray)
// returnで書いたものが、この関数を呼び出した際に結果として返ってくる
return targetOrderArray
}function getTargetOrder() {
....
....
....
}最初のコメントについては後から説明するとして、まずは5行目以下を先に説明します。この、”function getTargetOrder() {……}”となっている部分が、「関数」という概念になります。関数は、何か処理を行う際のまとまりです。これ以降、基本的に処理の中身は全て関数として作成していきます。関数は任意の箇所から呼び出して使用することができます。（正確には、関数内に定義した関数は定義した関数内でしか呼び出せない。など色々あるのですが、今回こういったことはしないので無視してください。）
一度に大きな処理のまとまりを作成しようとすると難しいので、小さな処理のまとまりを関数としてたくさん作って、それらを呼び出して使用することで最終的なプログラムを作成していきます。こうすることで、プログラムに何か間違えがあったときにどこが間違えているのか探すのが楽になり、あとで幸せになれます。プログラムを書くと何かしら必ず間違うので、少し面倒でも小さな関数を作るようにしていきましょう。
/**
* 出荷対象_注文番号シートに入力した値を取得するための関数
* @return {Array} targetOrderArray 出荷対象である注文番号をスプシから取得して返す
*/ここは既出のコメントですね。ただ、見たことがない形をしています。これは完全に任意の部分なのですが、関数を記述する際に、その関数が返す型はなんなのかを分かりやすくするためにコメントとして記載しています。上述の通り、関数は呼び出して使うものなので、その関数を呼び出した際に何が起こるのかがパッとわかると嬉しいですよね。
“@return“ ：返り値がなんのオブジェクトなのかを定義しています。
“{Array}”：波括弧の中身が返り値の型（オブジェクトの種類）を表しています。
ここでまた新しい言葉が出てきました。
返り値（return）：関数を呼び出した際に返される値のことです。
例えば、以下のように定数 “result“ に対して、”getTargetOrder()” を割り当てた際に、result = 返り値（return）になります。
const result = getTargetOrder()これまでは関数の外見を見てきました。ここからは関数の中身です。
// 出荷対象_注文番号シートに入力されている情報を抜き出す
const dataTargetOrder = sheetOrderNumberInput.getDataRange().getValues()
// 二次元配列を扱いやすいように１次元配列に変換する
const targetOrderArray = dataTargetOrder.flat()
// returnで書いたものが、この関数を呼び出した際に結果として返ってくる
return targetOrderArray新しいものの連続なので、１行ずつ説明していきます。
const dataTargetOrder = sheetOrderNumberInput.getDataRange().getValues() 左辺は今までと同じです。右辺ですが、 “sheetOrderNumberInput“に対して、２つの関数を連続して呼び出しています。これは、以下のように書くこともできます。これで、見え方は今までと同じになりましたね。あるオブジェクトに対して関数を呼び出している状態です。
const data1 = sheetOrderNumberInput.getDataRange()
const data2 = data1.getValues()まず、”getDataRange()” 関数ですが、Sheetオブジェクトに対して呼び出すことができる関数で、データが入っている範囲全てを表す Rangeオブジェクトを返り値として返します。Rangeオブジェクトとは、名前の通り、スプレッドシートのセルの範囲を指定したオブジェクトです。これでスプレッドシート内の範囲を選択できている状態です。
スプレッドシートの操作で表すとこのように範囲を選択した状態です。
今回は「出荷対象_注文番号」シートに対して呼び出したので１列だけですが、例えばこのように書くと
const data = sheetStockList.getDataRange()以下のように、複数行、複数列のデータが入っている全ての範囲を指定することができます。
注意：離れたセルに１つでも値が入ってしまっていると指定する範囲が1番遠いセル基準で決まってしまい、以下のように範囲選択した状態になってしまいます。
イメージが湧きましたね？次はここで取得したRangeオブジェクトに対して、”getValues()” 関数を呼び出しています。”getValues()”関数は先ほど選択した範囲内の値を全部取得する関数です。取得した値はどうなるのかというと。。。。
例として、仮に１−３行目までだけ選択している状態で見ていきましょう。ちなみに、この状態は、以下のように書くことができます。
const data = sheetStockList.getRange(1,1,3,4)
// getRange(始まり行, 始まり列, 終わり行, 終わり列)
これに対して、”getValues()” したとしましょう。すると、返り値は、以下のようになります。
[["入荷日", "製品コード", "製品名", "数量"],
[Sat Oct 02 2021 00:00:00 GMT+0900 (Japan Standard Time),'LS1000','リニアシャフト',2],
[Mon Oct 04 2021 00:00:00 GMT+0900 (Japan Standard Time),'BE1000','ベアリング',100]]なんだか[]が沢山出てきました。[]で括られた値は、配列（Array）と呼びます。配列は中身を持つことができて、その中身が”入荷日”, “製品コード“などです。これらの中身はコンマで区切られており、１つ１つ取り出して使用することができます。文字列を取り出したり、数字を取り出して計算したりといった処理をこれから書いていくので、ここではいったん、「配列の中に色々なデータを入れることができて、それらのデータは１つづつ取り出して活用することができる」と考えて頂ければと思います。詳細は後ほど説明します。
話を出荷対象_注文番号シートに戻します。これまでのルールに従って考えると、
const dataTargetOrder = sheetOrderNumberInput.getDataRange().getValues()はつまり
dataTargetOrder = [ [ '出荷対象注文番号' ], [ 'SN0001' ], [ 'TS0001' ], [ 'NG1000' ] ]という状態になっています。この時、定数”dataTargetOrder”は、最後に呼び出した関数の返り値のデータ型になっており、”getValues()” 関数はObject[][]オブジェクトを返しています。先ほど紹介した配列が入れ子になっているパターンのオブジェクトで、二次元配列と呼びます。よくわからなくてもいったんOKです。そういうものがあるのか。ぐらいでいったん次にいきましょう。
const targetOrderArray = dataTargetOrder.flat()
// targetOrderArray = ['出荷対象注文番号','SN0001','TS0001', 'NG1000']ここでは、先ほどの二次元配列である “targetOrderArray”定数に対して ”flat()” 関数を呼び出しています。”flat()” 関数は、２次元配列を１次元配列（通常、１次元配列はただ「配列」と呼びます。）に変換するための関数です。
なぜ変換する必要があったのでしょうか？それは、それぞれの要素へのアクセスが容易になるためです。
先ほどの”dataTargetOrder”を例にとって説明していきます。
まず、２次元配列の場合です。
この中から、それぞれの要素を単体で取り出したい場合には、以下のように取り出します。
dataTargetOrder = [ [ '出荷対象注文番号' ], [ 'SN0001' ], [ 'TS0001' ], [ 'NG1000' ] ]
dataTargetOrder[0][0] // '出荷対象注文番号'
dataTargetOrder[1][0] // 'SN0001'
dataTargetOrder[3][0] // 'NG1000'なんとなくパターンが見えてきましたかね？
よりわかりやすくするために、１次元配列に変換したあとの状態でそれぞれの要素を取り出してみましょう。
const targetOrderArray = dataTargetOrder.flat()
targetOrderArray[0] // '出荷対象注文番号'
targetOrderArray[2] // 'TS0001'そうです、配列から個々の要素を取り出すためには、
配列[要素位置]
と記載することで、取り出すことが可能です。要素位置は0からスタートする点がポイントです。
これを、二次元配列に応用すると、
二次元配列[1番外側[]中での要素位置][指定[]中での要素位置]
となります。二次元配列は少しわかりづらいと思うので、サンプルをもう一つ見てみましょう。
const sample = [[1,2,3], [4,5,6], [7,8,9]]
// 緑：sample[0][2] = 3
// 青：sample[2][1] = 8
イメージが湧きましたでしょうか？
// console.log(hogehoge)はhogehogeに入れたものを出力して確認するのに使う
console.log(targetOrderArray)
// [ '出荷対象注文番号', 'SN0001', 'TS0001', 'NG1000' ]コメントにも記載があるように、“console.log(hogehoge)” はhogehogeの内容を出力して、hogehogeに何が入っているのか確認するために使います。
”console.log(hogehoge)” を用いて、計算の途中結果を確認したり、バグが発生した際に途中経過を確認しながら修正していくために使います。
ちなみに、”hogehoge”には特に意味はなく、「何かを入れて使うよ」という意味で適当な文字列を入れているだけです。”hogehoge”には定数や変数だけでなく、文字列や数字を直接入れて、プログラムを実行した際にどこまで実行することができているのかを確認するためにも使うことができます。
console.log("getTargetOrder実行開始")
// getTargetOrder実行開始
console.log("実行終了。次の関数へ")
// 実行終了。次の関数へこんな感じで、関数の最初に関数名を入れておいたりすることが個人的には多いです。
// returnで書いたものが、この関数を呼び出した際に結果として返ってくる
return targetOrderArraygetTargetOrder関数最後の行にきました。
”return hogehoge” では、関数を実行した結果、返す値を記載します。
function hoge() {
return "hoge"
}この関数を実行すると、「hoge」という文字列が結果として返されます。
通常、関数内で処理をした結果を返すために使います。
今回の関数では、「[ '出荷対象注文番号', 'SN0001', 'TS0001', 'NG1000' ]」が返ってきています。
function getTargetOrder() {
// 出荷対象_注文番号シートに入力されている情報を抜き出す
const dataTargetOrder = sheetOrderNumberInput.getDataRange().getValues()
// 二次元配列を扱いやすいように１次元配列に変換する
const targetOrderArray = dataTargetOrder.flat()
// console.log(hogehoge)はhogehogeに入れたものを出力して確認するのに使う
console.log(targetOrderArray)
// returnで書いたものが、この関数を呼び出した際に結果として返ってくる
return targetOrderArray
}
// output : [ '出荷対象注文番号', 'SN0001', 'TS0001', 'NG1000' ]さて、ここまでで初めての関数を定義することができました。試しに関数を実行してみましょう。GASではエディタから関数を実行することができます。
まずは、赤丸部分から実行する関数を選択します。今回は”getTargetOrder()”関数しかないので、getTargetOrderを選択します。
次に、実行ボタンをクリックします。
初めて関数を実行すると、以下のような確認が出るので、「権限を確認」を選択して先に進みます。
そして、関数を実行した結果、実行ログがでてこんな感じになれば成功です！もしエラーなどが出てしまった場合には、エラーログをみながら解決するか、もう一度このチュートリアルを見直してみましょう。
この中で、真ん中の白い行に「情報」として出てきている部分が “console.log(targetOrderArray)“で出力した値です。
7. 出荷履歴：受注製品の抽出 / filterOrderList()
前のパートでは、入力した出荷対象注文番号の配列を取得しました。今度は、取得した出荷対象注文番号と一致する受注製品だけを受注一覧から抽出していきます。
同じファイル内で、”getTargetOrder()”関数の下に新しい関数、”filterOrderList()”関数を作成していきます。関数の完成形は以下です。
新しい概念がいくつか出てきたので、それぞれ順番に説明していきます。
/**
* 受注一覧の中から、出荷対象注文番号の製品だけを抽出するための関数
* @return {2D Array} 受注一覧シートから、出荷対象対象注文番号と注文番号が一致する製品だけを抽出した二次元配列
* @param {Array} targetOrderArray 出荷対象注文番号の一次元配列
*/
function filterOrderList(targetOrderArray){
// 受注一覧シートのデータを二次元配列で取得
const dataOrderList = sheetOrderList.getDataRange().getValues()
// dataOrderListから、targetOrderArrayに含まれている注文番号の製品だけ抽出する
const dataFilteredOrder = dataOrderList.filter(function(row) {return targetOrderArray.includes(row[1])})
return dataFilteredOrder
}function filterOrderList(targetOrderArray){
...
...
}今回の関数、”filterOrderList()”関数は、今ままでと異なり、”()” の中に「targetOrderArray」がセットされています。これは、関数の引数というもので、関数を呼び出す際に「targetOrderArray」の部分に何かを入れた状態で呼び出すことができ、関数内でtargetOrderArrayを値として活用することができます。
順番が前後しましたが、コメントに ”@param” として記載されている部分です。
@param {Array} targetOrderArray 出荷対象注文番号の一次元配列前のパートでは、”@return”で返り値がどんな値かをコメントとして記載していました。
ここでは、”@param”で引数がどんな値かを記載しています。
// dataOrderListから、targetOrderArrayに含まれている注文番号の製品だけ抽出する
const filteredDataOrderList = dataOrderList.filter(function(row) {return targetOrderArray.includes(row[1])})これが”filterOrderList()”関数において肝になる部分＆1番ややこしい部分です。少し理解に時間がかかるかもしれませんが、焦らず１つづつ分解してみていきましょう。
大枠として、ここでは定数 “dataOrderList” に対して、filter()関数を呼び出しています。”dataOrderList”は、前のパートと同じく、受注一覧シート内の値が入った全てのセルを、二次元配列として取り出したものです。
今回、”filter()”関数、”includes()”関数の2つが新しい概念です。逆から説明した方がわかりやすいので、”includes()”関数から１つづつ説明していきます。
includes()関数：
“includes()”関数が直接関わっている部分だけ切り出しました。ここだけ見ると、今までと同じくシンプルになりましたね。
targetOrderArray.includes(row[1])“includes()”関数は、配列に対して呼び出すことができる関数で、ここでは
”row[1]”（row配列の2番目の要素の値）が”targetOrderArray”の中の要素に含まれているか？を判定しています。
具体例で見るとわかりやすいので、以下の例を見てみましょう。
const testArray = [1, 2, 3, "test"]
testArray.includes(1) // True
testArray.includes(5) // False
testArray.includes("test") // True
testArray.includes("result") // Falseこのように、”includes()”関数の引数に入れた値が、”testArray”の要素として含まれているかを判定して、True / Falseを返します。True / Falseは今まで扱ってきた文字列や数字と同じ値の一種で、Booleanオブジェクトです。
filter()関数：
“filter()”関数は、配列に対して呼び出すことができる関数です。
引数として渡した関数において、Trueとなった全ての配列からなる新しい配列を生成します。何を言っているのかわかりませんね？僕もはじめはよくわかりませんでした。
今回も、シンプルな具体例を用いて説明していきます。
const filteredArray1 = [1,2,3].filter(function(t){return t>1})
console.log(filteredArray1)
// [2,3]
const filteredArray2 = [1,2,3,"name"].filter(function(hoge){return hoge==="name"})
console.log(filteredArray2)
// ["name"]“filter()”関数の引数には関数を入れます。この関数は「無名関数」と呼ばれるもので、今までの関数のように関数名がありません。他の箇所から呼び出すわけではないので、関数名が必要ないのですね。
そして、今回 ”t” と記載した部分は、適当な文字列でOKです。この “t“ は、配列内の各要素を順番に取り出してそれぞれに対して続く関数を実行していきます。
”return t>1”では、tが1よりも大きい場合にTrueを返します。例えば、”return t === 1”と記載したらtが1の場合にのみTrueを返します。この条件式は、True / Falseを返すものであればなんでもOKです。
そして、この関数においてTrueを返したものだけをまとめた配列を返り値として返しています。もしTrueを返す要素がなかった場合には、空の配列”[]”を返します。少し簡略化して説明しましたが、”filter()”関数について雰囲気は掴むことができましたでしょうか？一発で完全理解は難しいかもしれませんが、雰囲気を掴むことができたら次にいきましょう。使っているうちになれるはずです。
ちなみに、今回のようにシンプルなコードを試す際には、エディタ上で適当な関数を作って実行すると試すことができるので、ぜひ色々と弄りながら理解を深めて頂ければと思います。
“filter()”関数と”includes()”関数について理解できた（はずの）ところで、元のコードに戻ります。
// dataOrderListから、targetOrderArrayに含まれている注文番号の製品だけ抽出する
const filteredDataOrderList = dataOrderList.filter(function(row) {return targetOrderArray.includes(row[1])})“dataOrderList”２次元配列の構造をエクセルで表すとこんな感じです。
(セル結合していることが気になってしまったそこのあなた、どうかこのnoteを閉じないで最後までお読みください。。。)
これに対して、”filter()”関数の引数部分では、x=0 ~ x=14までを順番に比較していきます。つまり、”includes()”関数の引数である”row[1]”は
dataOrderList[0][1] ~ dataOrderList[14][1]まで順番に、その値がtargetOrderArray配列の要素として含まれているかどうかを順番にチェックしているのです。含まれている場合にはTrueを返します。
念の為補足しておくと、dataOrderList[0][1] ~ dataOrderList[14][1]では注文番号列の値を順番に取っています。
function(row) {return targetOrderArray.includes(row[1])}つまり以下のコードでは、注文番号列の値を順番に比較して、その値が”targetOrderArray”配列の要素として含まれているものだけを抽出して、”filteredDataOrderList”に格納しているのです。
// dataOrderListから、targetOrderArrayに含まれている注文番号の製品だけ抽出する
const filteredDataOrderList = dataOrderList.filter(function(row) {return targetOrderArray.includes(row[1])})確認のため、”test()”関数に”getTargetOrder()”関数と”filterOrderList()”関数を入れて実行してみましょう。”test()”関数を作った理由は、”filterOrderList()”関数を呼び出すためには、引数を渡す必要があるので”test()”関数内で”getTargetOrder()”関数を実行して引数となる”targetOrderArray”定数を作成するためです。
この際に、”getTargetOrder()”関数内にある、”console.log(targetOrderArray)”の部分は邪魔なので削除しておきます。
ここまでのコードと実行結果です。
ここまでのコード
/**
* 出荷対象_注文番号シートに入力した値を取得するための関数
* @return {Array} targetOrderArray 出荷対象である注文番号をスプシから取得して返す
*/
function getTargetOrder() {
// 出荷対象_注文番号シートに入力されている情報を抜き出す
const dataTargetOrder = sheetOrderNumberInput.getDataRange().getValues()
// 二次元配列を扱いやすいように１次元配列に変換する
const targetOrderArray = dataTargetOrder.flat()
// returnで書いたものが、この関数を呼び出した際に結果として返ってくる
return targetOrderArray
}
/**
* 受注一覧の中から、出荷対象注文番号の製品だけを抽出するための関数
* @return {2D Array} 受注一覧シートから、出荷対象対象注文番号と注文番号が一致する製品だけを抽出した二次元配列
* @param {Array} targetOrderArray 出荷対象注文番号の一次元配列
*/
function filterOrderList(targetOrderArray){
// 受注一覧シートのデータを二次元配列で取得
const dataOrderList = sheetOrderList.getDataRange().getValues()
// dataOrderListから、targetOrderArrayに含まれている注文番号の製品だけ抽出する
const dataFilteredOrder = dataOrderList.filter(function(row) {return targetOrderArray.includes(row[1])})
return dataFilteredOrder
}
function test(){
const targetOrderArray = getTargetOrder()
const dataFilteredOrder = filterOrderList(targetOrderArray)
console.log(dataFilteredOrder)
}実行結果：test()関数を実行してこうなったら成功
[ [ Tue Nov 02 2021 15:12:30 GMT+0900 (Japan Standard Time),
'SN0001',
'BE1000',
'ベアリング',
10,
'' ],
[ Tue Nov 02 2021 15:12:30 GMT+0900 (Japan Standard Time),
'SN0001',
'NS1000',
'六角ナット_ステンレス',
5,
'' ],
[ Mon Nov 08 2021 17:30:51 GMT+0900 (Japan Standard Time),
'TS0001',
'LS1000',
'リニアシャフト',
3,
'' ],
[ Sat Nov 13 2021 09:09:59 GMT+0900 (Japan Standard Time),
'TS0001',
'SH1000',
'シャフト',
8,
'' ] ]8. 出荷履歴：在庫数量チェック / calcStock()
/**
* 入荷一覧シートと出荷対象製品を比較して、在庫数量が十分か、不十分か判定するための関数
* @param {2D Array} dataFilteredOrder 出荷対象製品の二次元配列
* @return {String | 2D Array} "cancel" | dataStockList
* キャンセルボタンが押された場合にはcancelの文字列、
* アラートが出なかった場合には費消状況が更新された状態の入荷一覧の二次元配列
*/
function calcStock(dataFilteredOrder) {
// 配列の中身を操作するので、コピーを作成しておく
const dataFilteredOrderCopy = JSON.parse(JSON.stringify(dataFilteredOrder))
// 入荷一覧の情報を取得
const dataStockList = sheetStockList.getDataRange().getValues()
// ２つの二次元配列に対してループを回し、出荷対象製品と在庫の数量をチェックしていく
for (let i = 0; i < dataFilteredOrderCopy.length; i++){
// 受注情報の製品コードを定数に持たせる
const productIdOrder = dataFilteredOrderCopy[i][2]
for (let k = 0; k < dataStockList.length; k++){
// 入荷一覧の製品コードを定数に持たせる
const productIdStock = dataStockList[k][1]
// 製品コードが一致しているかどうかをチェックしつつ数量調整していく
// 製品コードが一致しているときかつ入荷一覧の数量が０ではないとき
// *入荷一覧の数量は入荷数量-費消数量で考える
if (productIdOrder===productIdStock && dataStockList[k][3]-dataStockList[k][4] !== 0){
// 受注情報の数量 < 入荷一覧の数量の時
if (dataFilteredOrderCopy[i][4] < dataStockList[k][3]-dataStockList[k][4]){
dataStockList[k][4] += dataFilteredOrderCopy[i][4]
dataFilteredOrderCopy[i][4] = 0
// これ以上ループが回っても、対象製品の数量は０から変わらずなのでループ終了
break
// 受注情報のロット >= 入荷一覧のロットの時
} else {
dataFilteredOrderCopy[i][4] -= dataStockList[k][3]-dataStockList[k][4]
dataStockList[k][4] = dataStockList[k][3]
}
}
}
// 内側のループが終わっても、受注情報の製品コードが0になっていない場合
// 出荷に対して在庫数量が足りないことを意味するので、アラートを出す。
if (dataFilteredOrderCopy[i][4] > 0){
// OKボタンが押されると"ok"を、キャンセルボタンが押されると"cancel"が返される
const ok_cancel = Browser.msgBox(
`${productIdOrder}の在庫数量が${dataFilteredOrderCopy[i][4]}個足りません。
\nこのまま処理を続けますか？`, Browser.Buttons.OK_CANCEL
)
if (ok_cancel === "cancel"){
// もし処理を続けない場合、シートへの書き戻しはしないで処理を終了させる。
return "cancel"
}
}
}
// キャンセルボタンが押されていない場合、費消数量を調整したdataStockListを返す
return dataStockList
}ちょっといきなりボリューミーなコードが来てしまいましたが、一つずつ機能を分解してみていきましょう。
// 配列の中身を操作するので、コピーを作成しておく
const dataFilteredOrderCopy = JSON.parse(JSON.stringify(dataFilteredOrder))右辺では、配列”dataFilteredOrder”の深いコピー(deep copy)を作成しています。
深入りすると難しくなるので、ここではあまり立ち入りませんが、コピーには浅いコピー(shallow copy)と深いコピー(deep copy)が存在しており、配列＆オブジェクト（これはまだ登場していない概念＆今回明示的には登場しないので気にしないください）では、深いコピーをしないと完全に元のデータと同じものを生成することができず、中身を意図せず変更してしまう可能性が生じてしまいます。
ここで深いコピーを作成しているのは、この関数が実行された後に”dataFilteredOrder”を別の関数で使い回すので、その際に意図しない変更が発生しないように作成しています。
今回は、配列のコピーを作成して、元のデータが変わらないようにしたい場合には深いコピーを使う。とだけ覚えておけばOKですが、念の為具体例と共に説明しておきます。
まずは、浅いコピーから。
新しい変数に割り当てる形でコピーを作成した場合、数字や文字列などの場合にはコピー先の変数を操作しても元の定数に問題はありませんが、配列の場合、コピー先の変数を操作したつもりでも、コピー元の変数まで要素が変更されてしまっています
こういった意図しない変更によってバグを埋め込みやすくなってしまうため、配列をコピーして操作する際には注意が必要です。
こういった事象を避けるためには、深いコピーを使う必要があります。
const sampleArray = [1,2,3]
const sampleNumber = 4
const sampleArrayCopy = sampleArray
let sampleNumberCopy = sampleNumber
sampleArrayCopy[0] = 10 //constで宣言しても配列の中身は書き換え可能
sampleNumberCopy = 100
//元の配列が変わってしまっている！
console.log(sampleArray) // [ 10, 2, 3 ]
//元の数字は変わらない
console.log(sampleNumber) // 4
//変更後の配列
console.log(sampleArrayCopy) // [ 10, 2, 3 ]
//変更後の数字
console.log(sampleNumberCopy) // 100 以下のように、”JSON.parse(JSON.stringify(配列))”を使って深いコピーを作成することができ、こうすることでコピー先の配列の要素を操作してもコピー元には影響しなくなります。
仕組みとしては、”JSON.stringify(配列)”でまずは配列を文字列に変換します。先ほど見たように、文字列からコピーを作成しても、元の文字列には影響がありませんでしたね？
そして、その文字列を”JSON.parse(文字列)”で再度配列に戻しています。
このように、一度配列を文字列に変換してから配列を作成することで深いコピーを実現しているのですね。
const sampleArray2 = [5,6,7]
const sampleArray2Copy = JSON.parse(JSON.stringify(sampleArray2))
sampleArray2Copy[0] = 100
// 深いコピーを作成すると元の配列は変わらない
console.log(sampleArray2) // [ 5, 6, 7 ]
// コピー先の配列は変更できている。
console.log(sampleArray2Copy) // [ 100, 6, 7 ]次は、
for (~~){}今回のメインはforから始まるループ処理です。
ループ処理は自動化において肝になる処理なので、ここでしっかりと理解しましょう。
まず、ループ処理とは？ですが、その名の通りある条件下において、繰り返し処理を実行することを指しています。
コードの書き方はいくつか方法があるのですが、今回は “for(){}” と書く方法を見ていきましょう。
まずは、1番外側のループ処理で説明していきます。
for (let i = 0; i < dataFilteredOrderCopy.length; i++){
...
...
...
}
// for (変数宣言; ループを続ける条件; ループの最後に変数を変更) {
//...
//...
//...
//}コメントに記載しましたが、for の後の ()の中には、「変数宣言」「ループを続ける条件」「ループの最後に変数を変更」という３つの役割を記載していきます。
まずは、「変数宣言」です。ここには、ループの条件で活用する変数を宣言していきます。
今までは”const”を使って「定数」を宣言してきたと思います。ここでは”
let”を用いて「変数」を宣言しています。定数と変数の違いですが、
「定数は再代入不可」「変数は再代入可能」という違いがあります。
const fruit = "apple"
fruit = "banana" // エラーになる
let sport = "soccer"
sport = "baseball" // 問題なし！
console.log(sport)
// "baseball"“calcStock()”関数内では”let i = 0”で０という数字を i という変数に対して割り当てています。
次に、「ループを続ける条件」です。ループが回る前にこの条件式が実行され、結果がTrueであれば内部の処理を実行、Falseであれば内部の処理は実行されず、ループが終了します。
今回の条件では”i< dataFilteredOrderCopy.length”と記載しているので、
「変数iがdataFilteredOrderCopy.lengthよりも小さい場合」内部の処理が実行され、逆に「変数iがdataFilteredOrderCopy.length以上の場合」内部の処理は実行されず、ループが終了します。
ここで、”dataFilteredOrderCopy.length”が新しく登場しました。”length”は配列に対して呼び出されるもので、配列の長さを取得することができます。実は、”length”は今まで登場したような関数とは異なる概念なのですが、ややこしくなるので今回は説明を割愛します。以下例で見ていきましょう。
const myArray = [1,2,3,4]
console.log(myArray.length)
// 4
const nextArray = ["my", "name", "is", "shogo", "nakano", "!"]
console.log(nextArray.length)
// 6
const twoDArray = [["a", "b", "c"], ["d", "e", "f"]]
console.log(twoDArray.length)
// 2
console.log(twoDArray[0].length)
// 3このように、配列に対して”length”を使うと、配列の要素数を取得することができます。
つまり、今回のループ内では”dataFilteredOrderCopy.length”では、”dataFilteredOrderCopy”の長さを取得しており、ループが続く条件としては、
「変数iがdataFilteredOrderCopyの長さよりも短い場合」内部の処理が実行され、「変数iがdataFilteredOrderCopyの長さ以上の場合」内部の処理は実行されず、ループが終了します。今回は「<（より小さい）」を条件として使いましたが、「<=（以下）」「>=（以上）」なども条件として活用することができます。
さあ次は、最後の役割「ループの最後に変数を変更」についてです。
ここに記載した処理は、ループの1番最後に実行され、その後に次の「ループを続ける条件」が判定されるようになります。
”calcStock()”関数内では”i++”とありますが、これは「変数iに対して１追加する」という意味です。慣例的に”i++”のような書き方をすることが多いですが、”i = i + 1”と書いても意味は同じで問題なく実行することができます。
ここまでの処理をまとめます。
for (let i = 0; i < dataFilteredOrderCopy.length; i++)このコードを文章に落とすと、
「i = 0から始まり、iがdataFilteredOrderCopy.lengthより小さい限りループが実行される。ループの最後には i に１を追加して、条件を満たす限り i は0,1,2,3,…と増えていく」となります。
今回、for文の中でもう一つのfor文を記載していますが、ループの原理は同じです。念の為、簡単なサンプルを記載しておきます。
// sampleArray.length = 3
const sampleArray = ["apple", "banana", "mango"]
// twoDArray.length = 3
const twoDArray = [["pineapple", 100, ""], ["melon", 500, ""], ["apple", 300, ""]]
for (let i = 0; i < sampleArray.length; i++) {
const sampleFruitName = sampleArray[i]
// i = 0の間に、k = 0~2までの3回実行される
// k = 3になったとき、3 < 3 が成り立たなくなるので内側のループ終了
// 外側のループも処理が終わるので、i++で i = 1になり、再度内側のループがk = 0からスタート
for (let k = 0; k < twoDArray.length; k++) {
const twoDFruitName = twoDArray[k][0]
if (sampleFruitName === twoDFruitName) {
twoDArray[k][2] = "done"
}
}
}
console.log(twoDArray)
/**
* [ [ 'pineapple', 100, '' ],
[ 'melon', 500, '' ],
[ 'apple', 300, 'done' ] ]
*/サンプルでもしれっと出てきてしまいましたが、プログラムを作成するにあたってのもう一つの肝、「条件分岐」も登場しました。
if (条件) {条件がTrueだった時になされる処理}条件分岐にも書き方はいくつかあるのですが、今回は1番ベーシック且つ馴染みやすそうなif文を活用していきましょう。
if文では、実行するための条件を記載して、条件が正だった時に実行する処理を書いていきます。
複数条件を書きたい場合には、”else if (条件){処理}”と続けていくことが可能で、条件は上から順番に評価されていきます。
if (条件1) {
条件1がTrueだった時に実行する処理
} else if (条件２) {
条件2がTrueだった時に実行する処理
} else if (条件3) {
条件3がTrueだった時に実行する処理
} else {
全ての条件がFalseだった時に実行する処理
}そのため、条件１がTrueとなった場合には、条件２以下は実行されず、次の処理に移っていきます。
ここでも具体例を用いて確認していきましょう。
function sampleIf(a, b) {
if (a > b) {
console.log("aはbより大きい")
} else if (a === b) {
console.log("aとbは等しい")
} else if (a < b) {
console.log("aはbより小さい")
} else {
console.log("不正な値が入力されました")
}
}
function sampleCall(){
sampleIf(1,1) //aとbは等しい
sampleIf(5,3) //aはbより大きい
sampleIf(1,10) //aはbより小さい
sampleIf(1, "string") //不正な値が入力されました
sampleIf("string", "numbear") //aはbより大きい
}このような関数を作成して、”sampleCall()”関数を実行するとコメントの出力結果が得られるはずです。
この時、文字列同士でも比較ができてしまうことに注意しましょう。もし数字以外が入力された場合にエラーになるようにしたい場合には、
function sampleIf(a, b) {
if (typeof a !== "number" || typeof b !== "number"){
console.log("数字を入力してください")
return
}
if (a > b) {
console.log("aはbより大きい")
} else if (a === b) {
console.log("aとbは等しい")
} else if (a < b) {
console.log("aはbより小さい")
} else {
console.log("不正な値が入力されました")
}
}こんな感じで、aとbのデータ型をチェックして、データ型が数字でない場合には”return”で以下の処理を終了させるような処理を書く必要があります。
”typeof”は値の型（string / number / booleanなど）を取得する際に使いますが、今回の本流とは逸れるので説明は割愛します。興味がある方は調べてみて頂けると、本チュートリアル完了後、入力制限やエラー処理を内包したより安全なツールを作成する際に役に立つかと思います。（とはいえ、スプシだけだと限界もあるのですが。。。）
ここまで理解できれば、一見複雑そうに見えた”calcStock()”関数のコードはほぼ理解できるかと思います。
最後に一つだけ、”Browser.msgBox()”関数と”`${変数}任意の固定文字列`”(バッククオート`を使っているのがポイントです)についてだけ説明していきます。
const ok_cancel = Browser.msgBox(
`${productIdOrder}の在庫数量が${dataFilteredOrderCopy[i][4]}個足りません。
\nこのまま処理を続けますか？`, Browser.Buttons.OK_CANCEL
)Browser.msgBox()
画面に出てくるダイアログを簡単に出すことができます。これです。
function msgBox(youname){
const OK_CANCEL = Browser.msgBox(`${youname}さん\nこのまま処理を続けますか？`, Browser.Buttons.OK_CANCEL)
console.log(OK_CANCEL)
// OKボタンを押した時：ok
// キャンセルボタンを押した時：cancel
}
function callMsgBox(){
msgBox("shogo")
}このようなサンプルコードを書いて、”callMsgBox()”関数を実行すると、スクショのようなダイアログを出すことができます。
”Browser.msgBox()”関数は、引数を”Browser.msgBox(任意のメッセージ, ボタンの種類)”と指定して呼び出すことが可能です。ボタンの種類に関して興味がある方は調べてみてください。
今回は、”Browser.Buttons.OK_CANCEL”を指定しましたが、この場合「OK」「キャンセル」の２種類のボタンを出すことができて、「OK」ボタンが押された場合には”ok”という文字列が、「キャンセル」ボタンが押された場合には”cancel”という文字列が返されます。
この文字列とif文を組み合わせて使うことで、今回”calcStock()”関数の中で実装したようなユーザーと対話形式で実行するコードを書くことができます。
`${変数}任意の固定文字列`
これは、文字列内に変数を埋め込む方法で、今回のようなエラーメッセージや”console.log()”で値を確認する際などによく利用します。
今回も具体例を置いておきます。
function variable(){
const name = "shogo"
const age = 26
console.log(`${name}は${age}歳です`) //shogoは26歳です
console.log("${name}は${age}歳です") //${name}は${age}歳です
}このように、``(バッククオート)で囲った場合、${変数}で変数を文字列の中に記載することができますが、””(ダブルクオート)や’’(シングルクオート)で囲った場合、変数としては扱われず通常の文字列になってしまうので注意してください。
さあ、長かったですが、これで8. 在庫数量チェック / calcStock()冒頭に記載がるコード全体を読み解くことができるはずです。
慣れるまでは、コードを読み解くのには時間がかかるかと思いますが、焦らず、手を動かしながら慣れていって頂ければと思います。
9. 出荷履歴：出荷済みフラグ付与 / addShippedFlag()
今度は、先ほど出荷対象製品を抽出したのに合わせて、出荷対象製品に対して出荷済みフラグを追加する処理を記載していきます。
同一ファイル内に “addShippedFlag()”関数を実装していきましょう。関数の完成形は以下です。
/**
* 出荷対象製品に出荷済みフラグを立ててシートにデータを書き戻す関数
* @param {2D array} 出荷済フラグがついていない状態の受注一覧の二次元配列
* @return {void} シートに書き戻すための関数なので、値は何も返さない
*/
function addShippedFlag(targetOrderArray) {
const dataOrderList = sheetOrderList.getDataRange().getValues()
for (let i = 0; i< targetOrderArray.length; i++ ){
const orderNumber = targetOrderArray[i]
for (let k = 0; k<dataOrderList.length; k++){
const listOrderNumber = dataOrderList[k][1]
if (orderNumber === listOrderNumber){
dataOrderList[k][5] = "done"
}
}
}
// 出荷した製品にdoneのフラグをつけたら、シートに書き戻す。
sheetOrderList.getDataRange().setValues(dataOrderList)
}今回の関数内に登場するのは、for文を活用したループ処理がメインです。
１点だけまだ解説できていない、関数最後にある以下のコードを見ていきましょう。
// 出荷した製品にdoneのフラグをつけたら、シートに書き戻す。
sheetOrderList.getDataRange().setValues(dataOrderList)“sheetOrderList.getDataRange()”までは今までと同じです。最後の”setValues(dataOrderList)”だけ説明します。
これは、Rangeオブジェクトに対して呼び出すことができる関数で、２次元配列を引数に取ることで選択した範囲に対して２次元配列のデータをペーストすることができます。注意点としては、”setValues()”関数でペーストするデータの大きさと、Rangeオブジェクトの範囲が一致している必要があることです。ここがずれているとエラーが発生してしまいます。
10.1 出荷履歴：出荷履歴出力 / updateShipmentHistory()
“updateShipmentHistory()”関数と、この関数内で使うもう一つの関数である”getColumns()”関数を作成したら、出荷履歴の出力まで完成です。そこまで行けば、ツール全体の2/3程度は完了でしょうか。
/**
* 出荷対象製品を出荷履歴シートの1番後ろに追加して更新するための関数
* @param {2D Array} 出荷対象製品の二次元配列
* @return {void} シートを更新するための関数なので値は何も返さない
*/
function updateShipmentHistory(dataFilteredOrder){
// 実行タイミングのタイムスタンプを作成
const timestamp = new Date()
// この関数は後ほど作成する。二次元配列から任意の列だけ抜き出す関数
const dataExtracted = getColumns(dataFilteredOrder, [2,3,4])
// 最初の列にタイムスタンプを追加する
dataExtracted.forEach(row => row.unshift(timestamp))
// 出荷履歴シートの最終行を取得する
const lastRow = sheetShipmentHistory.getLastRow()
// 出荷履歴シートにデータを戻す
sheetShipmentHistory.
getRange(lastRow+1, 1, dataExtracted.length, dataExtracted[0].length).
setValues(dataExtracted)
}// 実行タイミングのタイムスタンプを作成
const timestamp = new Date()
console.log(timestamp)
// Tue Dec 07 2021 18:15:23 GMT+0900 (Japan Standard Time)new Date()
“new Date()”が新しく登場しました。これは、現在時刻のタイムスタンプを作成するための機能です。
今回のように、何かツールを実行したタイミングなどでタイムスタンプを生成して、後ほど遡ったり、日付順で並び替えたりしたくなった時に使用します。
コメントにも記載がある通り、後ほど作成する”getColumns()”関数はいったん飛ばして後から戻ってきましょう。
// 最初の列にタイムスタンプを追加する
dataExtracted.forEach(row => row.unshift(timestamp))ここでは２つの新しい関数が登場しています。”forEach()”関数と”unshift()”関数です。それぞれ順番に見ていきましょう。
forEach()関数：
これは少し理解が難しい関数かもしれません。
forEach()関数は、配列に対して呼び出すことができる関数で、引数として与えられた関数を配列内のそれぞれの要素に対して１度ずつ実行する関数です？？？おそらくよくわからないと思うので、こういう時には具体例を見ていきましょう。
const array1 = [1,2,3]
array1.forEach(element => element-1) // elementは文字列ならなんでもOK。iとか。
console.log(array1)
// [0, 1, 2]
const array2 = [[1,2,3], [4,5,6], [7,8,9]]
array2.forEach(row => row[0]="hoge") // rowも文字列ならなんでもOK。
console.log(array2)
// [ [ 'hoge', 2, 3 ], [ 'hoge', 5, 6 ], [ 'hoge', 8, 9 ] ]だいぶ親しみやすくなったのではないでしょうか？
”forEach()”関数を理解したところで、”forEach()”関数の内部で使われている”unshift()”関数を見ていきます。
unshift()関数：
この関数は、配列の先頭に任意の要素を追加することができる関数です。ここでは、出荷履歴シートに対して挿入するデータを作っているのですが、出荷日時にあたる日付情報は今までどこにも出てきませんでした。そのため、”updateShipmentHistory()”関数の最初で、”timestamp”定数を宣言して、出荷日時としてデータに挿入するために用意していました。
“updateShipmentHistory()”関数の引数に入れていた、”dataFilteredOrder”二次元配列は、この後説明する”getColumns()”関数によって整形され、最終的に以下のようなデータになって49.50行目に渡ってきます。
[ [ 'BE1000', 'ベアリング', 10 ],
[ 'NS1000', '六角ナット_ステンレス', 5 ],
[ 'LS1000', 'リニアシャフト', 3 ],
[ 'SH1000', 'シャフト', 8 ] ]この状態のままでは、出荷日時の情報を持っていないので、先頭に”timestamp”を入れ込みたいわけです。そこで”unshift()”関数の登場です。
これも具体例を用いて確認していきましょう。
const array1 = [1,2,3]
array1.unshift(0)
console.log(array1)
//[ 0, 1, 2, 3 ]
const array2 = ["apple", "banana", "pineapple"]
array2.unshift("football")
console.log(array2)
//[ 'football', 'apple', 'banana', 'pineapple' ]あとは”forEach()”関数と、”unshift()”関数を組み合わせて考えるだけです。
ここでも、組み合わせた状態の具体例を見ていきましょう。
const array1 = [[1,2,3], [4,5,6], [7,8,9]]
array1.forEach(r => r.unshift(0))
console.log(array1)
//[ [ 0, 1, 2, 3 ], [ 0, 4, 5, 6 ], [ 0, 7, 8, 9 ] ]
const array2 = [["apple", "banana", "pineapple"], ["football", "soccer", "baseball"]]
array2.forEach(row => row.unshift(100))
console.log(array2)
// [ [ 100, 'apple', 'banana', 'pineapple' ],
// [ 100, 'football', 'soccer', 'baseball' ] ]イメージできましたでしょうか？もしいまいちでしたら、もう一度”forEach()”関数、”unshift()”関数それぞれについて見直してみることをお勧めします。
最後に、シートにデータを書き戻す処理です。前のパートでシートへのデータの書き戻し方は一度見ていますが、Rangeオブジェクトを取得する部分で初見の処理があるのでみてみましょう。
// 出荷履歴シートの最終行を取得する
const lastRow = sheetShipmentHistory.getLastRow()
// 出荷履歴シートにデータを戻す
sheetShipmentHistory.
getRange(lastRow+1, 1, dataExtracted.length, dataExtracted[0].length).
setValues(dataExtracted)
まず、”getLastRow()”関数です。この関数は、Sheetオブジェクト内の値が入力されている最終行を取得します。注意点として、「スプレッドシート式」が入力されているセルは、式が何らかの値を返していればそのセルも含めて最終行がどこかを返してくれますが、式が何も値を返しておらず、セルが空欄に見えている状態だとそのセルは最終行の判定には含まれません。
例えば、以下のようにE列にD列の同じ行を参照する式を入れているとしましょう。この場合、E12セルまで式は入力されていますが、
”sheetShipmentHistory.getLastRow()”で返ってくる値は9となります。
もしE12セルにD9セルを参照する式が入っているとすると、E12セルは８という値を持つことになります。この場合には、
”sheetShipmentHistory.getLastRow()”で返ってくる値は12になります。
次に、出荷履歴シートにデータを戻す処理です。一見出荷済みフラグを付与する箇所で行った処理と同じに見えますが、Rangeオブジェクトを取得するために使われているのが “getDataRange()”関数から”getRange()”関数に変わっています。ということで、”getRange()”関数についてみていきましょう。
“getRange()”関数は、”getDataRange()”関数同様、Sheetオブジェクトに対して呼び出すことができる関数で、引数に指定した範囲のRangeオブジェクトを返します。”getRange()”関数の引数はいくつか書き方があるので、具体例で紹介していきます。
/**
* getRange("スプシ関数で指定するようなセル範囲")
* 例1：getRange("A1:F5")
* 例1：getRange("A:A")
*
* getRange(始まり行, 始まり列, 始まり行含め何行選択するか, 始まり列含め何列選択するか)
* 例1：getRange(1,1,3,4)
* = getRange("A1:D3")
* 例2：getRange(4,2,2,5)
* = getRange("B4:F5")
*/
sheetSample.getRange("A1:D4")
// sample1
sheetSample.getRange(4,2,2,3)
// sample2
今回は、出荷履歴シートの最後の行に追加する形でデータを入れていきたかったので、始まり行を「最終行＋１」、A列からデータは入っているから始まり列は「1」、行数は貼り付ける二次元配列”dataExtracted”の長さ、列数は貼り付ける二次元配列の１つ目の要素”dataExtracted[0]”の長さを指定しています。
”getRange()”関数がわかれば、あとは”setValues()”関数で値を入れているだけなので、出荷済みフラグを付与した時の処理と同じですね！
では、”updateShipmentHistory()”の内部で使われている”getColumns()”関数についてみていきましょう。
10.2 出荷履歴：列の抽出 / getColumns()
関数自体は非常に短いです。
ただ、中身が少しとっつきにくい感じがします。
/**
* 二次元配列をスプシのように捉えたときに、列単位で行を抽出するための関数
* @param {2D Array} arr 列を抽出したい二次元配列。
* @param {Array} indices 何行目と何行目を抽出したいのかを配列で指定する。
* 例：1,4,5列目を抽出したい場合、[0,3,4]
* @return {2D Array} arr indicesで選択した列だけが抽出された状態の二次元配列
*/
function getColumns(arr, indices){
return arr.map(row => indices.map(i => row[i]))
}しかし、よくみると今まで説明してきた要素の組み合わせ+αぐらいなので、落ち着いてみていきましょう。
まずは初見の関数から。”map()”関数が今回初めて登場しました。
”map()”関数は配列に対して呼び出すことができる関数で、配列の各要素に対して引数に渡した関数を順番に呼び出し、結果としてできた新しい配列を返り値として返します。
なんとなく思った方もいらっしゃるかと思いますが、”map()”関数はすでに学習済みの”forEach()”関数に近いです。違いは、”map()”関数は新しい配列を作成して返り値を返す、”forEach()”関数は各要素に対して引数内の処理を実行するだけで、返り値は返さない（undefined）という点です。
使い分けとしては、返り値を使いたい場合には”map()”関数、処理を実行したいだけで返り値は特に使わない場合は”forEach()”関数というような分類でOKかと思います。明確な決まりはないので、”map()”関数と使って返り値は何も使わないこともできますが、混乱しやすくなるのでおすすめはしません。
少し説明が長くなりましたが、具体例で見ていきましょう。
const array1 = [1,2,3]
const newArray1 = array1.map(e => e*2)
console.log(newArray1)
//[ 2, 4, 6 ]
console.log(array1)
//[ 1, 2, 3 ] 元の配列は変わらない
const array2 = [[1,2,3], [4,5,6], [7,8,9]]
const newArray2 = array2.map(r => r[0])
console.log(newArray2)
// [ 1, 4, 7 ]この例を見てわかるように、一次元配列であれば比較的シンプルに”map()”関数を使って操作できるのですが、二次元配列を扱おうとした際に、”map()”関数をそのまま扱うのでは二次元配列の構造を保持したまま操作することができません。
そこで、今回は”map()”関数の中で”map()”関数を用いて二次元配列の操作を行うために、”getColumns()”関数を作成しています。これもコードを動かしながら見ていきましょう。
const array = [[1,2,3], [4,5,6],[7,8,9]]
const indices = [0,2]
// array.map(r => indices.map(i => r[i]))の処理を全て順番に見ていく
/**
* 1.a：(r=[1,2,3]) => ((i=0) => (r[i]=[1,2,3][0]=1))
* 1.b：(r=[1,2,3]) => ((i=2) => (r[i]=[1,2,3][2]=3))
* 1.まとめ：(r=[1,2,3]) => [1,3]
* 2.a：(r=[4,5,6]) => ((i=0) => (r[i]=[4,5,6][0]=4))
* 2.b：(r=[4,5,6]) => ((i=2) => (r[i]=[4,5,6][2]=6))
* 2.まとめ：(r=[4,5,6]) => [4,6]
* 3.a：(r=[7,8,9]) => ((i=0) => (r[i]=[7,8,9][0]=7))
* 3.b：(r=[7,8,9]) => ((i=2) => (r[i]=[7,8,9][2]=9))
* 3.まとめ：(r=[7,8,9]) => [7,9]
* -------------------------------------------------------------
* 全体まとめ：[ [ 1, 3 ], [ 4, 6 ], [ 7, 9 ] ]
*/
const extractedColumns = array.map(r => indices.map(i => r[i]))
console.log(extractedColumns)
// [ [ 1, 3 ], [ 4, 6 ], [ 7, 9 ] ]今回の処理は少しイメージが湧きづらいと思うので、全ての処理手順を書き下してみました。
自分で書いていて、配列の操作に慣れないと理解するのが難しいなと感じたので、もし今時点で理解しきれなくても、この箇所は「そういうやり方もあるのか〜」ぐらいで飛ばして頂いても結構です。もしGASやその他のプログラミング言語にハマって、より深く学びたい！となった際に再度学習していただければと思いますし、そうはならなくてもここは一旦理解を諦めてコピペで進むのもアリかと思います。
これでやっと、”updateShipmentHistory()”関数を動かすことができるようになりました！
ここまでに、”calcStock()”関数、”addShippedFlag()”関数、”updateShipmentHistory()”関数と３つの関数を通して計算及び結果に応じたシートに書き戻すデータの生成を行ってきました。
次のポイントは、これらのデータを書き戻すタイミングです。
コードを見ていきましょう。
11. 出荷履歴：シートへの書き戻し / writeBackToSheets()
/**
* 最後まで問題なく処理が進んだ場合だけ、複数シート全体更新する関数。
* 在庫数量不足で処理が失敗した場合には、シートは一切更新せず処理を終了する。
* @param {String | 2D Array} OK_CANCEL キャンセルボタンが押されていたらcancelの文字列
* 処理が問題なく進んでいるorOKボタンが押されていたら費消数量更新済み入荷状況の二次元配列
* @param {Array} targetOrderArray 出荷対象_注文番号シートに入力されたインプットの配列
* @param {2D Array} dataFilteredOrder 出荷対象受注製品の二次元配列
* @return {void} シートを更新するための関数なので返り値なし
*/
// 出荷情報と入荷状況の数量をチェックして、問題ないときにデータをシートに書き戻すための関数
function writeBackDataToSheets(OK_CANCEL, targetOrderArray, dataFilteredOrder) {
// 処理を途中でcancelしていた場合
if (OK_CANCEL==="cancel"){
// 何も処理をせずに終了
console.log("在庫数量が足りないため、処理を終了しました")
return
}
// 入荷一覧に費消数量をアップデートしたデータを書き戻す
sheetStockList.getDataRange().setValues(OK_CANCEL)
// 受注情報シートにも出荷済みフラグを付与した上でデータを書き戻す
addShippedFlag(targetOrderArray)
// 出荷履歴シートも更新する
updateShipmentHistory(dataFilteredOrder)
}“writeBackDataTosheets()”関数では、”OK_CANCEL”に引数で渡す値が”cancel”ではない場合だけ残りの処理を実行していく関数です。
ここで、”OK_CANCEL”には”calcStock()”で計算した値を渡していきます。思い出しましたか？”calcStock()”では、数量にエラーがあった場合にはダイアログボックスを出して、ユーザーと対話できるようにしてありました。
ダイアログボックス上でユーザーがキャンセルボタンを押すと、”calcStock()”関数は”cancel”という文字列を、OKボタンを押すか、そもそもダイアログボックスが出ない場合には費消状況を付与した入荷一覧データが返ってくる仕様になっていたので、それを活用してシートの更新を行っていきます。
例えば、ユーザーがキャンセルボタンを押しているにも関わらず、途中まで実行された処理によってシートが書き変わってしまってはデータの不整合が生じてしまいますね？そのため、ここでわざわざ判定処理を書いて、データの整合性を保つことができるようにしているのです。
内部の処理は今まで説明してきたもので説明できているかと思うので、ここでは説明しません。
ここまできたら、出荷履歴を更新する機能は実装完了です。
最後に、機能全体をまとめた”prepareShipent()”関数を作成して締めましょう。
12. 出荷履歴：まとめ / prepareShipment()
/**
* インプットされた出荷対象注文番号をもとに、出荷対象製品を抽出し、
* 在庫状況を確認しながらダッシュボード以外の各シートを更新していくために、
* これまでの処理をまとめた関数。
* @return {void} 全体の処理をまとめた関数なので返り値なし
*/
function prepareShipment(){
// 出荷対象_注文番号にインプットされているデータを抽出
const targetOrderArray = getTargetOrder()
// 受注情報から、出荷対象のデータだけを抽出する
const dataFilteredOrder = filterOrderList(targetOrderArray)
// 出荷対象のデータと入荷状況を突き合わせて、数量の減算をする
const updateAmount_OK_CANCEL = calcStock(dataFilteredOrder)
// 数量の減算加算をして、問題がなければ受注情報シートにもデータを書き戻す
writeBackDataToSheets(updateAmount_OK_CANCEL, targetOrderArray, dataFilteredOrder)
}シンプルに今まで作成してきた関数をまとめて実行可能にしただけです。
それでは、出荷対象_注文番号シートをスクショの状態にして”prepareShipment()”関数を実行してみましょう。
1回の実行で、出荷履歴シートの内容がこのような状態になったら成功です！
それでは、正しく2回目の実行でこのシートの下に履歴を追加することが出ているか確認していきましょう。
試しに、出荷対象_注文番号シートへのインプットをMM0002だけに変更して、再度”prepareShipment()”関数を実行してみてください。
受注一覧シートが以下の状態になっており
MM0002の製品に出荷済みフラグがついている状態
出荷履歴シートが以下の状態になっていたら成功です！
これで、出荷履歴の記録までできるようになりましたね！
本当は同一製品を2回出荷できないようにするためのチェックや、inputに入れていたNG1000などの受注一覧に存在しない値を検知するためのチェックなど入れるべきですが、今回は紙面の都合上省略しています。（すでにかなり長いですが。。。ｗ）
どんな処理を入れたら使いやすくなるかなど考えて、余力のある方は追加で実装してみると面白いかと思います。
追加分は出荷日時が分かれて入っている。
13. ダッシュボード：ユニークリスト作成/uniqueProduct()
ここまでは、出荷履歴を記録する機能を作成をしてきました。ここからは、今現在、在庫として存在している製品は製品単位でそれぞれいくつあるのか？を表示するダッシュボードを作っていきましょう。
完成形としては、スプレッドシートでピボットテーブルを組んだ時のイメージが近いです。同一製品が複数行に分かれて存在していても、数量を合算して表示する事ができるようにしたいと思います
全体の流れとしては、以下の流れで処理を進めていきます。
入荷一覧をもとに、製品コードでユニークな製品リストを作成する
１で作成したリストに対して、入荷一覧の入荷数量と費消数量をみながら製品毎に在庫数量の合計を出していく
２で作成したリストを製品コード順にソートする
シートにデータを書き戻す
まずは、１のユニークリスト作成から取り組んでいきましょう。
出荷履歴記録機能では “prepareShipment.gs”ファイルにコードを作成してきましたが、今度は機能が分かれるので、”createDashboard.gs”ファイルにコードを書いていきます。
/**
* 入荷一覧には製品コードが被っているものがあるので、これから在庫一覧を作成するにあたって
* 製品コードに重複がないリストを作成していくための関数。
* @return {array} dataProduct 二次元配列の製品コード単位でユニークなリストを返す。
*/
function uniqueProduct() {
const productCodes = []
const dataProduct = []
const dataStockList = sheetStockList.getDataRange().getValues()
// ユニークな製品リストを作成する
dataStockList.forEach(row => {
if (!productCodes.includes(row[1])){
dataProduct.push(
[row[1],row[2],0] //リストを作成する際に、ロットの合計を入れるためのカラムも作成しておく
)
productCodes.push(row[1])
}
})
// 現段階でヘッダーは不要なので削除しておく
dataProduct.shift()
return dataProduct
}ユニークな製品リストを作成するために、学習済みの”forEach()”関数と”includess()”関数を組み合わせて使っています。
一見ややこしいですが、ここまで理解できている方であれば順を追ってみていけば理解可能なはずです。
ただ、この関数内では３つ説明していない記法が登場しているので、順番に説明していきます。
if (!productCodes.includes(row[1])){...}まずはif文の条件式に登場している「！」です。
これは、True -> False / False -> Trueに条件を反転させる記号です。
サンプルを記載しておきますので、軽く目を通していただければ直感的にわかるかと思います。
function trueOrFalse(a, b){
if (!(a < b)){
console.log(`${a}は${b}よりも小さくないです`)
} else {
console.log(`${a}は${b}以下です`)
}
}
function call(){
trueOrFalse(1,4) //1は4以下です
trueOrFalse(6,3) //6は3よりも小さくないです
}次に、if文の内部で記載されている処理です。
push():
dataProduct.push(
[row[1],row[2],0] //リストを作成する際に、ロットの合計を入れるためのカラムも作成しておく
)
productCodes.push(row[1])ここでは、２つの配列、”dataProduct”と”productCodes”に対してそれぞれ”push()”関数を呼び出しています。
”push()”関数は、配列の最後に要素を追加するための関数です。これもシンプルな関数なので、サンプルだけ置いておきます。
function pushSample(){
const array1 = [1,2,3]
const array1Length = array1.push(10)
//[ 1, 2, 3, 10 ]
console.log(array1)
// 4
console.log(array1Length)
const array2 = [[1,2,3], [4,5,6], [7,8,9]]
const array2Length = array2.push([10,11,12])
//[ [ 1, 2, 3 ], [ 4, 5, 6 ], [ 7, 8, 9 ], [ 10, 11, 12 ] ]
console.log(array2)
// 4
console.log(array2Length)
const array3 = ["my", "name"]
array3.push("is", "shogo")
//[ 'my', 'name', 'is', 'shogo' ]
console.log(array3)
}このように”push()”関数を配列に対して呼び出すと、返り値としては要素が追加された新しい配列ではなく、配列の長さが返ってくるので注意です。
配列の長さが不要な時には、”array3”のように活用することも可能です。
また、”push()”関数には”array3”の例のように、引数を複数渡すことも可能になっています。
shift():
// 現段階でヘッダーは不要なので削除しておく
dataProduct.shift()“shift()”関数は、配列に対して呼び出すことができる関数で、配列の最初の要素を削除します。
少し前に出てきた、”unshift()”関数が配列の先頭に要素を追加していたのと逆のことを行うわけです。
function shiftSample(){
const array1 = [1,2,3]
const array1Length = array1.shift()
//[ 2, 3 ]
console.log(array1)
// 1
console.log(array1Length)
const array2 = [[1,2,3], [4,5,6], [7,8,9]]
const array2Length = array2.shift()
//[ [ 4, 5, 6 ], [ 7, 8, 9 ] ]
console.log(array2)
// [1,2,3]
console.log(array2Length)
const array3 = ["my", "name"]
array3.shift()
// ["name"]
console.log(array3)
}“shift()”関数では、元の配列を変更して返り値は「取り出した要素」になることに注意です。”push()”関数などと同様に、定数に割り当てることなく、”array3”の例のように呼び出すことも可能です。
ここまでで、”uniqueProduct()”関数に関しては全て理解できる土壌が整いました。ぜひ個別の関数を理解した状態で、もう一度関数全体を見直してみてください。
14. ダッシュボード：在庫数量の算出/sumLot()
/**
* ユニークな製品で構成された二次元配列に対して、在庫総数を付与していくための関数。
* エクセルでいうところの、sumif()関数 or ピボットテーブルを使っているイメージ。
* @param {array} 二次元配列のユニークな製品一覧。
* [製品コード, 製品名, 在庫数量]の形だが、この時点では在庫数量は0
* @return {array} 二次元配列のユニークな製品一覧。
* ここでは在庫数量がインプットされ、在庫が０の製品は出ない。
*/
function sumLot(dataProduct) {
// 入荷一覧の情報を取得
const dataStockList = sheetStockList.getDataRange().getValues()
// dataProductに対して、製品コード別に在庫数量を合計していく
dataProduct.forEach(row => {
dataStockList.forEach(product => {
// 製品コードで合計する
if(product[1]===row[0]){
// 在庫数量に入荷数量-費消数量を足す
row[2] += product[3]-product[4]
}
})
})
// 在庫数量が０の製品は在庫がない製品なので、リストからはじく
const dataFilteredProduct = dataProduct.filter(row => row[2] > 0)
return dataFilteredProduct
}この関数内では”forEach()”関数を入れ子にして実行し、１３で作成したユニークリストの製品それぞれに対して、製品コードをキーにして入荷一覧の製品を確認していき、製品コードが一致していた場合入荷数量-費消数量=在庫数量として数量を計算していっています。
入れ子構造になっているので、少しややこしく見えるかもしれませんが、全てこれまでに学習済みの処理で構成されているので落ち着いてみて見てください。もし難しければ、具体の数字を入れて自分でコードを動かしてみると理解しやすくなるかと思います。
15. ダッシュボード：データの並び替え/sortData()
/**
* 二次元配列をソートして、製品コード順に並べるための関数
* @param {2D Array} dataProduct 二次元配列の在庫一覧を渡す。この時点ではヘッダーはなし
* @return {2D Array} dataProduct 二次元配列のソートされた在庫一覧を返す。
* return前にヘッダーを付与しておき、すぐにシートに貼り付けできるようにしておく。
*/
function sortData(dataProduct){
dataProduct.sort()
dataProduct.unshift(["製品コード","製品名","在庫数量"])
return dataProduct
}いよいよ、やっとこ終盤に来ました。これで機能面の実装は最後です。
ここでは、引数で渡した二次元配列の１列目をキーにして、sort関数を用いてアルファベット順での並べ替えを行っています。
並べ替えたら、最後に”unshift()”関数でヘッダーを付与してシートに貼り付けることができる状態のデータに整形して完成です。
それでは、”sort()”関数に関してみていきましょう。
sort():
dataProduct.sort()“sort()”関数は名前に反して少し癖がある関数です。
今回は文字列評価しているので問題になっていないのですが、数字を並び替えたい時が厄介です。
function sortSmaple(){
const array1 = [4,10,5]
array1.sort()
//[ 10, 4, 5 ]
console.log(array1)
const array2 = ["a", "cd", "azd", "po"]
array2.sort()
//[ 'a', 'azd', 'cd', 'po' ]
console.log(array2)
const array3 = ["NS0010","BE1000","SH1000","NS0004","LS1000","PL3000"]
array3.sort()
//[ 'BE1000', 'LS1000', 'NS0004', 'NS0010', 'PL3000', 'SH1000' ]
console.log(array3)
}“array1”の例を見てください。本当は[4, 5, 10]となってほしいところですが、[10, 4, 5]と期待に反した結果になっています。
これは、”sort()”関数が実行される際に一度文字列に変換してから実行されていることが関係しているのですが、ここでは深く立ち入らないでおきましょう。
では、数字を並べ替えたいときにはどうするかというと、以下のように”sort()”関数の引数に比較関数を渡すと実現することができます。
const array4 = [4,10,5]
array4.sort((a, b) => {
return a - b;
});
console.log(array4)比較関数の引数に設定されているa,bには配列内の要素が順番に割り当てられていき、return a-bの結果がマイナスかプラスかで並び替えが行われていくと考えていただけるとわかりやすいかと思います。
興味がある方は、こちらなどを読んでいただくとより詳しい説明が載っています。ただ、この仕組みに関しては少し難しいと思うので、わからなくてもいったん慣用句的に覚えてしまってもいいかと思います。
16. ダッシュボード：まとめ / createDashboard()
/**
* ダッシュボード更新処理をまとめるための関数
* 処理の流れ
* 1. ユニークな製品コードリストを作成する
* 2. 1で作成したリスト毎に在庫数量を足し合わせていく
* イメージはピボットテーブルを作成 or sumif関数での集計
*/
function createDashboard(){
const dataProduct = uniqueProduct()
const dataResult = sumLot(dataProduct)
const dataSortedResult = sortData(dataResult)
sheetDashboard.getDataRange().clearContent()
sheetDashboard.getRange(1,1,dataSortedResult.length, dataSortedResult[0].length).setValues(dataSortedResult)
}いよいよダッシュボードの実装も大詰めにきました。
”createDashboard()”関数は基本的に、今まで作成してきた関数をまとめているだけですが、１箇所だけ新しい機能を実装しています。
sheetDashboard.getDataRange().clearContent()“sheetDashboard.getDataRange()”まではすでに学習済みで、”sheetDashboard”の中で値が入っている範囲を取得しているRangeオブジェクトが生成されています。ここに、”clearContent()”関数を呼び出しています。
関数名から想像できますが、これはRangeオブジェクトで選択された範囲内全ての値を削除するための関数です。今回、在庫ダッシュボードにおいては在庫が減るとダッシュボードに表示される製品数が減ることがあるので、アップデートした際に古いデータが残らないよう、一度古いデータを全部削除してから新しいデータをシートに貼り付けるようにしています。
ここまで完成したら、スクリプトエディタ上から”createDashboard()”関数を実行してみてください。
きっとこんな感じになっているはずです。
17. ユーザー向け実行ボタン作成：main()
やっと、やっと最後まで来ました。お疲れ様でした。
しかし！これではまだユーザーが使用することができないので、ユーザーが使えるようにスプレッドシート上にボタンを設置していきます。
input>>>出荷対象_注文番号の右側に、「出荷登録ボタン」シートを新しく作成しましょう。
シートを作成したら、上のスクショに従って進んでください。
すると、こんな画面が出ると思うので、好きな図形を選択して、保存して終了を押します。この際に、ボタンに文字を追加することができるので、ユーザーがパッとみてわかりやすい名前をつけておきましょう。
今回は「出荷記録登録」とボタンに書いておきました。後からも文字は変更可能です。
ここから、このボタンに対して関数を割り当てて、ボタンをクリックしたときに関数が実行されるようにしていきます。
ボタンをクリックすると、わかりづらいですが右上に丸が３つ出てきますので、これをクリックして、
スクリプトを割り当てを選択
まだ作成していないですが、これまでの処理を全てまとめた”main()”関数をこれから作成するので「main」と入力。この際に、()などを入れると正しく実行できなくなってしまうので、ここでは関数の名前のみを登録しておきます。
ここまできましたら、肝心の”main()”関数を作成しましょう。といっても、
スクリプトエディタ上で新しくファイルを作成して、これまで作成した”prepareShipment()”関数と”createDashboard()”関数をまとめるだけです。
main関数の中身はシンプルです。
// 全体の処理をまとめた関数。
// スプシ上に貼り付けるボタンで活用する。
function main(){
prepareShipment()
createDashboard()
}これで全ての作業が完成です！好きな注文番号を出荷対象_注文番号シートに入れてボタンから実行してみてください。問題なく動作することが確認できたでしょうか？お疲れさまでした！！！！
コード全体像
最後に、コード全体像を残しておきますので、もし何かうまく動かない部分等あった際にはご自身のコードと見比べてみてください。
_define.gs
// このファイルには、各関数で繰り返し使う共通の定義を書いていく
// 操作対象のスプレッドシートを指定
// 今回はスプレッドシートに紐付けたGASを作成しているので以下方法で指定
const ss = SpreadsheetApp.getActiveSpreadsheet();
// それぞれのシートを１枚ずつ指定
// output>>>
const sheetDashboard = ss.getSheetByName("在庫ダッシュボード");
const sheetShipmentHistory = ss.getSheetByName("出荷履歴");
// raw_data>>>
const sheetStockList = ss.getSheetByName("入荷一覧");
const sheetOrderList = ss.getSheetByName("受注一覧");
// input>>>
const sheetOrderNumberInput = ss.getSheetByName("出荷対象_注文番号");createDashboard.gs
/**
* 入荷一覧には製品コードが被っているものがあるので、これから在庫一覧を作成するにあたって
* 製品コードに重複がないリストを作成していくための関数。
* @return {array} dataProduct 二次元配列の製品コード単位でユニークなリストを返す。
*/
function uniqueProduct() {
const productCodes = []
const dataProduct = []
const dataStockList = sheetStockList.getDataRange().getValues()
// ユニークな製品リストを作成する
dataStockList.forEach(row => {
if (!productCodes.includes(row[1])){
dataProduct.push(
[row[1],row[2],0] //リストを作成する際に、ロットの合計を入れるためのカラムも作成しておく
)
productCodes.push(row[1])
}
})
// 現段階でヘッダーは不要なので削除しておく
dataProduct.shift()
return dataProduct
}
/**
* ユニークな製品で構成された二次元配列に対して、在庫総数を付与していくための関数。
* エクセルでいうところの、sumif()関数 or ピボットテーブルを使っているイメージ。
* @param {array} 二次元配列のユニークな製品一覧。
* [製品コード, 製品名, 在庫数量]の形だが、この時点では在庫数量は0
* @return {array} 二次元配列のユニークな製品一覧。
* ここでは在庫数量がインプットされ、在庫が０の製品は出ない。
*/
function sumLot(dataProduct) {
// 入荷一覧の情報を取得
const dataStockList = sheetStockList.getDataRange().getValues()
// dataProductに対して、製品コード別に在庫数量を合計していく
dataProduct.forEach(row => {
dataStockList.forEach(product => {
// 製品コードで合計する
if(product[1]===row[0]){
// 在庫数量に入荷数量-費消数量を足す
row[2] += product[3]-product[4]
}
})
})
// 在庫数量が０の製品は在庫がない製品なので、リストからはじく
const dataFilteredProduct = dataProduct.filter(row => row[2] > 0)
return dataFilteredProduct
}
/**
* 二次元配列をソートして、製品コード順に並べるための関数
* @param {2D Array} dataProduct 二次元配列の在庫一覧を渡す。この時点ではヘッダーはなし
* @return {2D Array} dataProduct 二次元配列のソートされた在庫一覧を返す。
* return前にヘッダーを付与しておき、すぐにシートに貼り付けできるようにしておく。
*/
function sortData(dataProduct){
dataProduct.sort()
dataProduct.unshift(["製品コード","製品名","在庫数量"])
return dataProduct
}
/**
* ダッシュボード更新処理をまとめるための関数
* 処理の流れ
* 1. ユニークな製品コードリストを作成する
* 2. 1で作成したリスト毎に在庫数量を足し合わせていく
* イメージはピボットテーブルを作成 or sumif関数での集計
*/
function createDashboard(){
const dataProduct = uniqueProduct()
const dataResult = sumLot(dataProduct)
const dataSortedResult = sortData(dataResult)
sheetDashboard.getDataRange().clearContent()
sheetDashboard.getRange(1,1,dataSortedResult.length, dataSortedResult[0].length).setValues(dataSortedResult)
}main.gs
// 全体の処理をまとめた関数。
// スプシ上に貼り付けるボタンで活用する。
function main(){
prepareShipment()
createDashboard()
}prepareShipment.gs
/**
* 出荷対象_注文番号シートに入力した値を取得するための関数
* @return {Array} targetOrderArray 出荷対象である注文番号をスプシから取得して返す
*/
function getTargetOrder() {
// 出荷対象_注文番号シートに入力されている情報を抜き出す
const dataTargetOrder = sheetOrderNumberInput.getDataRange().getValues()
// 二次元配列を扱いやすいように１次元配列に変換する
const targetOrderArray = dataTargetOrder.flat()
// returnで書いたものが、この関数を呼び出した際に結果として返ってくる
return targetOrderArray
}
/**
* 受注一覧の中から、出荷対象注文番号の製品だけを抽出するための関数
* @return {2D Array} 受注一覧シートから、出荷対象対象注文番号と注文番号が一致する製品だけを抽出した二次元配列
* @param {Array} targetOrderArray 出荷対象注文番号の一次元配列
*/
function filterOrderList(targetOrderArray){
// 受注一覧シートのデータを二次元配列で取得
const dataOrderList = sheetOrderList.getDataRange().getValues()
// dataOrderListから、targetOrderArrayに含まれている注文番号の製品だけ抽出する
const dataFilteredOrder = dataOrderList.filter(function(row) {return targetOrderArray.includes(row[1])})
return dataFilteredOrder
}
/**
* 入荷一覧シートと出荷対象製品を比較して、在庫数量が十分か、不十分か判定するための関数
* @param {2D Array} dataFilteredOrder 出荷対象製品の二次元配列
* @return {String | 2D Array} "cancel" | dataStockList
* キャンセルボタンが押された場合にはcancelの文字列、
* アラートが出なかった場合には費消状況が更新された状態の入荷一覧の二次元配列
*/
function calcStock(dataFilteredOrder) {
// 配列の中身を操作するので、コピーを作成しておく
const dataFilteredOrderCopy = JSON.parse(JSON.stringify(dataFilteredOrder))
// 入荷一覧の情報を取得
const dataStockList = sheetStockList.getDataRange().getValues()
// ２つの二次元配列に対してループを回し、出荷対象製品と在庫の数量をチェックしていく
for (let i = 0; i < dataFilteredOrderCopy.length; i++){
// 受注情報の製品コードを定数に持たせる
const productIdOrder = dataFilteredOrderCopy[i][2]
for (let k = 0; k < dataStockList.length; k++){
// 入荷一覧の製品コードを定数に持たせる
const productIdStock = dataStockList[k][1]
// 製品コードが一致しているかどうかをチェックしつつ数量調整していく
// 製品コードが一致しているときかつ入荷一覧の数量が０ではないとき
// *入荷一覧の数量は入荷数量-費消数量で考える
if (productIdOrder===productIdStock && dataStockList[k][3]-dataStockList[k][4] !== 0){
// 受注情報の数量 < 入荷一覧の数量の時
if (dataFilteredOrderCopy[i][4] < dataStockList[k][3]-dataStockList[k][4]){
dataStockList[k][4] += dataFilteredOrderCopy[i][4]
dataFilteredOrderCopy[i][4] = 0
// これ以上ループが回っても、対象製品の数量は０から変わらずなのでループ終了
break
// 受注情報のロット >= 入荷一覧のロットの時
} else {
dataFilteredOrderCopy[i][4] -= dataStockList[k][3]-dataStockList[k][4]
dataStockList[k][4] = dataStockList[k][3]
}
}
}
// 内側のループが終わっても、受注情報の製品コードが0になっていない場合
// 出荷に対して在庫数量が足りないことを意味するので、アラートを出す。
if (dataFilteredOrderCopy[i][4] > 0){
// OKボタンが押されると"ok"を、キャンセルボタンが押されると"cancel"が返される
const ok_cancel = Browser.msgBox(
`${productIdOrder}の在庫数量が${dataFilteredOrderCopy[i][4]}個足りません。
\nこのまま処理を続けますか？`, Browser.Buttons.OK_CANCEL
)
if (ok_cancel === "cancel"){
// もし処理を続けない場合、シートへの書き戻しはしないで処理を終了させる。
return "cancel"
}
}
}
// キャンセルボタンが押されていない場合、費消数量を調整したdataStockListを返す
return dataStockList
}
/**
* 出荷対象製品に出荷済みフラグを立ててシートにデータを書き戻す関数
* @param {2D array} 出荷済フラグがついていない状態の受注一覧の二次元配列
* @return {void} シートに書き戻すための関数なので、値は何も返さない
*/
function addShippedFlag(targetOrderArray) {
const dataOrderList = sheetOrderList.getDataRange().getValues()
for (let i = 0; i< targetOrderArray.length; i++ ){
const orderNumber = targetOrderArray[i]
for (let k = 0; k<dataOrderList.length; k++){
const listOrderNumber = dataOrderList[k][1]
if (orderNumber === listOrderNumber){
dataOrderList[k][5] = "done"
}
}
}
// 出荷した製品にdoneのフラグをつけたら、シートに書き戻す。
sheetOrderList.getDataRange().setValues(dataOrderList)
}
/**
* 出荷対象製品を出荷履歴シートの1番後ろに追加して更新するための関数
* @param {2D Array} 出荷対象製品の二次元配列
* @return {void} シートを更新するための関数なので値は何も返さない
*/
function updateShipmentHistory(dataFilteredOrder){
// 実行タイミングのタイムスタンプを作成
const timestamp = new Date()
// この関数は後ほど作成する。二次元配列から任意の列だけ抜き出す関数
const dataExtracted = getColumns(dataFilteredOrder, [2,3,4])
// 最初の列にタイムスタンプを追加する
dataExtracted.forEach(row => row.unshift(timestamp))
// 出荷履歴シートの最終行を取得する
const lastRow = sheetShipmentHistory.getLastRow()
// 出荷履歴シートにデータを戻す
sheetShipmentHistory.
getRange(lastRow+1, 1, dataExtracted.length, dataExtracted[0].length).
setValues(dataExtracted)
}
/**
* 二次元配列をスプシのように捉えたときに、列単位で行を抽出するための関数
* @param {2D Array} arr 列を抽出したい二次元配列。
* @param {Array} indices 何行目と何行目を抽出したいのかを配列で指定する。
* 例：1,4,5列目を抽出したい場合、[0,3,4]
* @return {2D Array} arr indicesで選択した列だけが抽出された状態の二次元配列
*/
function getColumns(arr, indices){
return arr.map(row => indices.map(i => row[i]))
}
/**
* 最後まで問題なく処理が進んだ場合だけ、複数シート全体更新する関数。
* 在庫数量不足で処理が失敗した場合には、シートは一切更新せず処理を終了する。
* @param {String | 2D Array} OK_CANCEL キャンセルボタンが押されていたらcancelの文字列
* 処理が問題なく進んでいるorOKボタンが押されていたら費消数量更新済み入荷状況の二次元配列
* @param {Array} targetOrderArray 出荷対象_注文番号シートに入力されたインプットの配列
* @param {2D Array} dataFilteredOrder 出荷対象受注製品の二次元配列
* @return {void} シートを更新するための関数なので返り値なし
*/
// 出荷情報と入荷状況の数量をチェックして、問題ないときにデータをシートに書き戻すための関数
function writeBackDataToSheets(OK_CANCEL, targetOrderArray, dataFilteredOrder) {
// 処理を途中でcancelしていた場合
if (OK_CANCEL==="cancel"){
// 何も処理をせずに終了
console.log("在庫数量が足りないため、処理を終了しました")
return
}
// 入荷一覧に費消数量をアップデートしたデータを書き戻す
sheetStockList.getDataRange().setValues(OK_CANCEL)
// 受注情報シートにも出荷済みフラグを付与した上でデータを書き戻す
addShippedFlag(targetOrderArray)
// 出荷履歴シートも更新する
updateShipmentHistory(dataFilteredOrder)
}
/**
* インプットされた出荷対象注文番号をもとに、出荷対象製品を抽出し、
* 在庫状況を確認しながらダッシュボード以外の各シートを更新していくために、
* これまでの処理をまとめた関数。
* @return {void} 全体の処理をまとめた関数なので返り値なし
*/
function prepareShipment(){
// 出荷対象_注文番号にインプットされているデータを抽出
const targetOrderArray = getTargetOrder()
// 受注情報から、出荷対象のデータだけを抽出する
const dataFilteredOrder = filterOrderList(targetOrderArray)
// 出荷対象のデータと入荷状況を突き合わせて、数量の減算をする
const updateAmount_OK_CANCEL = calcStock(dataFilteredOrder)
// 数量の減算加算をして、問題がなければ受注情報シートにもデータを書き戻す
writeBackDataToSheets(updateAmount_OK_CANCEL, targetOrderArray, dataFilteredOrder)
}最後に
超長文にお付き合いくださりありがとうございました。
ここまで手を動かしながら目を通してくださった方は、きっとGASをはじめとした自動化、効率化がお好きな方でしょう。
今回のチュートリアルにて紹介した機能以外もGASには沢山の機能があります。ルールさえ決まれば、GAS＋αを組み合わせれば大体のことが自動化可能です。
（ちなみに、GASについてググる場合には「GAS 〜〜」ではなく、「JavaScript 〜〜」でググるのがおすすめです。GAS固有の機能はJavaScriptでは見つけられないのですが、データの操作などに関することはJavaScriptのほとんどの機能がGASでそのまま使えます。「GAS 〜〜」でググると特に日本語の記事は良い記事が出てきません。。。もちろん中にはいい記事もあるのですが。。。）
このチュートリアルで自動化に興味が湧いて下さった方が、社内でガンガン自動化を進め、働く人々のポテンシャル解放を進めていって下さったならば、締め切りに追い込まれながら記事を書いた私としても嬉しい限りです。
働く人々、いや、製造業で働く人々、ひいては製造業全体のポテンシャル解放がしたいぞ！という方はぜひTwitterのDM(@__shogo__) or FBなどよりご連絡頂けたら嬉しいです。（一応社内アドベントカレンダー企画ということで最後に宣伝ｗ）