GAS

【GAS】スプレッドシートのピボットを作る方法

スプレッドシートでデータをちょっとしたデータを集計するのにピボットテーブルは便利です。

ただし、毎月同じ条件でピボットテーブルでレポートを作る場合など継続的に同じことをやるような場合はGASなどで自動化したくなる時があります。

今回は「GASでピボットテーブルを作る方法」をいくつか例を挙げてご紹介します。

基本のパターン

データを読み込んでピボットテーブルを作成するまでのコードです。

「データ」シートには表形式のマスタデータが記載されています。

function main() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  const pivotSheet = spreadsheet.getSheetByName("ピボット")
  const dataSheet = spreadsheet.getSheetByName("マスタ")

  const data = dataSheet.getDataRange()
  const pivot = pivotSheet.getRange(1, 1).createPivotTable(data)
}

このコードを実行すると次のように空の状態のピボットテーブルが作成されます。

この部分で、指定のシート（このコードでは「ピボット」シート）のA1セルにピボットテーブルを作成しています。

const pivot = pivotSheet.getRange(1, 1).createPivotTable(data)

行と列の追加

ピボットテーブルを作成できたので、次は集計単位の行と値を追加していきましょう。

function main() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  const pivotSheet = spreadsheet.getSheetByName("ピボット")
  const dataSheet = spreadsheet.getSheetByName("マスタ")

  const data = dataSheet.getDataRange()
  const pivot = pivotSheet.getRange(1, 1).createPivotTable(data)

  pivot.addRowGroup(3)
  let salary = pivot.addPivotValue(6, SpreadsheetApp.PivotTableSummarizeFunction.SUM)
  salary.setDisplayName("月給")
}

このコードを実行するとピボットテーブルが次のように変化していると思います。

追加した部分は以下の3行です。

pivot.addRowGroup(3)
let salary = pivot.addPivotValue(6, SpreadsheetApp.PivotTableSummarizeFunction.SUM)
salary.setDisplayName("月給")

addRowGroup(3)は元となるシートの左から数えた列数を引数に指定しています。今回は所属1なのでC列3列目を指定します。

また、月給の合計を値として集計したい場合は、「SpreadsheetApp.PivotTableSummarizeFunction.SUM」を指定します。

他の集計方法は次の通りです。

参考

https://developers.google.com/apps-script/reference/spreadsheet/pivot-table-summarize-function

ここで大切なのが、ピボットの元データとして取得したRangeの列数ではなくて、シートの絶対数であるという点です。以下のような列を一つずらした指定のRangeの指定であっても「所属1」の指定は3のままです。

const data = dataSheet.getRange(1, 2, dataSheet.getLastRow(), dataSheet.getLastColumn() - 1)
const pivot = pivotSheet.getRange(1, 1).createPivotTable(data)

参考

https://developers.google.com/apps-script/reference/spreadsheet/pivot-table#addrowgroupsourcedatacolumn

フィルタの設定

次はピボットテーブルにフィルタを追加しましょう。

function main() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  const pivotSheet = spreadsheet.getSheetByName("ピボット")
  const dataSheet = spreadsheet.getSheetByName("マスタ")

  const data = dataSheet.getRange(1, 2, dataSheet.getLastRow(), dataSheet.getLastColumn() - 1)
  const pivot = pivotSheet.getRange(1, 1).createPivotTable(data)

  pivot.addRowGroup(3)
  let salary = pivot.addPivotValue(6, SpreadsheetApp.PivotTableSummarizeFunction.SUM)
  salary.setDisplayName("月給")

  let criteria = SpreadsheetApp.newFilterCriteria().setVisibleValues(["営業本部", "経営本部"]).build()
  pivot.addFilter(3, criteria)
}

実行するとピボットテーブルの所属1がフィルタされて行が減っているのがわかると思います。

今回追加したのは次の2行です。

let criteria = SpreadsheetApp.newFilterCriteria().setVisibleValues(["営業本部", "経営本部"]).build()
pivot.addFilter(3, criteria)

フィルタを設定するにはFilterCriteriaクラスを生成する必要があります。フィルタの設定情報を保持したこのクラスを生成するためのBuilderクラスも用意されているので、今回はそちらを利用しています。

SpreadsheetApp.newFilterCriteria()でFilterCriteriaBuilderを生成して、setVisibleValuesで絞り込む対象の値を指定します。ほかにも細かいフィルタの設定方法がありますので、以下のドキュメントを参考にしてみてください。

参考

https://developers.google.com/apps-script/reference/spreadsheet/filter-criteria-builder

addFilterで生成したFilterCriteriaを設定します。第一引数の3は元データの左から数えた列数を差しています。ピボットテーブルの3列目ではないのでご注意ください。

まとめ

今回は「GASでピボットテーブルを作る方法」をご紹介しました。主にフィルタの設定に多少の癖があり注意が必要ですが使いこなせれば素早くピボットテーブルが作成できるようになるはずです。

ぜひ、GASを使って定型的なピボットテーブル分析を効率化していきましょう。