膨大な情報の更新・追加処理

Question

Google apps scriptで仕事中に使えるスプレッドシートのシステムを作っています。
スクリプトで効率化する味を占めてしまい、新しい内容に手をつけましたが、コードを書き始めたのが先月中旬からでまだまだ理解の乏しい私には難しく、検索しては継ぎ接ぎをして作り直していますが、エラーが連発してしまい完全に作業が止まってしまいました。
思いつく内容も出切ってしまい、素人目では、もはや何が間違っているのかも正直わかりません。

恐れ入りますが、どうか原因や改善点をご教示いただけないでしょうか？
よろしくお願いいたします。

実現したいこと

シートAとシートPがあり、別のシステムからエクスポートした情報をシートPに貼り付け。
シートPに貼り付けた情報は多いときには500件程のデータを確認することになります。シートAに重複するものがあれば上書き、無ければデータ最下行に追加できるものを目指しています。

・シートPに貼り付けるデータはA-CB列まであり、1行目がヘッダー、それ以降は横軸表記で筐体番号や本体名が並んでいます。
データ量としては日によって増減するため、情報量は一定ではありません。（多いときには500件ほどの量になります。）
・シートAに貼り付けをするときには必要情報のみ抽出して貼り付けたいので、列の順番変更を取り入れています。
・シートA / Pの重複確認のキーワードにはそれぞれに含まれる筐体番号（シートA / P共に3列目）と本体名（シートA / P共に4列目）での確認としています。
・シートAには以上で並び替えた必要情報を随時更新、累積していくため、膨大な量になっていく予定なので、今後フィルターでシートAの情報量も調整していく予定です

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

自信の素人考えではシートAとシートPの両方で変数を作りシートPの内容をシートAで走査、該当があれば更新、無ければ追加。ループに戻りシートPを全部確認し終えた時点で終了。
と、したいのですが、for から ifまではできているのだと思うのですが、setValueの部分でThe parameters (number[]) don't match the method signature for SpreadsheetApp.Range.setValues.となってしまいます。

エラーメッセージ
The parameters (number[]) don't match the method signature for SpreadsheetApp.Range.setValues.

### 該当のソースコード

```ここに言語名を入力
function copyPaste() {
  const col_A = "A";
  const col_C = "C";
  const col_M = "M";

  const today = new Date(); today.setHours(0, 0, 0, 0);　　
  const today_GetTime = today.getTime();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  const p_sh = ss.setActiveSheet(ss.getSheetByName('PASTE'), true).activate();
  const a_sh = ss.setActiveSheet(ss.getSheetByName('A CHECK'), true).activate();

  // 'A CHECK'
  const a_Values = a_sh.getRange(col_C + "4:" + col_M + a_sh.getLastRow()).getValues();
  // 'PASTE'
  const p_values = p_sh.getRange(col_A + "1:" + p_sh.getLastRow()).getValues();
  //　'PASTE'　貼り付け日でフィルター
  const p_filtered = p_values.filter(function (element) {
    return typeof element[0] == 'object' && element[0].getTime() == today_GetTime;
  });
  // Col並び替え
  const p_MoveCol = p_filtered.map(elm => [elm[0], elm[7], elm[19], elm[3], elm[4], elm[29], elm[15], elm[17], elm[16], elm[26], elm[35]]);


  for (var p = 0; p < p_MoveCol.length; p++) {  //  'PASTE'
    for (var a = 0; a < a_Values.length; a++) {  // 'ARR CHECK'

      if (p_MoveCol[p][3] === a_Values[a][3] && p_MoveCol[p][4] === a_Values[a][4]) {
        a_sh.getRange([ a + 1 ] , 3, 1, 12).setValues(p_MoveCol[[p]]);


      } else { // 行が見つからなかったら新しくデータを挿入
        sheet_CopyTo.appendRow([[p]]);
       
        return a + 1;


      }
    }
  }
};

試したこと

setValues内を変数に変更してみたり、書き順を変えてみたり、[ ]を増やしてみたりと素人ながらに試しましたが全滅でした。

Comments

[退会済みユーザー]

「 } else { // 行が見つからなかったら新しくデータを挿入
sheet_CopyTo.appendRow([[p]]);」
の「 sheet_CopyTo」はどこで定義されているのでしょうか。

[Banana]

返答が遅くなってしまい申し訳ありません。
qnoirさんの理解いただけたとおり、（sheet_CopyTo = a_sh）であっています。試し書きしすぎて汚くなり、書き直した際の直し漏れです。

Answer 1

修正案

function copyPaste() {
  const col_A = "A";
  const col_C = "C";
  const col_M = "M";

  const today = new Date(); today.setHours(0, 0, 0, 0);
  const today_GetTime = today.getTime();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  const p_sh = ss.getSheetByName('PASTE')
  const a_sh = ss.getSheetByName('A CHECK')

  // 'A CHECK'
  const a_Values = a_sh.getRange(col_C + "4:" + col_M + a_sh.getLastRow()).getValues();
  // 'PASTE'
  const p_values = p_sh.getRange(col_A + "1:" + p_sh.getLastRow()).getValues();
  //　'PASTE'　貼り付け日でフィルター
  const p_filtered = p_values.filter(function (element) {
    return typeof element[0] == 'object' && element[0].getTime() == today_GetTime;
  });
  // Col並び替え
  const p_MoveCol = p_filtered.map(elm => [elm[0], elm[7], elm[19], elm[3], elm[4], elm[29], elm[15], elm[17], elm[16], elm[26], elm[35]]);


  for (var p = 0; p < p_MoveCol.length; p++) {  //  'PASTE'
    var found = false;
    for (var a = 0; a < a_Values.length; a++) {  // 'ARR CHECK'
      if (p_MoveCol[p][3] === a_Values[a][3] && p_MoveCol[p][4] === a_Values[a][4]) {
        a_sh.getRange(a + 4, 3, 1, 11).setValues([p_MoveCol[p]]);
        found = true;
        break;
      }
    }
    // 行が見つからなかったら新しくデータを挿入
    //sheet_CopyTo.appendRow([[p]]);
    if (!found) {
      a_sh.appendRow(['', '', ...p_MoveCol[p]]);
    }
  }
}

---

説明

① setValuesの引数について

The parameters (number[]) don't match the method signature for SpreadsheetApp.Range.setValues.

というエラーメッセージは、

「numberの一次元配列（[]）は、setValuesの引数に使えませんよ」ということを意味しています。

具体的には、setValues(p_MoveCol[[p]]) の「p_MoveCol[[p]]」の部分になります。

setValues() の引数に指定できるのは、原則として2次元配列だけです。

（2次元配列とは、　[[1,2,3],[4,5,6]] のような、二重の入れ子になっている配列のことです）

p_MoveCol[[p]] は、　[[p]] という形になっていますが、p_MoveCol の添え字が二重括弧になっているだけで、実際は一次元の配列です。

ここは　setValues([p_MoveCol[p]]) というように、
p_MoveCol[p] の全体の外側をさらに角括弧で囲ってやる必要があります。

---

② getRange と setValues の関係について

        a_sh.getRange(a + 4, 3, 1, 12).setValues([p_MoveCol[p]]);

ここの getRange() 内の4番目の引数「12」もエラーの原因になっています。

なぜなら、その前の

  const p_MoveCol = p_filtered.map(elm => [elm[0], elm[7], elm[19], elm[3], elm[4], elm[29], elm[15], elm[17], elm[16], elm[26], elm[35]]);

で、p_MoveCol は、11個の要素を持つ配列として作成されているからです。

このままだと、12列の範囲に対して、11列のデータを書き込もうとするため、エラーになります。

getRange() で指定した範囲と、setValues() で書き込もうとする配列の縦・横(行・列）の数は、一致させなければなりません。

したがって、ここの第4引数は、12 ではなく 11 とします。

---

③ for文の流れについて

後半の　for文について：
元々のコードの意図としてはおそらく、
・PASTEシートから１行取り出す。
・取り出した行と、A CHECKシートの各行に重複がないかチェック
→重複する場合は、上書きする
→行が見つからなかった場合（重複がない場合）は、新たに追加する
・次の行を取り出す・・・以下繰り返し

という動作を期待しているものと思います。

しかしながら、実際には
・重複がなかった場合、１行追加した後、return a+1 する
という動作になっています。

returnすると、その関数から抜けます。

このため、①②を直して実行しても、重複がない行が見つかった段階で、1行追加されるだけでスクリプトが終了してしまいます。

したがって、上記修正案では、
・重複する行が見つかった場合は上書きして次のループに行く
・重複する行が見つからなかった場合は、追記して次のループに行く。
・重複行が見つかったかどうかを found という値でフラグ管理することで、二重に追加されないようにする
という動作を仕込んでいます。

また、A CHECKシートはC列からデータが入っているはずですが、元のコードだと、1列目(左端)からデータを追記してしまい、もともとのデータと2列ずれてしまうため、

      a_sh.appendRow(['', '', ...p_MoveCol[p]]);

というように先頭に２つダミーの値を入れて行追加してずれないようにしています。

---

高速化

下記のように、1行ずつsetValuesで書きこむのではなく、配列で加工してまとめて書き込むことで、高速化できます。

function copyPaste2() {
  const col_A = "A";
  const col_C = "C";
  const col_M = "M";

  const today = new Date(); today.setHours(0, 0, 0, 0);
  const today_GetTime = today.getTime();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  const p_sh = ss.getSheetByName('PASTE')
  const a_sh = ss.getSheetByName('A CHECK')

  // 'A CHECK'
  const a_Values = a_sh.getRange(col_C + "4:" + col_M + a_sh.getLastRow()).getValues();
  // 'PASTE'
  const p_values = p_sh.getRange(col_A + "1:" + p_sh.getLastRow()).getValues();
  //　'PASTE'　貼り付け日でフィルター
  const p_filtered = p_values.filter(function (element) {
    return typeof element[0] == 'object' && element[0].getTime() == today_GetTime;
  });
  // Col並び替え
  const p_MoveCol = p_filtered.map(elm => [elm[0], elm[7], elm[19], elm[3], elm[4], elm[29], elm[15], elm[17], elm[16], elm[26], elm[35]]);


  for (var p = 0; p < p_MoveCol.length; p++) {  //  'PASTE'
    var found = false;
    for (var a = 0; a < a_Values.length; a++) {  // 'ARR CHECK'
      if (p_MoveCol[p][3] === a_Values[a][3] && p_MoveCol[p][4] === a_Values[a][4]) {
        a_Values[a] = p_MoveCol[p];
        found = true;
        break;
      }
    }
    // 行が見つからなかったら新しくデータを挿入
    //sheet_CopyTo.appendRow([[p]]);
    if (!found) {
      a_Values.push(p_MoveCol[p]);
    }
  }
  if (a_Values.length < 1) return;
  a_sh.getRange(col_C + "4:" + col_M + (a_Values.length + 4 - 1)).setValues(a_Values);
}

Comments

[Banana]

qnoirさん、早速の返答ありがとうございます！！
早速試してみたところ、処理速度の速さにも驚きを隠せません。
それぞれの内容の説明もしっかりと教えていただき、本当に嬉しくテンション爆上がりました！！
あとは、自分自身で知識を蓄えてまた色々と試してみたいと思います！

それと、もしよろしければ今回教えていただいた、SETVALUEを使わずに変数での値入力のやり方など、応用的な内容を学ぶにはどうしたらいいのか？おすすめの資料や、検索方法のコツなどご教示いただけないでしょうか？

[退会済みユーザー]

配列の基本的な知識は
ttps://www.yukibnb.com/entry/notion_create_integration
setValuesの代わりに配列を使用する件については
ttps://tonari-it.com/gas-array-push-append/
あたりが参考になると思います。

検索方法としては、「GAS 配列 setvalues」や「GAS 配列 setvalues API」というキーワードでGoogle検索すればいろいろ出てくると思います。

上記サイト等に書いてあることを参考にしながら、小さい単位でよいので実際にご自分でコードを書いていろいろ動作を試してみるのが、理解の近道です。