【GAS】取引完了した在庫数を、マスタへ自動反映したい

Question

前提・実現したいこと

GASで在庫管理シートを作成しております。
「J列：取引完了フラグ」が押されたら「L列：仮在庫計算の値」を、
買取価格表 or 販売価格表シートの「D列：在庫」へ自動反映したいと考えております。

文章のみですと上手く伝えられないため、スプレッドシートのURLを記載します。
https://docs.google.com/spreadsheets/d/1IeiLGX01Z0T2hIaT60gWa8QU_wEhKjZMGKsxV07YRI0/edit#gid=721420774

標題にもございます通り、取引完了した際取引した在庫数を引いた数を、マスタへ自動反映するコードを作成しているのですが上手くいきません。

発生している問題・エラーメッセージ

実装手順を書き出したものの、①の段階で躓いております。
売買のあったアイテムがマスタのどの位置にあるのかを探す処理が上手くいきません。

<実装手順>
①売買のあったアイテム名を、マスタから探す処理(B列：購入希望 or 買取希望？｜購入希望なら「販売価格表」・買取希望なら「買取価格表」)
②行を探せたら、各マスタの「D列：在庫」に「取引シート.L列：仮在庫計算」値をセットする処理
③J列：取引完了フラグが押されたら、①～②の処理が走るように

該当のソースコード

//ゴール
//「J列：取引完了フラグ」が押されたら、「L列：仮在庫計算の値」を、買取価格表 or 販売価格表シートの「D列：在庫」へ自動反映したい。

//実装手順
//①売買のあったアイテム名を、マスタから探す処理(B列：購入希望 or 買取希望？｜購入希望なら「販売価格表」・買取希望なら「買取価格表」)
//②行を探せたら、D列：在庫に値をセットする処理
//③J列：取引完了フラグが押されたら、①～②の処理が走るように

//懸念点
//完了したレコード削除が「J列：取引完了フラグ」チェック時に動いてしまうため、在庫自動更新はこのスプリクトよりも先に走るようにしたい

////////////// ***** 変数まとめ ***** //////////////
//**シート内の全データを取得//
  var ss = SpreadsheetApp.getActiveSheet();
  var data = ss.getDataRange().getValues();

//**変更されたセルを取得//
  var targetCell = ss.getActiveCell();
  var targetRow = targetCell.getRow();

//参照マスタ
  var kaitori = ss.getSheetByName("買取価格表");
  var hanbai = ss.getSheetByName("販売価格表");
////////////// ***** 変数まとめ ***** //////////////

function search() {
//**マスタから探す処理(B列：購入希望 or 買取希望？｜購入希望なら「販売価格表」・買取希望なら「買取価格表」)//
  if(targetCell.getColumn() == 2 && targetCell.getValues() == "購入希望")
    {
    }
  else(targetCell.getColumn() == 2 && targetCell.getValues() == "買取希望")
    {
    }
}

補足情報（FW/ツールのバージョンなど）

完了したレコード削除が「J列：取引完了フラグ」チェック時に動いてしまうため、
在庫自動更新はこのスプリクトよりも先に走るようにしたいと考えております。

// **完了レコード削除** //
function delete_record() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('取引シート');
  var data = sheet.getDataRange().getValues();
  var length = data.length;
  
  for (var i = length - 1; i >= 1 ; i--){
    var keyword = data[i][9]; 
    if(keyword === true){
      sheet.deleteRow(i + 1);
    } 
  }
}

以上でございます。
初心者質問で申し訳ないのですが、お力添えいただけますと幸いでございます。

Answer 1

処理するシートに応じて関数を分けます。

「取引シート」シートにかかわる処理はsearchに集中させ、
「販売価格表」シート／「買取価格表」シートにかかわる処理は、updateStockCount　として別関数に切り出します。

search 関数
「取引シート」シートにかかわる処理＝チェックを入れた行のアイテム名の取得、行の削除

updateStockCount　関数
「販売価格表」シート／「買取価格表」シートにかかわる処理＝アイテム名の検索、該当する行の在庫数の更新


コードは、下記のようになるでしょうか。
取引商品名の検索は、行の最初から文字列を比較していき、等しい文字列に当たったところでストップするというやり方です。

（略）
//**変更されたセルを取得//
  var targetCell = ss.getActiveCell();
  var targetRow = targetCell.getRow();
+ var targetCol = targetCell.getColumn();  // 編集されたセルの列数を取得。
//参照マスタ
(略)

　

/*
* 処理対象＝「取引シート」
* 取引シートのチェックが入ったら、チェックが入った行のアイテム名を探し、
* マスタの在庫数を更新する関数に、シート、アイテム名、更新後の在庫数を渡す。
* 在庫数更新に成功したら、チェックを入れた行を削除する。
 */
function search() {
  // 取引完了チェックボックス以外のセルが編集された場合は何もしない。
  if (targetCol != 10) return;

  var dealType = data[targetRow-1][1];  // 取引タイプ（購入希望　or 買取希望）
  var itemName = data[targetRow-1][2];  // 取引商品名 
  var tempStockCount = data[targetRow-1][11];  // 仮在庫数量
  var targetSheet = null;

  if (dealType == "購入希望") {
    targetSheet = hanbai;
  } else if (dealType == "買取希望") {
    targetSheet = kaitori;
  }
  if (targetSheet != null) {
    var result = updateStockCount(targetSheet, itemName, tempStockCount);
    if (result == true) {
      // 行を削除
      delete_record();
    } else {
      Logger.log(`Failed to delete ${targetSheet.getName()} of row:${targetRow}`);
    }
  }
}

/* 
* 処理対象＝「販売価格表」シート、または「買取価格表」シート
* 引数：
*     sheet：検索対象となるシートのSheetオブジェクト
*     itemName：sheet内を検索する取引商品名
*     newStockCount：sheetのD列を更新する在庫数（取引シートの「仮在庫計算」の数値）
* 
* 戻り値：
*     アイテム名が見つかり、在庫数の更新に成功したらtrue、
*     sheetに指定したシートが不適切または取引商品名が見つからなかった場合はfalse。
*/
function updateStockCount(sheet, itemName, newStockCount) {
  if (sheet.getDataRange() == null) {
    Logger.log(`Could not detect a valid sheet.:[${sheet.getName()}]`);
    return false;
  }
  var sheetData = sheet.getDataRange().getValues();
  // 取引商品名の検索
  for(var rowNum = 1; rowNum < sheetData.length; rowNum++) {
    if (sheetData[rowNum][1] == itemName) {
      // 更新後の在庫数をD列にセット
      sheet.getRange(rowNum+1, 4).setValue(newStockCount);
      Logger.log(`Update ${sheet.getName()} row:${rowNum+1} itemName:${itemName} stockCount:[${sheetData[rowNum][3]} -> ${newStockCount}]`);
      return true;
    }
    
  }
  Logger.log(`Not found itemName:${itemName} from ${sheet.getName()}`);
  return false;
}

Comments

[alicos]

qnoir様

早々のご回答ありがとうございました。
いただいたコードを動かしてみた結果、希望通りの挙動になりました。

途中に処理のコメントも残していただいているので、ドキュメントと見比べながらもっと勉強してみようと思います。

本当にありがとうございました。