Google Spreadsheetでいつも、悩むというか、忘れてしまい無駄に時間を消費して書き方を調べなおしてるものの1つに、データの塊(例えば配列データ)をスプレッドシートに書く時のテクニック。ただ単純に、データの塊をsetValuesで貼り付けりゃいいってもんじゃないので、書き込むに当っての最適なやり方と注意点がある。また、データの塊を書き込むテクニックは大きくわけて2つあり、状況に応じて使い分ける必要性がある。
このメソッドは、1行ずつデータを指定のシートの一番最後のデータ行の次に入れてくれる非常に便利なメソッドです。扱い方もそれほど複雑なわけでもないので、オススメではあるのですが、いかんせん1行ずつなので、あまり大規模なデータの追加には向いていません。リファレンスでは、シート.appendrow([変数1, 変数2,変数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);
}
}
1行のみであれば、for文は必要ありません。
要するに、[]を用意して、レコードとして入れたい順番でpushをしてそれに押し込む。押し込んだものをappendRowメソッドを使って書き込むという手順です。上記の例では、uid, hospname, editday,acRangeの数値の全部で4列の1レコードのデータが作られて、最後にappendRowで書き込む。そして、次のループで改めてまた、[]を用意しての繰り返し。非常に簡単です。
appendRowとは異なり、setValuesでの一括貼り付けは、一発で貼り付けるので、スピード的にもメソッドを何回も使う必要がないので、スクリプトの時間的にも非常に有効なのですが、結構面倒くさいメソッドです。何が面倒くさいか?というと、しっかりと貼り付ける元データの横と縦、それに対する貼り付ける相手側のシートの横と縦のそれぞれのRangeがキッチリと一致しないと、動きません。ということなので、setValuesに対する各種の値をしっかりと手に入れてあげないといけません。
下記のコードは、ある二次元配列データ(若しくは、どこぞのシートの範囲のデータ)を自分のシートの特定の範囲内に、常に洗い替えで書き込むルーチンです。1行目はタイトル行なので、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からのスタートで固定されていて取得される度に、以前の数値が上書きで消えるようになっています。しかし、追記して欲しい場合にはこれでは困ります。1は固定のままでもいいと思いますが、2の部分については、変動させる必要があるわけです。通常はここは、追記で書き込むセルの行目を記入するわけですが、手でいれるわけもありません。書き込み対象のスプレッドシートの一番最後の行をgetLastRowなどで取得しておいて、そこに+1でもしておけば、追記で書く場合の開始セルの番地を構築出来ます。
こうすれば、配列データの塊単位で、書き込みたいシートにどんどん追記されるようになります。
例えば、取得したデータを自分のシートにどんどん蓄積した場合。
必ずしも取得したデータのあるべき順番が保たれているとは限りません。その時に、タイムスタンプなどのデータを取得しておいて、それをもってしてソートを掛けておけば最適なのですが、忘れてしまう人も多いでしょう。データ量が多ければ尚の事です。そこで、データの書き込み後にこれらのデータをスプレッドシート上でソートするコードを使いましょう。タイムスタンプやIDなどの情報を用いて、ソートを掛けたり、場合によってはファイル名や名称などを持ってソートを掛けることもあるでしょう。人それぞれ、取得後のデータをきちんと整列したい場合には以下のようなコードを使用します。
sheetname.getRange("A2:C").sort({column:3, ascending:false});
上記コードは、