同じ文字列を検索して隣のセルの値を返す

Question

GoogleAppScriptで、同じ文字列を検索して、隣のセルにあるメールアドレスをメールの受信者へ設定をするよう、設定を行いたいと考えています。

シート１

シート２

以下の以前の投稿を参考にし、scriptを作成してみたのですが、11行目でエラーが出てしまいます。
参考にしたscrit・・・https://teratail.com/questions/135506

v8で実行しているため、foreachは変更をしています。

var ss = SpreadsheetApp.getActiveSpreadsheet();

  // 一覧を連想配列化
  var sheet1 = ss.getSheetByName('シート2');
  var kvRow = 2;        // 開始する行数を定義（ヘッダは省く） 

  var keyColumn = 1;    // キーの列を定義
  var valueColumn = 2;  // 値の列を定義（ただしキー列＜値列）
  var kvData = sheet1.getRange(kvRow, keyColumn, sheet1.getLastRow(), valueColumn).getValues();
  var kvDic = {};

  kvData.foreach(function(kvData) {
    kvDic[kv[keyColumn - 1]] = kv[valueColumn - 1];
  });

  // 値をセットする
  var sheet2 = ss.getSheetByName('シート1');

  var startRow = 1;     // 開始する行数を定義（ヘッダは省く） 
  var targetColumn = 6; // キーになるコードの列を指定
  var targetData = sheet2.getRange(startRow, targetColumn, sheet2.getLastRow(), 1).getValues();

  var setData = [];

  targetData.foreach(function(targetData) {
    setData.push([kvDic[targetRow[0]]]);
  });
  Logger.log(setData);
  var setColumn = 17; // 挿入したい列数
  sheet2.getRange(startRow, setColumn, sheet2.getLastRow(), 1).setValues(setData);
}

エラーの原因、または、別のscriptなどがありましたらご教授お願いいたします。

Comments

[draq]

どんなエラーが出ているのか、エラーメッセージも書いてください。

[yoyoy]

エラーは「TypeError: Cannot read property 'getRange' of null（行 11、ファイル「メールアドレス」）」と出ます。
シート名を再度、コピーしてscriptに張り付けをしたところ、動くようになりました。

また、確認ありがとうございました。
大変助かりました。
現在scriptが動くこと確認しております。

Answer 1

ぱっと見ただけですが、以下の２箇所がおかしいです。

  //kvData.foreach(function(kvData) {
  kvData.forEach(function(kv) {
    kvDic[kv[keyColumn - 1]] = kv[valueColumn - 1];
  });

  //(中略)

  //targetData.foreach(function(targetData) {
  targetData.forEach(function(targetRow) {
    setData.push([kvDic[targetRow[0]]]);
  });

別の方法として、スプレッドシート関数のARRAYFORMULAとVLOOKUPを使うことでGASのコードなしでも実現できます。

• ARRAYFORMULA

https://support.google.com/docs/answer/3093275?hl=ja

• VLOOKUP

https://support.google.com/docs/answer/3093318?hl=ja

Q3 セルに以下を指定することで Q4 以降のセルも自動的にメールアドレスが設定されます。

=arrayformula(iferror(vlookup($F3:$F, 'シート2'!$A$2:$B,2,false),""))