シート２を更新した際、シート１の条件一致したセル行の該当列に数量と更新日が反映されるようにしたいです。

前提・実現したいこと

現在、在庫管理表を作成したく試行錯誤しております。
しかし勉強不足もあり再現ができずに行き詰っています。
是非知恵をお貸し頂ければと思い投稿させて頂きました。

質問にあたり仮のスプレットシートを作成しました。
「在庫表」と３種類のバッテリーを想定し
又それを使用する現場という欄を作成し、それを「A」「B」「C」と割り振り
シートを作成しております。

シート「A」にて現場名を入力すると日付が更新されるスクリプトまでは作成できました。
状態を「入庫」とし数量を入力した際
シート「在庫表」の該当の現場の在庫数と入庫日が反映されるように
又「出庫」とした場合には在庫数を数量分マイナスし出庫日が反映されるように
する事は可能でしょうか？
その場合はスクリプトは分けるべきなのでしょうか？

色々と調べては見たのですが実現には至っておりません。
無知で大変申し訳ございませんが、ご教示頂けると幸いです。
宜しくお願い致します。

![
![

該当のソースコード

GAS
1ソースコード

function onEdit(e){

var ss = SpreadsheetApp.getActiveSpreadsheet();
var edit_sheet_name = ss.getActiveSheet().getSheetName();
Logger.log(edit_sheet_name);

switch (edit_sheet_name){
case 'A':
onEdit1();
break;

case 'B':
  onEdit2();
  break;

case 'C':
  onEdit3();
  break;

}
}

// シートに変更があった時に実行する関数
function onEdit1() {

var ss = SpreadsheetApp.getActive().getSheetByName("A");// 変更のあったシート
var currentRow = ss.getActiveCell().getRow(); //アクティブなセルの行番号を取得
var currentCol = ss.getActiveCell().getColumn(); //アクティブなセルの列番号を取得
var currentCell = ss.getActiveCell().getValue(); //アクティブなセルの入力値を取得
var updateRange = ss.getRange('D' + currentRow); //どの列に更新日時を挿入したいか。この場合はA列
Logger.log(updateRange); //更新日の記入
if(currentRow > 3　&& currentCol == 1) { //2行目以降かつG列の変更を参照とする
if(currentCell) {
updateRange.setValue(new Date());
}
}
}

function onEdit2() {

var ss = SpreadsheetApp.getActive().getSheetByName("B");// 変更のあったシート
var currentRow = ss.getActiveCell().getRow(); //アクティブなセルの行番号を取得
var currentCol = ss.getActiveCell().getColumn(); //アクティブなセルの列番号を取得
var currentCell = ss.getActiveCell().getValue(); //アクティブなセルの入力値を取得
var updateRange = ss.getRange('D' + currentRow); //どの列に更新日時を挿入したいか。この場合はA列
Logger.log(updateRange); //更新日の記入
if(currentRow > 3　&& currentCol == 1) { //2行目以降かつG列の変更を参照とする
if(currentCell) {
updateRange.setValue(new Date());
}
}
}

// シートに変更があった時に実行する関数
function onEdit3() {

var ss = SpreadsheetApp.getActive().getSheetByName("C"); // 変更のあったシート
var currentRow = ss.getActiveCell().getRow(); //アクティブなセルの行番号を取得
var currentCol = ss.getActiveCell().getColumn(); //アクティブなセルの列番号を取得
var currentCell = ss.getActiveCell().getValue(); //アクティブなセルの入力値を取得
var updateRange = ss.getRange('D' + currentRow); //どの列に更新日時を挿入したいか。この場合はA列
Logger.log(updateRange); //更新日の記入
if(currentRow > 3　&& currentCol == 1) { //2行目以降かつG列の変更を参照とする
if(currentCell) {
updateRange.setValue(new Date());
}
}
}

行動規範の内容に同意します

回答1件

ベストアンサー

javascript
1
2const onEdit = (e) => {
3    if(typeof e.value === "undefined") return;
4    if(isItemSheetEdited(e)) {
5        itemSheetEditAction(e);
6        return;
7    }
8}
9const isItemSheetEdited = (eventObject) => ["品目A","B","C"].includes(eventObject.source.getSheetName());
10const itemSheetEditAction = (eventObject) => {
11    const storageSheet = "在庫表";
12    const r = eventObject.range.getRow();
13    const c = eventObject.range.getColumn();
14    if(!isEditingTargetTable(r,c)) return;
15    const spreadsheet = eventObject.source;
16    const range = eventObject.range;
17    const sheet = range.getSheet();
18    const row = spreadsheet.getSheetValues(r,1,1,4)[0];
19    if(!isRowReadyToAction(row)) return;
20    const now = new Date();
21    fillDateOnItemSheet(sheet,r,now);
22    if(!cascadeStorage(spreadsheet.getSheetByName(storageSheet),sheet.getSheetName(),[...row.slice(0,3),now])) {
23        rollbackItemSheet(sheet, r);
24        console.log("在庫を下回る出庫");
25        return;
26    };
27}
28const isEditingTargetTable = (row, column) => {
29    const startRow = 2;
30    const startColumn = 1;
31    const endColumn = 3;
32    const r = row;
33    const c = column;
34    if(r < startRow) return false;
35    if(c < startColumn) return false;
36    if(c > endColumn) return false;
37    return true;
38}
39const isRowReadyToAction = (values) => {
40    if(values.length < 4) return false;
41    if(values[0] === "") return false;
42    if(!/[出入]庫/.test(values[1])) return false;
43    if(!Number.isSafeInteger(values[2])) return false;
44    if(values[3] !== "") return false;
45    return true;
46}
47const fillDateOnItemSheet = (sheet, row, now) => sheet.getRange(row,4).setValue(now);
48const cascadeStorage = (sheet, sheetName, values) => {
49    const storage = sheet.getDataRange().getValues();
50    let idx = -1;
51    for(let i = 1; i < storage.length; i++) {
52        if(storage[i][1] === sheetName) idx = i;
53        if(storage[i][3] !== values[0]) continue;
54        if(idx !== -1) {
55            idx = i;
56            break;
57        }
58    }
59    if(idx === -1) {
60        if(values[1] === "出庫") return false;
61        appendStorage(sheet,sheetName, values);
62        return true;
63    }
64    const current = Number(storage[idx][4]);
65    const next = values[1] === "入庫" ? current + values[2] : current - values[2];
66    if(next < 0) {
67        return false;
68    }
69    const dateColumn = values[1] === "入庫" ? 5 : 6;
70    storage[idx][4] = next;
71    storage[idx][dateColumn] = values[3];
72    sheet.getDataRange().setValues(storage);
73    return true;
74}
75const rollbackItemSheet = (sheet, row) => {
76    sheet.getRange(row,3,1,2).clearContent();
77}
78const appendStorage = (sheet,itemName, values) => {
79    const storage = sheet.getDataRange().getValues();
80    let idx = -1;
81    let bottom = -1;
82    for(let i = 1; i < storage.length; i++) {
83        if(storage[i][1] === itemName) {
84            idx = i;
85            continue;
86        }
87        if((idx !== -1) && (storage[i][1] !== "")) {
88            bottom = i;
89            break;
90        }
91    }
92    if(idx === -1) {
93        sheet.appendRow(['',itemName,'',values[0],values[2],values[3]]);
94        return;
95    }
96    if(bottom === -1) {
97        sheet.appendRow(['','','',values[0],values[2],values[3]]);
98        return;
99    }
100    sheet.insertRowAfter(bottom);
101    sheet.getRange(bottom+1,1,1,6).setValues([['','','',values[0],values[2],values[3]]]);
102}