#実現したいこと
いつもお世話になっております。
https://teratail.com/questions/196217#reply-290859
https://teratail.com/questions/196919
以前質問させていただいた二つの質問でＨＴＭＬのフォームからスプレットシート(シート１)に値を入れるときに値が0の場合シートに格納しないことができて、シート１の指定した箇所の値をコピーして、値が入っている時といない時でそれぞれ別の値を別のシート(シート２)の同じ個所にどちらかが入るような所まで教えていただきました。
今回はそれの続きでシート１に値が入っていない場合、Ｊ列をシート２に格納する所は同じで、シートに値が入っていた場合Ｋ列を代入するところまでは同じなのですが、代入する前にシート２の最終行に行を追加してＪ列の値と日付(Ｌ列)を指定した箇所に追加してからその下の行にＫ列の値と日付を追加したいです
##発生している問題・エラーメッセージ
メソッド insertRowBefore() が見つかりません。
insertRowBefore()の()に適当な値を入れると下のメッセージが表示される
Array を Object[][] に変換できません。
##　概要のソースコード
【コードgs】

  function copylist() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var ss_copyFrom = ss.getSheetByName("シート1"); 
    var copyValue = ss_copyFrom.getRange("L2:L299").getValues();//コピー元のシートの中のセルを指定
    var copyValue2 = ss_copyFrom.getRange("J2:K299").getValues();//コピー元のシートの中のセルを指定
    
    var setData = copyValue2.map(function(row) {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var ss_copyFrom = ss.getSheetByName("シート1");
      var copyValue3 =ss_copyFrom.getRange("J2:J299").getValues();
      var ss_copyTo = SpreadsheetApp.openById('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
      var sheet_copyTo = ss_copyTo.getSheetByName('シート2');  
      if(row[1]!== ""){
        sheet_copyTo.insertRowBefore();
        sheet_copyTo.getRange("I3:I300").setValues(copyValue3);
        sheet_copyTo.getRange("O3:O300").setValues(copyValue3);
      return [row[1] === "" ? row[0] : row[1]];
    　}
   });
   var ss_copyTo = SpreadsheetApp.openById('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx);
   var sheet_copyTo = ss_copyTo.getSheetByName('シート2');//コピー先のスプレットシートの中のシート名 
   sheet_copyTo.getRange("I3:I300").setValues(setData);
   sheet_copyTo.getRange("O3:O300").setValues(setData);  
   sheet_copyTo.getRange("D3:D300").setValues(copyValue);
 }

##　試したこと

if(row[1]!== ""){
        sheet_copyTo.insertRowBefore();
        sheet_copyTo.getRange("I3:I300").setValues(copyValue3);
        sheet_copyTo.getRange("O3:O300").setValues(copyValue3);
return [row[1] === "" ? row[0] : row[1]];
}

row[1]の中身がＫ列であると思ったので!==で空白でなければといった感じのif文を作り、insertRowBeforeで指定した行(Ｋ列)が入る前に新しい行を挿入して、Ｊ列をgetValuesで手に入れてきてから、setValuesで値を入れるような処理を考えたつもりでしたが動かなくて手詰まりになってしまったので、問題点等のご指摘をお願いします。

##　図表


やりたいこととしてはこのような感じにしたいと考えています

##　追記
macaron_xxx様の回答・追記を受けて自分なりの解釈と疑問点を追記させていただきます

function q197326() {
  //コピー元のスプレットシートを取得する
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ss_copyFrom = ss.getSheetByName("シート1"); 
  var fromData = ss_copyFrom .getDataRange().getValues(); // データは全部取る

  //コピー先のスプレットシートを取得する
  var ss_copyTo = SpreadsheetApp.openById('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
  var sheet_copyTo = ss_copyTo.getSheetByName('シート2'); //コピー先のスプレットシートの中のシート名 
  var toData = sheet_copyTo.getDataRange().getValues(); // データは全部取る

この部分は理解できました


var tmpRow = (function(baseArr) {
    var arr = [];
    for (var i = 3; i < 15; i++) {
      arr.push(baseArr[i]);
    }
    return arr;
  })(toData[1]);

tmprowを元の配列としてarrに空の配列を与えて、３列目（差額支払い日）から１５列になるまで繰り返し処理を行ってbaseArr（基準の配列?）に配列を加える
その後returnでarrに返してtoDateに収納する
テンプレートになるという表現がとても分かりやすかったです

var setData = fromData.reduce(function(acc, cur, idx, arr) {
    if(idx !== 0) {
      var row1 = tmpRow.concat();
      row1[0] = cur[11];
      row1[5] = cur[9];
      row1[6] = arr[0][9];
      row1[11] = cur[9];
      acc.push(row1);

acc(アキュムレーター）状態を保持するための引数?　cur(現在値）idx(現在のインデックス)シートの行
arr(配列）上の行で作ったシート２のテンプレの配列
row1=tmpRowの部分に以下の配列を連結する
row1[0] = cur[11];　シート２の差額支払い日とシート１の支払日
row1[5] = cur[9];　シート２の仮払い金とシート１の支払金
row1[6] = arr[0][9];　この部分がいまいち理解できませんでした
row1[11] = cur[9];　シート２の仮払い金とシート1の支払金
acc.push(row1);　最新の配列（コピー先のデータ）に上の行でrow１に加えた情報を追加する

if(cur[10] !== "") {
        var row2 = tmpRow.concat();
        row2[0] = cur[11];
        row2[5] = cur[10];
        row2[6] = arr[0][10];
        row2[11] = cur[10];
        acc.push(row2);
      }
    }
    return acc;
  }, []);

シート１の差額の部分が空白でないならば変数row2を追加する
row２の部分は上の値が違うだけなので省略
return acc; if分で追加した要素を最新の配列コピー先のデータにreturnで戻す　
[]が初期値なのは空の配列だからということであっているでしょうか?

sheet_copyTo.getRange(sheet_copyTo.getLastRow() + 1, 4, setData.length, setData[0].length).setValues(setData);
}

だいたいは理解できたのですが
setData.lengthは貼り付ける行数
setData[0].lengthは貼り付ける列数になるの部分がいまいち理解できていないです・・・

##　わからない部分
１　今回使っているreduce関数と前回使っていただいたmap関数は役割的にかなり近いものがあると調べてみて感じたのですが今回reduce関数を使用した理由等があればお聞きしたいです
２　row1[6] = arr[0][9];　この部分がいまいち理解できませんでした　
この部分はどのような役割を果たしているのでしょうか?
３　etData.lengthは貼り付ける行数
setData[0].lengthは貼り付ける列数になる理由・・・

追記　SpreadsheetApp.openById('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx')の文字列リテラルを閉じました

macaron_xxx

2019/06/27 06:15

たぶん、コードをもらったことのつぎはぎで、全然理解していないまますすめているからそういうことになるんです。前回のコードについて、ちゃんと理解していますか？できていないなら、ベストアンサーをつけずに、ちゃんと聞きましょうよ。

macaron_xxx

2019/06/27 06:16

＞代入する前にシート２の最終行に行を追加してＪ列の値と日付(Ｌ列)を指定した箇所に追加してからその下の行にＫ列の値と日付を追加したいですあとこの日本語が理解できないです。図示してください。

tarataia

2019/06/27 07:50

本当にその通りだと思います前回いただいたものはmap関数でＪ列とＫ列の行をひとつの配列としてまとめて新しい一つの配列にするコードでrow[1]が差額で差額が空白であれば真を返して真だと仮払い金のrow[0]を返して、偽であるならば差額のrow[0]を返してsetValuesで格納していると勝手に解釈して、新しくできたrow[1]にＪ列の配列を与えてシートの行の前にいれるようなinsertRowBeforeを入れればなんとなく動くだろうといったようなメゾットなどの基本的なことがまだいまいちわかっていない感じがします

macaron_xxx

2019/06/27 08:07

理解はほとんどあっています。つまりsetValuesするまで値が入っていないので、mapの中でinsertRowなんてしても意味がないわけです。確認ですが、E～H列、K～N列はコピーする際には空ですか？

tarataia

2019/06/27 08:25

setValuesがひとつひとつ値を格納していると誤解していました・・・シート２のE～H列、K～N列は2行目のこの行は使わないとしていた行に固定の内容があって、コピーなどが終わった後にドラックで引っ張ってくるようなイメージでした

行動規範の内容に同意します

回答1件

ベストアンサー

理解してから使ってね。
（もっといいやり方があるとかないとか）

javascript
1function q197326() {
2  //コピー元のスプレットシートを取得する
3  var ss = SpreadsheetApp.getActiveSpreadsheet();
4  var ss_copyFrom = ss.getSheetByName("シート1"); 
5  var fromData = ss_copyFrom .getDataRange().getValues(); // データは全部取る
6  
7  //コピー先のスプレットシートを取得する
8  var ss_copyTo = SpreadsheetApp.openById('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
9  var sheet_copyTo = ss_copyTo.getSheetByName('シート2'); //コピー先のスプレットシートの中のシート名 
10  var toData = sheet_copyTo.getDataRange().getValues(); // データは全部取る
11  
12  var tmpRow = (function(baseArr) {
13    var arr = [];
14    for (var i = 3; i < 15; i++) {
15      arr.push(baseArr[i]);
16    }
17    return arr;
18  })(toData[1]);
19  
20  var setData = fromData.reduce(function(acc, cur, idx, arr) {
21    if(idx !== 0) {
22      var row1 = tmpRow.concat();
23      row1[0] = cur[11];
24      row1[5] = cur[9];
25      row1[6] = arr[0][9];
26      row1[11] = cur[9];
27      acc.push(row1);
28      
29      if(cur[10] !== "") {
30        var row2 = tmpRow.concat();
31        row2[0] = cur[11];
32        row2[5] = cur[10];
33        row2[6] = arr[0][10];
34        row2[11] = cur[10];
35        acc.push(row2);
36      }
37    }
38    return acc;
39  }, []);
40  
41  sheet_copyTo.getRange(sheet_copyTo.getLastRow() + 1, 4, setData.length, setData[0].length).setValues(setData);
42}

追記 - 解説

javascript
1//コピー元のスプレットシートを取得する
2var ss = SpreadsheetApp.getActiveSpreadsheet();
3var ss_copyFrom = ss.getSheetByName("シート1"); 
4var fromData = ss_copyFrom .getDataRange().getValues(); // データは全部取る
5  
6//コピー先のスプレットシートを取得する
7var ss_copyTo = SpreadsheetApp.openById('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx);
8var sheet_copyTo = ss_copyTo.getSheetByName('シート2'); //コピー先のスプレットシートの中のシート名 
9var toData = sheet_copyTo.getDataRange().getValues(); // データは全部取る

基本的に、スプレッドシートの値はgetDataRange().getValues()で全部取得してしまう。
getValue()する度にAPIが実行され、速度がどんどん低下していくため。
forの中でgetValue()するのはご法度で、エディタでも警告がでる。

また今回は質問の回答にあった

シート２のE～H列、K～N列は2行目のこの行は使わないとしていた行に固定の内容があって、コピーなどが終わった後にドラックで引っ張ってくるようなイメージ

から、コピーする時点でE~H列、K～N列に2行目の値をいれておくためにコピー先のデータも取得している。

javascript
1var tmpRow = (function(baseArr) {
2    var arr = [];
3    for (var i = 3; i < 15; i++) {
4      arr.push(baseArr[i]);
5    }
6    return arr;
7  })(toData[1]);

tmpRowはコピー先の行のテンプレートとして作成。
toData[1]でコピー先の2行目を渡し、D(3)からO(14)列の値が入った配列を用意した。

javascript
1// tmpRowイメージ
2["(D2の値)","(E2の値)","(F2の値)",....,"(O2の値)"]

javascript
1var setData = fromData.reduce(function(acc, cur, idx, arr) {
2    if(idx !== 0) {
3      var row1 = tmpRow.concat();
4      row1[0] = cur[11];
5      row1[5] = cur[9];
6      row1[6] = arr[0][9];
7      row1[11] = cur[9];
8      acc.push(row1);
9
10      if(cur[10] !== "") {
11        var row2 = tmpRow.concat();
12        row2[0] = cur[11];
13        row2[5] = cur[10];
14        row2[6] = arr[0][10];
15        row2[11] = cur[10];
16        acc.push(row2);
17      }
18    }
19    return acc;
20  }, []);

おそらくここが一番の難所であり、肝となる部分。
コピー先に貼り付けるデータを作成している。

Array.prototype.reduce()がわからない場合は、リファレンスで確認。
※リファレンスを読んでもけっこう理解できないかもしれない。色々試してみるのがよいかも。

簡単に解説すると

javascript
1var setData = fromData.reduce(function(acc, cur, idx, arr) {
2  /* 省略 */
3  }, []);

この一番最後にある[]（初期値）にどんどん行を追加していって、データを作っているイメージ
reduceはfromDataの行がループ的にcurで渡ってくる。
accはループでどんどん行が追加された最新のもの（←よくわかんない表現TT）
idxはループのインデックス
arrはfromData自身

その上で中の処理

javascript
1if(idx !== 0) {
2  var row1 = tmpRow.concat();
3  row1[0] = cur[11];
4  row1[5] = cur[9];
5  row1[6] = arr[0][9];
6  row1[11] = cur[9];
7  acc.push(row1);
8
9  if(cur[10] !== "") {
10    var row2 = tmpRow.concat();
11    row2[0] = cur[11];
12    row2[5] = cur[10];
13    row2[6] = arr[0][10];
14    row2[11] = cur[10];
15    acc.push(row2);
16  }
17}
18return acc;

idx===0のときは、コピー元のヘッダ行のため、スルー。
それ以外の場合はrow1をtmpRowからディープコピー。

javascript
1row1 = tmpRow;

としてしまうと、シャローコピーとなり、row1を変更した際にtmpRowも変更されてしまうためNG

javascript
1row1 = Array.from(tmpRow);

とするのがベストなのだが、GASではArray.from()が使えないため、Array.prototype.concat()を使うことで、擬似的にディープコピーしている。

あとはコピーした行に対して、変更をかけるD,I,J,O列に値を代入してaccに行追加。
ここまでがJ列の先払金の処理。（J列は必ず入っている想定）

次にK列が入っている場合のみ、J列と同様のことをおこなう。（値のみK列に変更）

fromData（コピー元シート）の1行ごとにループをまわして、J列→K列（あれば）という形で新しい行列データを作成していく。

最後に

javascript
1sheet_copyTo.getRange(sheet_copyTo.getLastRow() + 1, 4, setData.length, setData[0].length).setValues(setData);

で一気にコピー先シートにデータの貼り付け。

sheet_copy.getLastRow() + 1で最終行の下からスタート
4はD列
setData.lengthは貼り付ける行数
setData[0].lengthは貼り付ける列数
をそれぞれ指す

わからない部分があれば。

追記2 - わからない部分への回答

１　今回使っているreduce関数と前回使っていただいたmap関数は役割的にかなり近いものがあると調べてみて感じたのですが今回reduce関数を使用した理由等があればお聞きしたいです

mapとreduceは全然違います。
そもそもmapは配列を返すのに対して、reduceはなんでも返せます。
（reduceのリファレンス例でも数値を返しています。）
mapはその性質上、元となる配列の長さ（今回の場合は行数）と作成される配列の長さが一致します。
このため今回はmapは使えません。⇒reduceを使っています。

２　row1[6] = arr[0][9];　この部分がいまいち理解できませんでした　

この部分はどのような役割を果たしているのでしょうか?

ここは結構雑にやっちゃってるので、わかりにくいですが、arr（コピー元のデータ）のJ1を代入しています。
つまり、"先払い金","差額"っていうやつですね。

３　etData.lengthは貼り付ける行数

setData[0].lengthは貼り付ける列数になる理由・・・

ここはすんなり理解してもらいたかったですが、
SpreadsheetのgetValuesやsetValuesでは2次元配列を扱います。
[[1,2,3],[1,2,3],[1,2,3]]←こんな感じ

そのため、今回もreduceで2次元配列を作成しています。
（配列にrow（配列）をpushしています。）
なので、setDataのlengthは行数ということです。
またsetDataの各row（setData[0]やsetData[N]も）は列数になります。
かならず各rowの長さは一致させなければいけません。

javascript
1/* OK */ [[1,2,3],[1,2,3],[1,2,3]]
2/* NG */ [[1,2,3],[1,2],[1,2,3],[1,2,3,4]]

投稿2019/06/27 09:03

編集2019/06/28 06:47

macaron_xxx

総合スコア3191

tarataia

2019/06/27 09:19

回答ありがとうございますただすぐには理解できそうにないので今日1日考えてみてまた明日わからないことを質問させていただく形でもよろしいでしょうか?

tarataia

2019/06/28 01:50

詳しい追記解説までありがとうございます。特にArray.prototype.reduce()の部分はリファレンスを見ただけではほとんどわからない感じで解説まで読ませていただいてようやく雰囲気がつかめたといった次第ですここまで丁寧な解説までしていただいて大変恐縮なのですが追記部分に自分の解釈と疑問点を追記させていただいたのでお時間があるときに確認していただけると本当に幸いです

tarataia

2019/06/28 08:21

ご丁寧なご返信ありがとうございます。おかげさまで完璧に理解できたとはとても言えないですが理解できたことも多いので後は積極的に使ってみれる段階まで少しづつ練習してみようと思います。特に二次元配列の部分は理解に乏しかったのでとても分かりやすい説明で助かりました。

行動規範の内容に同意します