【Google Apps Scriptとスプレッドシートで在庫管理】選択範囲の中で取得した値を使って、別シートで計算する

#はじめに
この動画を見ていただけると、大体何をやるのか理解していただけるかと。。
<blockquote class="twitter-tweet"><p lang="ja" dir="ltr">Google Apps Scriptで在庫管理】選択範囲の中で取得した値を使って、別シートで計算する <a href="https://t.co/wDrpmCA9w3">pic.twitter.com/wDrpmCA9w3</a></p>&mdash; げん げんと (@gento34165638) <a href="https://twitter.com/gento34165638/status/1273870185359798272?ref_src=twsrc%5Etfw">June 19, 2020</a></blockquote> <script async src="https://platform.twitter.com/widgets.js" charset="utf-8"></script>

#経緯
[Google Apps Script 入力された文字を別シートにて検索し、その隣にある文字を書き出す](https://qiita.com/kokogento/items/7d8d32a7c3361a954454)
こちらの続き:sweat_smile:

スプレッドシートでアクセサリーの在庫管理をしていると、とてもめんどくさいのが以下の作業！
**`在庫として保有している材料の数　ー　今回使った材料の数　＝　現在の在庫の数`**

アクセサリー作りで使う材料の数は多いようで、それを毎回手動でスプレッドシートを使い引き算するのが面倒だということですね。。。

#用意するもの
##在庫をまとめたシート
<img width="593" alt="スクリーンショット 2020-06-19 17.33.59.jpg" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/167220/85094692-c223-000d-bddf-f3bd7a3a48a3.jpeg">


##作ったアイテムをまとめたシート
<img width="742" alt="スクリーンショット 2020-06-16 21.37.07.jpg" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/167220/351f8ed2-e590-e9e7-ded1-6ccf89d7636c.jpeg">

#全体の流れ
①「作ったアイテム」のシートで使った材料を範囲選択（動画参照）
②カスタムメニューでボタンを作っておいて、それを押すことで発動し、**①で選択した範囲を２次元配列で取得**
③「在庫」シートにて同じ名前の材料を検索し、２つ横のセルの値（各材料の在庫数）を取得
④②と③で取得した在庫数を引き算して、残った在庫数を書き出す

大体の処理の流れはこんな感じ。


#全てのコード

```javascript:コード.gs
// Spreadsheetが開かれた時に自動的に実行されます.
function onOpen() {
  // 現在開いている、スプレッドシートを取得します.
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  // メニュー項目を定義します.
  var entries = [
    {name : "在庫から使った数を減らす"  , functionName : "menuItem1"},
  ];
  // 「Custom」という名前でメニューに追加します.
  spreadsheet.addMenu("Custom", entries);
}
    
 // menuItem1をクリックした時の処理
function menuItem1() {
 //現在アクティブなシートの名前を取得
 var nowSheet = SpreadsheetApp.getActiveSheet().getName();
     //シート「作ったアイテム」以外で実行したらアラート
  if (nowSheet === '作ったアイテム') {
     var data = getData();
     matchData(data);
    } else {
    alert();
    }
}
    
function getData() {
    //現在のスプレットシートを取得する
    var sheet1 = SpreadsheetApp.getActiveSheet();
    var values = sheet1.getActiveRange().getValues();
    var numRows = sheet1.getActiveRange().getNumRows();
    
    return [values, numRows];
}
    
function matchData(data) {
    //現在のスプレッドシートを取得
 var ss = SpreadsheetApp.getActiveSpreadsheet();
   //シート「在庫」を取得
 var stockSheet = ss.getSheetByName('在庫');
  
  //使った材料の配列「values」をdataの中から取り出す
  var items = data[0];

 // ループする回数「numRows」をdataの中から取り出す
  var dataNumRows = data[1];
  var stopSetCount = dataNumRows - 1;
  
  for(var i = 0; i <= stopSetCount; i++){
    var item = items[i];
    
    var itemName = item[0];
    var usedCount = item[2];
     
    //シート「在庫」で材料名を探す
    var textFinder = stockSheet.createTextFinder(itemName);
    var ranges = textFinder.findAll();
    //材料名のセル範囲を取得
    var textPos = ranges[0].getA1Notation();
    //材料名の位置を取る
    var textPosCell = stockSheet.getRange(textPos);
    //材料名の2つ右隣のセルの値を取る
    var targetValue = textPosCell.offset(0, 2).getValue();
    //在庫の数から使った数を引く
    var afterUsedCount = targetValue - usedCount;
    textPosCell.offset(0, 2).setValue(afterUsedCount);
  }
}
    
function alert() {
  var ui = SpreadsheetApp.getUi();
  var title = '「作ったアイテム」のシートで実行してよ';
  var text = 'おかしくなるから！';
  ui.alert(title, text, ui.ButtonSet.OK_CANCEL);
}
```

#軽く解説
##なぜループする回数を-1するのか？
```javascript
// ループする回数「numRows」をdataの中から取り出す
  var dataNumRows = data[1];
  var stopSetCount = dataNumRows - 1;

 for(var i = 0; i <= stopSetCount; i++){
```

範囲選択した部分が何行か？を取得して、ループを回す回数を決めているんだが、なぜ−１するのか？
**結論を言うと、−１しないとループする回数が１回増えてしまうからだ。**

もし範囲選択した部分が4行だったとする。すると４回ループしないといけない。
しかし、for分は`var i = 0;`で0から始まってる。だから−１しないと、１回ループする回数が増えてしまいエラーが起こる。

なので、`var i = 0;`を`var i = 1;`としても良い。

##matchDataで受け取る`data`は何？
```javascript
 // menuItem1をクリックした時の処理
function menuItem1() {
 //省略
     var data = getData();
     matchData(data);
  //省略
}


function matchData(data) {
//省略
//使った材料の配列「values」をdataの中から取り出す
  var items = data[0];

 // ループする回数「numRows」をdataの中から取り出す
  var dataNumRows = data[1];

}
```

ここで受け取ってるのは`getData()`で返されている値。
１つ目の配列（つまり`values`）が**範囲選択した部分のデータが２次元配列で入っている。**
２つ目の配列（つまり`numRows`）が**「範囲選択した部分は何行あったか？」と言うデータが入っている。**


```javascript
function getData() {
    //現在のスプレットシートを取得する
    var sheet1 = SpreadsheetApp.getActiveSheet();
    //getValueではなくgetValues。複数形のsがあるよ！
    var values = sheet1.getActiveRange().getValues(); 
    var numRows = sheet1.getActiveRange().getNumRows();
   
    return [values, numRows];
}
```

##カスタムメニューはこの部分で作られてる

```javascript
// Spreadsheetが開かれた時に自動的に実行されます.
function onOpen() {
  // 現在開いている、スプレッドシートを取得します.
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  // メニュー項目を定義します.
  var entries = [
    {name : "在庫から使った数を減らす"  , functionName : "menuItem1"},
  ];
  // 「Custom」という名前でメニューに追加します.
  spreadsheet.addMenu("Custom", entries);
}

 // menuItem1をクリックした時の処理
function menuItem1() {
 //現在アクティブなシートの名前を取得
 var nowSheet = SpreadsheetApp.getActiveSheet().getName();
     //シート「作ったアイテム」以外で実行したらアラート
  if (nowSheet === '作ったアイテム') {
     var data = getData();
     matchData(data);
    } else {
    alert();
    }
}
```