スプレッドシートに配列の内容を一括で出力する

目的

VBAからGASへの移行作業をしていて、スプレッドシートに一括でデータを書き込みたい場面があり、VBAのノリでチャチャっとできるだろうと思ってやってみると意外とハマったので備忘録として残しました。配列操作の勉強にもなりました。

結論

GASにおいてシートに配列を貼り付ける際の注意は以下の通りです。

• 貼り付け対象の配列は必ず二次元配列にする
  GASの場合、VBAと違い一次元配列だとエラーが発生します。
• 配列の行列サイズと貼り付け範囲の行/列数を一致させる
  VBAで言うところのResizeのイメージです。

コード

outputArrayToSheet.js

// 3行４列のシート範囲に一括貼り付け
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('シート1');

function outputArrayToSheet() {
    sheet.clearContents();      // シートクリア
    var arrTest = [];           // 配列初期化
  // 貼り付け対象の配列作成
    for(var i=1;i<=3;i++){      // ①行ループ(３行)
        var arrTestInt = [];    // ②列要素初期化
        for(var j=1;j<=4;j++){  // ③列ループ(４列)
            arrTestInt.push('test'+i+j);  // ④列要素作成
        }
        arrTest.push(arrTestInt); // ⑤列要素を行成分として格納(二次元配列を作成)
    }
  // ⑥シートに出力 
    var rowSize = arrTest.length    //  貼り付け行サイズ
    var colSize = arrTest[0].length //  貼り付け列サイズ
    sheet.getRange(1,1,rowSize,colSize).setValues(arrTest);
}

①〜⑤で貼り付け対象配列の作成、⑥でシートへの貼り付けを行なっています。
①〜⑤の部分は前準備のつもりで軽い気持ちで作ろうとしたのですが、 意外と苦戦した 勉強になったので残しておきました。初期化の時点では一次元配列ですが、配列の各要素に配列をpushすることで二次元配列にしています。
⑥は説明のため、変数としてrowSize, colSizeを宣言しています。引数に直接arrTest.lengthなどと記述しても構いません。VBAがわかる方なら以下の記述と同義です。

' 配列をシートに貼り付ける(Excel VBA)
Sheets("シート１").Cells(1, 1).Resize(rowSize, colSize).Value = arrTest

実行結果

実行結果は以下の通りです。①の行ループ毎に、二次元配列として要素が順次追加されています。test○△の○が行番号、△が列番号を表し、スプレッドシートにもイメージ通り出力されています。

// 1行目作成
[ [ 'test11', 'test12', 'test13', 'test14' ] ]
// 2行目追加
[ [ 'test11', 'test12', 'test13', 'test14' ],
  [ 'test21', 'test22', 'test23', 'test24' ] ] ←追加
// 3行目追加
[ [ 'test11', 'test12', 'test13', 'test14' ],
  [ 'test21', 'test22', 'test23', 'test24' ],
  [ 'test31', 'test32', 'test33', 'test34' ] ] ←追加

貼り付け結果

補足

逆に1行n列のシートデータを配列に格納するとどうなるのか、下記コードで検証してみました。

confirmRange.js

function confirmRange(){
  var rngData = sheet.getRange(1,1,1,4).getValues();
  console.log(rngData);
}

// 出力結果
// [ [ 'test11', 'test12', 'test13', 'test14' ] ]

A1:D1のデータをgetValuesで取得し、結果を出力したものですが、たとえ1行でも二次元配列として返ることがわかります。
実はVBAでも同じ結果になりますが、ExcelVBAでは一次元配列は例外的に1行n列で出力可能です。GASは汎用性が高いからなのか、この辺りは例外なく全て二次元配列で扱っているんでしょうかね？

あとがき

VBAでは一次元配列でもシートに貼り付けは可能ですが、GASで同じノリだとエラーが発生します。1行n列でも必ず二次元配列にします。
また、二次元配列を順々に生成していくのは意外とハマりました。当初はコード内に配列の中身を直書きするつもりでしたが、何か芸がないと思いループを使ってみると予想外の動きをして意外と難航しました。気がつけば(配列の各要素に配列をpushする)あぁそうだよねという方法ですね。

リンク

GASにおける二次元配列について、導入から丁寧に説明されているサイトを紹介します。
【超初心者向け！】GASの二次元配列をやさしく図入り解説！