2枚のスプレッドシートで複数の条件を追加し情報を抽出したい。

Question

実現したいこと

2枚のスプレッドシートで複数の条件を追加し情報を抽出したい。

前提

以下の条件で情報を抽出したい場合、どのようなGASを使用すべきでしょうか。
・シート2のAH列とシート3のF列の情報が完全一致
・シート2のC列とシート3のG列の情報が完全一致
・シート3のH列に10月1日から10月31日までの期間を含む年月が記載されている
・シート3のI列に10月1日から10月31日までの期間を含む年月が記載されている
上記が一致した場合
・シート2のG列にシート3のH列を書き込み
・シート2のH列にシート3のI列を書き込み
・シート2のBC列にシート3のA列を書き込み
・同じ条件が複数あった場合は同じ条件の行のすぐ下に行を一行追加して書き込み
・それ以外の場合は空白

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

エラーも表示されず、書き込みもされずでした。

エラーメッセージ

該当のソースコード

function transferDataWithLinelnsertion(){
 const ss = SpreadsheetApp.getActiveSpreadsheet);
 const sheet2 = ss.getsheetByName（シート2）；//シート2の名前を指定
 const sheet3 = ss.getsheetByName（シート3）；//シート3の名前を指定

 //シートデータの取得 
const data2 = sheet2.getDataRange().getValues();
const data3 = sheet3.getDataRange().getValues();
 
//シート2のデータを処理
 for （leti= 1; i< data2.length; i++）｛ //更新する行
const ahVal= data2[i][32];//シート2のAH列
const cVal= data2[i][2];//シート2のC列
let insertCount = 0; //挿入された行数をカウント

 for （let j = 1; j < data3.length; j++）｛// シート3の各行を確認
 const fVal = data3[j][5];//シート3のF列
 const gVal = data3[i][6];//シート3のG列
 const hVal = data3[i][7];//シート3のH列
 const iVal = data3[i][8];//シート3のI列
 const aVal= data3[i][0];//シート3のA列

 //条件を確認 
if (ahVal === fVal && cVal === gVal) { 
//行を追加してデータを書き込む
 sheet2.insertRowAfter（i+insertCount);//現在の行のすぐ下に行を挿入
 sheet2.getRange（i + insertCount + 1， 7）.setValue（hVal）;
 sheet2.getRange（i + insertCount + 1, 8）.setValue（iVal）;
 sheet2.getRange（i + insertCount + 1,55）.setValue（aval）;
 insertCount++；//挿入された行数を増やす
}
}
 //一致しなかった場合は空白を設定
 if (insertCount === 0) { 
sheet2.getRange(i + 1, 7).setValue(");
sheet2.getRange(i + 1, 8) .setValue(");
sheet2.getRange(i + 1, 55) .setValue("); 
}
}
}

試したこと

3パターンぐらい書き出しましたが結果どれも同じく何も書き込みもされませんでした。
大変お手数をおかけいたしますが、どなたかお力をお貸しいただけますと幸いです。
何卒よろしくお願いいたします。

Comments

[YellowGreen]

const fVal = data3[j][5];//シート3のF列
const gVal = data3[i][6];//シート3のG列
const hVal = data3[i][7];//シート3のH列
const iVal = data3[i][8];//シート3のI列
const aVal= data3[i][0];//シート3のA列

上は実際のコードからのコピペでしょうか
それとも転記誤りでしょうか？

[Sunny-o-]

ご返信ありがとうございます。
実際のコードからのコピペです。
GASを初めて触りました。何があってて何が間違っているのかまだわかりません。
ご教授いただけますと幸いです。

[jimbe]

java は関係ありませんのでタグを外して頂いたほうが良いと思います。

[Sunny-o-]

ありがとうございます。
恐らく外れたかと思います。

[jimbe]

>恐らく外れたかと思います。

いえ、外れていないようです・・・ご自身でも確認出来るはずです。
まぁ何か致命的な問題があるわけでもありませんので、お時間ある時にでもまた試して頂ければと思います。

Answer 1

YellowGreenさんにコメントいただいているように
data3[i][...]としている個所は、外側のループのインデックス i を参照しており、
正しくはdata3[j][...]だと思います。
あとAH列はおそらく34列目ですね。

あとはサンプルコードではgetsheetByNameとなっている箇所は、
おそらくgetSheetByNameだったり全角半角が入り混じって居たりと
そのままでは動きそうになかったです。

あと日付の判定の処理も入ってなかったようだったので
追加して処理を記述してみました。
処理もあまり想像がつかなかったので、ログを入れながら動かしてみました。
たぶん動くとは思います。

エラーも表示されず、書き込みもされないとのことだったので、
特に開発中は処理がどこまで正しいのかログで出すか、
デバッグ機能（遅いですが）を使ってみるなどして、
変数に想定通りの値が入っているかを確認しながら進めると間違いないかなと思います！

function transferDataWithLineInsertion(){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet2 = ss.getSheetByName('シート2'); // シート2の名前を指定
  const sheet3 = ss.getSheetByName('シート3'); // シート3の名前を指定

  // シートデータの取得
  const data2 = sheet2.getDataRange().getValues();
  const data3 = sheet3.getDataRange().getValues();

  // 期間を設定 (2024年10月1日から2024年10月31日まで)
  const startDate = new Date('2024-10-01');
  const endDate = new Date('2024-10-31');

  Logger.log(`開始日: ${startDate}, 終了日: ${endDate}`);

  // シート2のデータを処理（下から上へ）
  for (let i = data2.length - 1; i >= 1; i--) { // ヘッダー行を除く
    const ahVal = data2[i][33]; // シート2のAH列 (34列目)
    const cVal = data2[i][2];   // シート2のC列 (3列目)
    let insertCount = 0;        // 挿入された行数をカウント

    Logger.log(`\nシート2の${i + 1}行目を処理中: AH=${ahVal}, C=${cVal}`);

    for (let j = 1; j < data3.length; j++) { // シート3の各行を確認
      const fVal = data3[j][5];  // シート3のF列 (6列目)
      const gVal = data3[j][6];  // シート3のG列 (7列目)
      const hVal = data3[j][7];  // シート3のH列 (8列目)
      const iVal = data3[j][8];  // シート3のI列 (9列目)
      const aVal = data3[j][0];  // シート3のA列 (1列目)

      Logger.log(`  シート3の${j + 1}行目と比較中: F=${fVal}, G=${gVal}, H=${hVal}, I=${iVal}, A=${aVal}`);

      // 条件を確認
      if (String(ahVal) === String(fVal) && String(cVal) === String(gVal)) {
        Logger.log(`    -> 一致しました。`);

        // H列とI列の日付をDate型に変換
        const hDate = new Date(hVal);
        const iDate = new Date(iVal);

        Logger.log(`    変換した日付: H=${hDate}, I=${iDate}`);

        // 日付が期間内にあるか確認
        if (hDate >= startDate && hDate <= endDate && iDate >= startDate && iDate <= endDate) {
          Logger.log(`    日付が期間内です。シート2の${i + insertCount + 1}行目にデータを挿入します。`);

          // 行を追加してデータを書き込む
          sheet2.insertRowAfter(i + insertCount); // 挿入する位置を調整
          sheet2.getRange(i + insertCount + 1, 7).setValue(hVal); // G列（7列目）
          sheet2.getRange(i + insertCount + 1, 8).setValue(iVal); // H列（8列目）
          sheet2.getRange(i + insertCount + 1, 55).setValue(aVal); // BC列（55列目）
          insertCount++; // 挿入された行数を増やす
        } else {
          Logger.log(`    日付が期間外です。`);
        }
      } else {
        Logger.log(`    -> 一致しません。`);
      }
    }

    // 一致が見つからなかった場合は空白を設定
    if (insertCount === 0) {
      Logger.log(`  一致が見つかりませんでした。シート2の${i + 1}行目のG列、H列、BC列を空白に設定します。`);
      sheet2.getRange(i + 1, 7).setValue("");
      sheet2.getRange(i + 1, 8).setValue("");
      sheet2.getRange(i + 1, 55).setValue("");
    }
  }
}

Comments

[Sunny-o-]

夜分遅くに失礼いたします。
明日ご教授いただきました内容にて試してみたいと思います！
またご連絡させてください。

Answer 2

既に回答があるので、
こちらは参考程度にしていただければと思います

// シート2とシート3とで反服処理が繰り返されることを避けて
// シート3の照合用辞書を生成して照合を省力化
// シートへの記入も列ごとに配列から一括して行う
function transferDataWithLinelnsertion() {
  // 初期設定（シート名、日付、列番号）
  const sheetName2 = 'シート2';
  const sheetName3 = 'シート3';
  const termStart = '2024/10/01';
  const termEnd = '2024/10/31';
  const column = {
    a: 1,
    c: 3,
    f: 6,
    g: 7,
    h: 8,
    i: 9,
    ah: 34,
    bc: 55,
  };

  // 期間の日付、配列のインデックスなどの条件を生成
  const startDate = new Date(termStart);
  const endDate = new Date(termEnd);
  const index = {};
  for (const col in column) {
    index[col] = column[col] - 1;
  };

  // 各シートからデータを取得
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet2 = ss.getSheetByName(sheetName2);
  const sheet3 = ss.getSheetByName(sheetName3);
  const data2 = sheet2.getDataRange().getValues();
  const data3 = sheet3.getDataRange().getValues();

  // シート3のデータから照合用辞書を生成
  // 辞書のキー: F列の値 + '_$_' + G列の値
  // 辞書の値（配列）: [{a: A列の値, h:H列の値, i:I列の値}, {a: A列の値, h:H列の値, i:I列の値}, {...}, ...]
  // 同じキーを持つ値が複数あるときは、配列の要素に加える
  const mapData = new Map();
  for (const v of data3) {
    // 期間外のデータは除外
    if (new Date(v[index.h]) < startDate ||
      new Date(v[index.i]) > endDate) {
        continue;
    }
    // 辞書にデータを登録
    const key = v[index.f] + '_$_' + v[index.g];
    const value = {
      a: v[index.a],
      h: v[index.h],
      i: v[index.i],
    };
    if (!mapData.has(key)) {
      mapData.set(key, [value])
    } else {
      const values = mapData.get(key);
      values.push(value);
    }
  }

  // シート2のデータを辞書と照合して、シートに記入する値を配列に蓄積
  const data = {
    bc: [],
    g: [],
    h: [],
  };
  let rowsCount = 0;
  data2.forEach((v, i) => {
    const key = v[index.ah] + '_$_' + v[index.c];
    if (mapData.has(key)) {
      // マッチした場合
      const values = mapData.get(key);
      values.forEach((value, n) => {
        // 同じキーをもつ要素が複数ある時は行を挿入
        if (n > 0) {
          sheet2.insertRowAfter(i + rowsCount + 1);
          rowsCount++;
        }
        const row = i + rowsCount;
        data.bc[row] = [value.a];
        data.g[row] = [value.h];
        data.h[row] = [value.i];
      });
    } else {
      // マッチしない場合
      const row = i + rowsCount;
      data.bc[row] = [''];
      data.g[row] = [''];
      data.h[row] = [''];
    }
  }); 

  // 各列ごとに配列に蓄積した値を記入
  if (data.bc.length > 0) {
    sheet2.getRange(1, column.bc, data.bc.length, 1).setValues(data.bc);
    sheet2.getRange(1, column.g, data.g.length, 1).setValues(data.g);
    sheet2.getRange(1, column.h, data.h.length, 1).setValues(data.h);
  }
}