データの塊をシートに書き込む

Google Spreadsheetでいつも、悩むというか、忘れてしまい無駄に時間を消費して書き方を調べなおしてるものの１つに、データの塊（例えば配列データ）をスプレッドシートに書く時のテクニック。ただ単純に、データの塊をsetValuesで貼り付けりゃいいってもんじゃないので、書き込むに当っての最適なやり方と注意点がある。また、データの塊を書き込むテクニックは大きくわけて２つあり、状況に応じて使い分ける必要性がある。

appendRowでシートの最終行にじゃんじゃん入れる

このメソッドは、１行ずつデータを指定のシートの一番最後のデータ行の次に入れてくれる非常に便利なメソッドです。扱い方もそれほど複雑なわけでもないので、オススメではあるのですが、いかんせん１行ずつなので、あまり大規模なデータの追加には向いていません。リファレンスでは、シート.appendrow([変数１, 変数２,変数3])とか書いてますが、普通こういう無骨なコードは書かずに以下のように書きます。

ソースコード

function insertrow(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var editsheet = sheet.getSheetByName("書き込むシート名");
    //レコード化
    for(var k = 0;k<byoto;k++){
       var tempArray = [];
       uid = uid + 1;
       tempArray.push(uid);
       tempArray.push(hospname);
       tempArray.push(editday);
       
       for(var L = 0;L<23;L++){
         
         tempArray.push(acRange[L][k]);
       }
       editsheet.appendRow(tempArray);
    }
}

書き方のヒント

１行のみであれば、for文は必要ありません。

要するに、[]を用意して、レコードとして入れたい順番でpushをしてそれに押し込む。押し込んだものをappendRowメソッドを使って書き込むという手順です。上記の例では、uid, hospname, editday,acRangeの数値の全部で４列の１レコードのデータが作られて、最後にappendRowで書き込む。そして、次のループで改めてまた、[]を用意しての繰り返し。非常に簡単です。

配列のデータをsetValues()でいっぺんに貼り付ける

appendRowとは異なり、setValuesでの一括貼り付けは、一発で貼り付けるので、スピード的にもメソッドを何回も使う必要がないので、スクリプトの時間的にも非常に有効なのですが、結構面倒くさいメソッドです。何が面倒くさいか？というと、しっかりと貼り付ける元データの横と縦、それに対する貼り付ける相手側のシートの横と縦のそれぞれのRangeがキッチリと一致しないと、動きません。ということなので、setValuesに対する各種の値をしっかりと手に入れてあげないといけません。

下記のコードは、ある二次元配列データ（若しくは、どこぞのシートの範囲のデータ）を自分のシートの特定の範囲内に、常に洗い替えで書き込むルーチンです。１行目はタイトル行なので、A2からということで、rangeの指定も、2,1と始まっているわけです。

ソースコード

function insertArray(){
  var mvsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = mvsheet.getSheetByName("ここに書き込みたいシートの名前を入れる");
・・・・・ 中略 ・・・・・
  //二次元配列を作成する
  var varValues = []
・・・・・ 二次元配列作成と値の格納など ・・・・・
//データの入っている配列データはシートに一気に書き込む
  var lastColumn = varValues[1].length; //カラムの数を取得する
  var lastRow = varValues.length;   //行の数を取得する
  sheet.getRange(2,1,lastRow,lastColumn).setValues(varValues);
}

書き方のヒント

getRangeの謎

通常、レンジ指定する時は、getRange("A2:E15")といった用に、スプレッドシートの番地指定（A1参照型と云います）をするのが当たり前になっているかと思います。まぁ、中には、R1C1方式の方もいるかもしれませんね。しかし、このsetValuesではどういうわけか、これらのRange指定だと受け付けてくれません。範囲指定をする最初のセルと最後のセルのそれぞれのR1C1的な数値を元にRange指定してあげなければいけません。このルールですが、これ自体はとてもシンプルです。

getRange（開始セル○行目, 開始セル○列目, 終了セル○行目, 終了セル○列目）
  • A2から始まるのが今回の開始セル。その数値は、２行目１列目になるわけですから、2,1となるわけです。
  • 終了せるは今回は、E15という事ですから、15行目5列目になります。なので、15, 5となるわけです。
  • これらをgetRangeで表すと、getRange（2, 1, 15, 5）となるわけです。ただ、普通は数値を直接指定するのではなく、書き込み予定の配列の列数と行数を取得しておき、それをgetRangeの縦横の範囲の指定に使うわけです。
  • 配列の列数と行数の取得はコードを見てみて下さい。

追記型で書きたい場合

今回の場合、A2からのスタートで固定されていて取得される度に、以前の数値が上書きで消えるようになっています。しかし、追記して欲しい場合にはこれでは困ります。1は固定のままでもいいと思いますが、2の部分については、変動させる必要があるわけです。通常はここは、追記で書き込むセルの行目を記入するわけですが、手でいれるわけもありません。書き込み対象のスプレッドシートの一番最後の行をgetLastRowなどで取得しておいて、そこに+1でもしておけば、追記で書く場合の開始セルの番地を構築出来ます。

こうすれば、配列データの塊単位で、書き込みたいシートにどんどん追記されるようになります。

データの書き込み後に・・・

例えば、取得したデータを自分のシートにどんどん蓄積した場合。

必ずしも取得したデータのあるべき順番が保たれているとは限りません。その時に、タイムスタンプなどのデータを取得しておいて、それをもってしてソートを掛けておけば最適なのですが、忘れてしまう人も多いでしょう。データ量が多ければ尚の事です。そこで、データの書き込み後にこれらのデータをスプレッドシート上でソートするコードを使いましょう。タイムスタンプやIDなどの情報を用いて、ソートを掛けたり、場合によってはファイル名や名称などを持ってソートを掛けることもあるでしょう。人それぞれ、取得後のデータをきちんと整列したい場合には以下のようなコードを使用します。

sheetname.getRange("A2:C").sort({column:3, ascending:false});

上記コードは、

  1. getRangeで範囲指定をしたら、引き続き、sortコマンドをつなげる。
  2. .sortに対しては、ソートする列番号（C列なら3となる）を指定
  3. sortnい対しては、ソートを何順にするか？（falseならば降順、trueならば昇順）。常に最新をリスト等の上の方にしたいということであるならば、falseを指定するわけです。今回のケースでは、C列にタイムスタンプが入っており、そのC列を基準に降順でソートするというものです。
  4. 手動でフィルタなどを適用したものと異なり、ソートは実際にデータの位置を動かしているので、少々リスキーです。

参考リンク