Google Apps Scriptでスプレッドシート中にある特定の値を検索し、合致する場合に日付を入力する動作の実現

Question

前提・実現したいこと

Google Apps Scriptを利用し、スプレッドシートにて「確認結果シート」に記入された管理番号から「物品管理シート」に記載されている管理番号を検索し、合致する番号があった場合に同じ行の確認日付欄（24列目）に日付を自動入力する動作を実現したいです。

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

「物品管理シート」の1行目〜50行目ぐらいまでは日付が自動入力されますが、50行目ぐらいから自動入力されなくなってしまいます。（特にエラー等は出ていません）

該当のソースコード

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
 
  //「確認結果」シートを代入
  var sheetAnser = ss.getSheetByName('確認結果シート');
 
  //「物品管理」シートを代入
  var sheetKanri = ss.getSheetByName('物品管理シート');
 
 
  //最終行を取得
  var lastRow = sheetAnser.getLastRow();
 
  //2列目、最終行のセルの値を取得。
  var data = sheetAnser.getRange(lastRow, 2).getValue();
 
  //今日の日付を代入
  var date = new Date();
 
  //物品管理シートの最初の行から最終行まで確認結果シートに記載された管理番号を検索し、一致する番号があると確認日付欄（24列目）に今日の日付を記入する
  for(var i=1;i<=lastRow;i++){
    if(sheetKanri.getRange(i, 2).getValue()=== data){
      sheetKanri.getRange(i, 24).setValue(date);
   
    }
   
   
  }
 
}

Answer 1

確認結果シートの方の最終行を forループで使ってるからではないでしょうか？
物品管理シートの最終行を取得してそちらを使ってみてください。

Comments

[kenn99]

遅くなり申し訳ございません。ご回答ありがとうございます。
確認結果シートの最終行に追加された値を物品管理シートの1行目からforで同じ管理番号がないかを繰り返して探す動作としたのですが、forではだめでしょうか？

[sawa]

forで処理するのが間違ってるのではなくて、forの処理で使っている最終行 lastRow が、 sheetAnser（確認結果シート） の最終行を使ってるところが違ってませんか？という回答です。

for(var i=1;i<=lastRow;i++) で繰り返しても、確認結果シートの最終行までしか動かないので、例えば 物品管理シートが本当は100行あっても、確認結果シートが50行なら 50で終わっちゃうってことです。

なので、forで使う為の物品管理シートの最終行を取得して、それを使うfor文のコードにすれば良いです。

[kenn99]

ご回答ありがとうございます。
確かにおっしゃる通りですね。。おそらくこれが原因かと思いますのでこれを修正したいと思います。

Answer 2

// 最終行を取るパターン
const q305132 = () => {
    const resultSheetName = '結果確認シート';
    const itemSheetName = '物品管理シート';
    const book = SpreadsheetApp.getActive();

    const needle = book.getSheetByName(resultSheetName).getDataRange().getValues().slice(-1)[1];
    const itemSheet = book.getSheetByName(itemSheetName);
    fillIn(itemSheet,1,needle,23,new Date());
}

// そういう操作は onEdit を使うべき
const onEdit_305132 = (e) => {
    const resultSheetName = '結果確認シート';
    const itemSheetName = '物品管理シート';
    if (!e.value) return;
    const curRange = e.range;
    const c = curRange().getColumn();
    if (c !== 2) return;
    const sheet = curRange.getSheet();
    if (sheet.getName() !== resultSheetName) return;
    fillIn(e.source.getSheetByName(itemSheetName),1,e.value,23,new Date());
}

// 共通のヘルパ
const fillIn = (sheet,pos,needle,posToSet,valueToSet) => {
    sheet.getDataRange().getValues().map(e=> (e[pos] === needle?e.map((f,i)=> (i===posToSet?valueToSet:f)):e));
    sheet.getRange(1,1,dat.length,dat[0].length).setValues(dat);
}

物品管理シートに重複があったらどうするのか、という点が気になりました(現状、全てに書き込むようですが)