SpreadSheetのピボットテーブルをGASで自動生成した際に注意すべきポイント

定期的にSpreadSheetへデータを自動出力して振り返りを行っていますが、特定のステータスになっているデータについて見落としなくチェックできるようにとGASを用いてピボットテーブルでの自動集計も行えるようにしてみました。その際に気になったことなどを挙げてみます。

呼び出し方

特定範囲にテーブルを作成して、集計対象行・列の指定を入れて、集計方法の指定とフィルタ設定を追加します。

function createPivot(startpos) {
  var ss = SpreadsheetApp.getActiveSheet();
  let criteria = SpreadsheetApp.newFilterCriteria()
                             .whenCellNotEmpty()
                             .build();
  var pivotRange = ss.getRange(startpos + 1, 1, 1, 1)
  var pivot = pivotRange.createPivotTable(ss.getRange(1, 3, ss.getLastRow(), 2))
  pivot.addRowGroup(4)
  pivot.addColumnGroup(3)
  pivot.addPivotValue(4, SpreadsheetApp.PivotTableSummarizeFunction.COUNTA)
  pivot.addFilter(4, criteria)
}

SpreadSheetメニューでは列の名前で指定できましたが、GAS経由では左からの列数での指定となります。

列幅の指定について

これは、GASでピボットテーブルを設定する際に最も気になったポイントです。入力された文字列に基づいて自動調整が行われるわけではないので、必要に応じて手作業で広げるか、ピボットテーブル生成後に列幅を調整するコードを追加する必要があります。

注意点

ピボットテーブル上の行と列に対してSpreadSheet上の列番号を指定しますが、コードでの実装ではやや分かり辛い点があります。フィルタも同様です。先にSpreadSheetのメニューから設定して確認することをおすすめします。

既にデータがあるシートの中へ出力する場合、列幅が既存データ分の列幅に影響されて、セルの中身が読み取れなくなることもあります。ピボットテーブルが出力された範囲のセルは結合できません。ソースとなるデータのあるシート上へ出力することで列幅の調整が難しい場合は、新規シート上に出力する方が無難でしょう。

あとがき

殆ど実例を見たことがなかったピボットテーブルですが、項目毎の集計をする際には便利な機能でした。

GASで出力する場合は余り馴染みのない処理を使うために戸惑うかもしれませんが、今回紹介した処理が参考になれば幸いです。