質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

88.57%

コピーしたシートに行を加えてから内容を入れたい

解決済

回答 1

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 682

tarataia

score 17

実現したいこと

いつもお世話になっております。
https://teratail.com/questions/196217#reply-290859
https://teratail.com/questions/196919
以前質問させていただいた二つの質問でHTMLのフォームからスプレットシート(シート1)に値を入れるときに値が0の場合シートに格納しないことができて、シート1の指定した箇所の値をコピーして、値が入っている時といない時でそれぞれ別の値を別のシート(シート2)の同じ個所にどちらかが入るような所まで教えていただきました。
今回はそれの続きでシート1に値が入っていない場合、J列をシート2に格納する所は同じで、シートに値が入っていた場合K列を代入するところまでは同じなのですが、代入する前にシート2の最終行に行を追加してJ列の値と日付(L列)を指定した箇所に追加してからその下の行にK列の値と日付を追加したいです

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

メソッド 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]の中身がK列であると思ったので!==で空白でなければといった感じのif文を作り、insertRowBeforeで指定した行(K列)が入る前に新しい行を挿入して、J列を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に空の配列を与えて、3列目(差額支払い日)から15列になるまで繰り返し処理を行って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(配列)上の行で作ったシート2のテンプレの配列
row1=tmpRowの部分に以下の配列を連結する
row1[0] = cur[11]; シート2の差額支払い日とシート1の支払日
row1[5] = cur[9]; シート2の仮払い金とシート1の支払金
row1[6] = arr[0][9]; この部分がいまいち理解できませんでした
row1[11] = cur[9]; シート2の仮払い金とシート1の支払金
acc.push(row1); 最新の配列(コピー先のデータ)に上の行でrow1に加えた情報を追加する

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;
  }, []);


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

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


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

 わからない部分

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

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

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • tarataia

    2019/06/27 16:50

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

    キャンセル

  • macaron_xxx

    2019/06/27 17:07

    理解はほとんどあっています。
    つまりsetValuesするまで値が入っていないので、mapの中でinsertRowなんてしても意味がないわけです。

    確認ですが、E~H列、K~N列はコピーする際には空ですか?

    キャンセル

  • tarataia

    2019/06/27 17:25

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

    キャンセル

回答 1

checkベストアンサー

+2

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

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]);

  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);

      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;
  }, []);

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

追記 - 解説

//コピー元のスプレットシートを取得する
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(); // データは全部取る


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

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

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

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


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


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

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

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);

      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;
  }, []);


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

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

簡単に解説すると

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


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

その上で中の処理

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);

  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;

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

row1 = tmpRow;


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

row1 = Array.from(tmpRow);


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

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

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

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


最後に

sheet_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 - わからない部分への回答

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

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

2 row1[6] = arr[0][9]; この部分がいまいち理解できませんでした 
この部分はどのような役割を果たしているのでしょうか?

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

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

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

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

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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/06/27 18:19

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

    キャンセル

  • 2019/06/28 10:50

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

    キャンセル

  • 2019/06/28 17:21

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 88.57%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る