GAS 転記元のデータを条件付きで転記先のデータに反映したい

Question

GASまったくの初心者です。
いろんなサイトを確認していますが、応用できず質問させていただきます。

下記の条件で、転記元から転記先のシートの最終列にコピーできるようにしたいです。

①転記元のE列が「確定」になっていること
②転記元のG列（番号）と転記先のＦ列を比較し、同じ番号があれば無視
⑤同じ番号がない場合、転記先の最終列（A列以降）に転記元のデータB列～I列、M列をコピー
（下記の表の場合黄色い部分）

こちらの回答が似ているかと思いますが、自分のやりたいことにうまく当てはめることができません。
https://teratail.com/questions/m9951yjox94ueq

よろしくお願いいたします。

Comments

[YAmaGNZ]

質問者さんはどこまでのことが出来ていてどこが出来ないのでしょうか？

[退会済みユーザー]

ほんとうに初心者なので変数とは…引数とは…
のレベルでした…
わかりづらく申し訳ございませんでした。
ありがとうございました。

Answer 1

例えば次のようなスクリプトでしょうか。（修正案の再修正をアップしました。）
当初の例で画像シートの行、列、配列のインデックスを数値のまま（いわゆるマジックナンバーのまま）使っていました。
また、転記する列がM列以降となっていたので、N列以降は転記しないように改めました。
行、列を指定する変数名が多くなりました。（→変数の扱いを再修正しました。）

function myFunction() {
  const fr = {          //転記元データ
    rowBegin: 5,        //開始行：5行目
    colBegin: 2,        //開始列：B列
    colEnd: 13,         //最終列：M列（この列以降は不要:N列～）
    colStatus: 5,       //ステータスの列：E列
    colNumber: 7,       //番号の列：G列
    colFillerBegin: 10, //不要な列の開始列：J列
    colFillerEnd: 12,   //不要な列の終了列：L列
  };
  const to = {          //転記先データ
    rowBegin: 4,        //開始列：4行目
    colBegin: 1,        //開始列：A列
    colNumber: 6,       //番号の列：F列
  };
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  fr.sheet = ss.getSheetByName(FROM_NAME);//← FROM_NAMEを実際の  'シート名' で書換
  to.sheet = ss.getSheetByName(TO_NAME);//同上
  //転記元データを二次元配列として取得
  let fromValues = fr.sheet.getRange(fr.rowBegin, fr.colBegin, fr.sheet.getLastRow() - fr.rowBegin + 1, fr.colEnd - fr.colBegin + 1).getValues();
  //確定となっている要素を抽出
  fromValues = fromValues.filter(v => v[fr.colStatus - fr.colBegin] == '確定');
  //転記先の番号の列を一元配列として取得
  const toValues = to.sheet.getRange(to.rowBegin, to.colNumber, to.sheet.getLastRow() - to.rowBegin + 1, 1).getValues().flat();
  //番号が一致している要素を除く
  if (fromValues.length > 0) {
    fromValues = fromValues.filter(f => toValues.every(t => t != f[fr.colNumber - fr.colBegin]));
  }
  if (fromValues.length > 0) {
    //不要な列の要素を除く
    fromValues.forEach(f => f.splice(fr.colFillerBegin - fr.colBegin, fr.colFillerEnd - fr.colFillerBegin + 1));
    //転記先シートの最終行の後に追記
    to.sheet.getRange(to.sheet.getLastRow() + 1, 1, fromValues.length, fromValues[0].length).setValues(fromValues);
  }
}

（当初の提案：N列以降も転記される）

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const shFrom = ss.getSheetByName(FROM_NAME);
  const shTo = ss.getSheetByName(TO_NAME);
  //転記元データを二次元配列として取得
  let  vFrom = shFrom.getRange(5, 2, shFrom.getLastRow() - 5 + 1, shFrom.getLastColumn() - 2 + 1).getValues();
  //確定となっている要素を抽出
  vFrom = vFrom.filter(v => v[3] == '確定');
  //転記先の番号の列を一元配列として取得
  const vTo = shTo.getRange(4, 6, shTo.getLastRow() - 4 + 1, 1).getValues().flat();
  //番号が一致している要素を除く
  if (vFrom.length > 0) {
    vFrom = vFrom.filter(f => vTo.every(t => t != f[5]));
  }
  if (vFrom.length > 0) {
    //不要な列の要素を除く
    vFrom.forEach(f => f.splice(8, 3));
    //転記先シートの最終行の後に追記
    shTo.getRange(shTo.getLastRow() + 1, 1, vFrom.length, vFrom[0].length).setValues(vFrom);
  }
}

Comments

[YellowGreen]

コメント欄の列名表記を何度か修正したので最後のものをご利用ください。
コードは変わっていません。

[退会済みユーザー]

まさに！！N列以降もコピーされてしまうので、試行錯誤しているところでした・・・・
正常に動きました。ほんとにすごいです。尊敬します。
GASのやり方をサイトでみながらここ数日頭が痛くなりながら考えていましたが、
私だけだったら1ヶ月考えてもできなかったでしょう…

本当に助かりました。
本当にありがとうございました。

[退会済みユーザー]

解決後にすみません。
転記先のA4以降の行が空白の場合、エラーが出てしまいます。
おそらく何も入っていないってことはないと思うので大丈夫だとは思うのですが、
もしお時間があればで大丈夫ですのでご回答いただけますと幸いです。

また入れていただいたコメントもとてもわかりやすく、私でもアレンジして使うことができました。
ありがとうございます！！

[YellowGreen]

エラーの内容をそのままコピペできますでしょうか？

[YellowGreen]

Exception: The number of rows in the range must be at least 1.
というエラーでしょうか。
そうであれば、
//転記先の番号の列を一元配列として取得
const toValues = to.sheet.getRange(to.rowBegin, to.colNumber, to.sheet.getLastRow() - to.rowBegin + 1, 1).getValues().flat();

を

//転記先の番号の列を一元配列として取得
let toValues = [];//空の配列を用意
if (to.sheet.getLastRow() >= to.rowBegin) {
toValues = to.sheet.getRange(to.rowBegin, to.colNumber, to.sheet.getLastRow() - to.rowBegin + 1, 1).getValues().flat();
}

に変更してみてください。
let toValues = [];
で最初に空の配列を用意して
転記先にデータがあるときだけtoValuesに値を取得するようにします。
let で配列 toValues を宣言するので、if 文の中のコードは const をつけません。

[YellowGreen]

上の修正をせずに、
転記先toのrowBeginを3に設定するという手もあります。
番号が見出しと一致することはありませんので。

[退会済みユーザー]

ご回答ありがとうございます！！
エラーの内容を記載しておらずすみませんでした。
YellowGreenさんが仰っているのと同じエラーとなります。

＞転記先toのrowBeginを3に設定する
こちらで解決できました！

今回初めてこのような場所に質問させていただきドキドキしておりましたが
親切で丁寧な回答をいただいて本当に嬉しく思います。
ありがとうございました！！

[YellowGreen]

お役に立って良かったです。