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

はじめに

この動画を見ていただけると、大体何をやるのか理解していただけるかと。。

Google Apps Scriptで在庫管理】選択範囲の中で取得した値を使って、別シートで計算する pic.twitter.com/wDrpmCA9w3

— げん げんと (@gento34165638) June 19, 2020

経緯

Google Apps Script 入力された文字を別シートにて検索し、その隣にある文字を書き出す
こちらの続き

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

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

用意するもの

在庫をまとめたシート

作ったアイテムをまとめたシート

全体の流れ

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

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

全てのコード

コード.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するのか？

// ループする回数「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は何？

 // 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）が「範囲選択した部分は何行あったか？」と言うデータが入っている。

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

    return [values, numRows];
}

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

// 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();
    }
}