【GAS】スプレッドシートのシート1を読み込んで、★条件に合致すれば、 シート1のR列と同じ名前を、シート2のA列から探し出し、 同じ行のIDを出力するというプログラムを作成しています。

Question

前提・実現したいこと

スプレッドシートのシート1を読み込んで、★条件に合致すれば、
シート1のR列と同じ名前を、シート2のA列から探し出し、
同じ行のIDを出力するというプログラムを作成しています。

★の条件式はあっているので、
以下部分のプログラムが間違っています。
＝＝＝
シート1のR列と同じ名前を、シート2のA列から探し出し、
同じ行のIDを出力する
＝＝＝

今回実施しているシート1と、シート2は以下内容になり、
条件に合致するのが、シート1の20行目のため、
シート2の「田中」のID「1000001」を出力したいのですが、上手くできません。
エラー内容は、以下になっているのですが、どこを直せばよいか分かる方教えていただけますでしょうか？
よろしくお願いします。

シート1：

シート2：

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

以下のエラーメッセージが出て、正しく実装されません。
【エラー内容】
TypeError: Cannot read property '0' of undefined
test1 @ コード.gs:44

44行目は、コード内の以下部分になります。
//シート2の、A列を定義
var questioner2 = table2[i-2][0];

該当のソースコード

function test1() {

//アクティブなスプレッドシートを取得
var ss = SpreadsheetApp.getActiveSpreadsheet();

//シート1を取得
var sh = ss.getSheetByName("シート1");

//シート2を取得
var sh2 = ss.getSheetByName("シート2");

　//シート1の最終行を定義
var lastRow = sh.getLastRow();

//シート2の最終行を定義
var lastRow2 = sh2.getLastRow();

//スプレッドシート1の、O2～R最終行の値をgetValuesで取得
var table = sh.getRange(2,15,lastRow,4).getValues();

//スプレッドシート2の、A2～B最終行の値をgetValuesで取得
var table2 = sh.getRange(2,1,lastRow2,2).getValues();

//実行日時
var today = new Date();

　//2行目～14行目までを読み込み
for(var i=2; i<=lastRow; i++)
{

//シート1の、O列を定義
var anser = table[i-2][0];

//シート1の、P列を定義
var send = table[i-2][1];

//シート1の、Q列を定義
var cell = table[i-2][2];

//シート1の、R列を定義
var questioner = table[i-2][3];

//シート2の、A列を定義
var questioner2 = table2[i-2][0];

//シート2の、B列を定義
var id = table2[i-2][1];

//シート1の、O列が空白ではない、かつ、シート1の、P列が「送信可能」と入力されている、かつ、シート1の、Q列が空白の場合
if(anser!=="" && send=="送信可能" && cell=="")

{

//シート1のR列の値を含むものを、シート列のA列から探す
if(questioner == questioner2)
{

var sendid = id;

}

//ログでanserの内容を出力（後で、チャットワークに出力に修正する）
Logger.log(anser);
Browser.msgBox(anser);
Browser.msgBox(sendid);
sh.getRange(i, 17).setValue(new Date());

}

}

}

試したこと

ここに問題に対して試したことを記載してください。

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

ここにより詳細な情報を記載してください。

Answer 1

TypeError: Cannot read property '0' of undefined
test1    @ コード.gs:44

44行目は、コード内の以下部分になります。
//シート2の、A列を定義
var questioner2 = table2[i-2][0];

＜このエラーの原因＞

「i」 は「シート1」の2行目から最終行まで繰り返されます。
一方、配列 table2 は「シート2」 の最大行分までしかありません。

var questioner2 = table2[i-2][0];

の部分は、シート1の読み取り対象行に用いている「i」という変数を、シート2にも適用しようとしていることになります。
forループの最中に、シート2の行数を超えてデータにアクセスしようとすると、上記のエラーが発生します。

（i-2 が table2 の行数（table2.length）以上の場合、 table2[i-2] は undefined になります）


あと、直接のエラーの原因ではないですが、

//スプレッドシート2の、A2～B最終行の値をgetValuesで取得
var table2 = sh.getRange(2,1,lastRow2,2).getValues(); 

これだと、table2 は シート1の内容（ただし行数はシート2）になってしまうので、正しくは

var table2 = sh2.getRange(2,1,lastRow2,2).getValues(); 

となります。

＜直し方の指針＞
（下記は、理解しやすいよう質問者さんの考え方に沿って直したものです。
二重ループを使わない／別の関数に切り分ける等、もっと最適化できる余地はありますが、この回答では省略します。他の方が答えてくれるかもしれません）

・上述のように、シート1 と シート2 ではシート自体が異なるので、読み取る処理を分けます。
具体的には、シート2を読み取る部分を j という変数を使って、別のループに切り分けます。

      //シート2の2行目～最終行までを読み込み
      for (var j = 2; j <= lastRow2; j++) {
        //シート2の、A列を定義
        var questioner2 = table2[j - 2][0];

        //シート2の、B列を定義
        var id = table2[j - 2][1];
        if (questioner===questioner2) {
          // IDを設定してforループを抜ける
          sendid = id;
          break;
        }
      }  

このループを元々のforループの中に入れます。


全体としては下記になります。
（後半でIDが見つからなかった場合のエラー処理も追加しています）

function test1() {

  //アクティブなスプレッドシートを取得
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  //シート1を取得
  var sh = ss.getSheetByName("シート1");

  //シート2を取得
  var sh2 = ss.getSheetByName("シート2");

  //シート1の最終行を定義
  var lastRow = sh.getLastRow();
  
  //シート2の最終行を定義
  var lastRow2 = sh2.getLastRow();

  //スプレッドシート1の、O2～R最終行の値をgetValuesで取得
  var table = sh.getRange(2,15,lastRow,4).getValues(); 

  //スプレッドシート2の、A2～B最終行の値をgetValuesで取得
  var table2 = sh2.getRange(2,1,lastRow2,2).getValues();  // sh -> sh2に修正

  //実行日時
  var today = new Date();

  //2行目～14行目までを読み込み
  for (var i = 2; i <= lastRow; i++) {

    //シート1の、O列を定義
    var anser = table[i - 2][0];

    //シート1の、P列を定義
    var send = table[i - 2][1];

    //シート1の、Q列を定義
    var cell = table[i - 2][2];

    //シート1の、R列を定義
    var questioner = table[i - 2][3];

    var sendid = null;  // 追加

    //シート1の、O列が空白ではない、かつ、シート1の、P列が「送信可能」と入力されている、かつ、シート1の、Q列が空白の場合
    if (anser !== "" && send == "送信可能" && cell == "") {
      //シート1のR列の値を含むものを、シート2のA列から探す

      //シート2の2行目～最終行までを読み込み     // ここから追加
      for (var j = 2; j <= lastRow2; j++) {
        //シート2の、A列を定義
        var questioner2 = table2[j - 2][0];

        //シート2の、B列を定義
        var id = table2[j - 2][1];

        // IDが見つかったら sendid を設定してforループを抜ける
        if (questioner === questioner2) {
          sendid = id;
          break;
        }
      }  

      if (sendid != null) {
        //ログでanserの内容を出力（後で、チャットワークに出力に修正する）
        Logger.log(anser);
        Browser.msgBox(anser);
        Browser.msgBox(sendid);
        sh.getRange(i, 17).setValue(new Date());
      } else {
        // 名前が見つからなかった場合のエラー処理
        var message = "名前「" + questioner + "」はシート２に存在しません。"
        Logger.log(message);
        // Browser.msgBox(message);
      }
    }
  }
}

Comments

[tanaka_444]

ありがとうございます！
教えていただいた方法で、実行できました。
エラー処理までありがとうございますm(_ _)m

もし分かれば、以下追加で教えていただけますでしょうか？
今回、シート1のR列の値と完全一致するものを、シート2のA列から探して、
一致すればIDを取得していますが、
部分一致の場合、どのように記載すればよいでしょうか？

以下の、if文の条件式内の書き方を変更すればよいでしょうか？
ネットで、比較演算子で調べてみましたが、「部分一致」する場合の
書き方が載っていなかったため、もし分かれば教えていただきたいです。

よろしくお願いしますm(_ _)m

＝＝＝
// IDが見つかったら sendid を設定してforループを抜ける
if (questioner === questioner2) {
sendid = id;
break;
}
＝＝＝

[退会済みユーザー]

たとえば 「questioner2　の一部に　questioner が含まれているかどうか」を判定する場合は
if (questioner2.indexOf(questioner) !== -1) {
//含まれている場合
} else {
//含まれていない場合
}
となります。

[tanaka_444]

なるほど、ありがとうございます。
ネットで検索しても出てきました。助かりましたm(_ _)m