GASでピボットテーブルを作る

ピボットテーブルを操作するためのメモ。
こちらの記事を参考にさせていただきました。

集計元データ

こんな感じのデータを元にピボットテーブルを作る。

シート名

データが入ってるシートの名前 → data
ピボットテーブルを作るシートの名前 → pivot

コードの全体像

sample.gs

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const dataSheet = ss.getSheetByName('data');
  const pivotSheet = ss.getSheetByName('pivot');
  const dataSource = dataSheet.getDataRange();
  // ピボットテーブルの作成
  const pivotTable = pivotSheet.getRange('A1').createPivotTable(dataSource);

  // 行の設定
  let itemGroup = pivotTable.addRowGroup(1);
  itemGroup.showTotals(true).sortAscending;
  itemGroup.showRepeatedLabels();
  itemGroup = pivotTable.addRowGroup(2);
  itemGroup.showTotals(false).sortAscending;

  // 列の設定
  itemGroup = pivotTable.addColumnGroup(3);

  // 値の設定
  let pivotValue = pivotTable.addPivotValue(4, SpreadsheetApp.PivotTableSummarizeFunction.SUM);
  pivotValue.setDisplayName('売上金額');
  pivotValue = pivotTable.addPivotValue(1, SpreadsheetApp.PivotTableSummarizeFunction.COUNTA);
  pivotValue.setDisplayName('売上件数');
}

1.ピボットテーブルを作成する

該当コード部分

sample.gs

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const dataSheet = ss.getSheetByName('data');
  const pivotSheet = ss.getSheetByName('pivot');
  const dataSource = dataSheet.getDataRange();
  // ピボットテーブルの作成
  const pivotTable = pivotSheet.getRange('A1').createPivotTable(dataSource);
}

こんな感じで作成される。

2.行の設定

該当コード部分

sample.gs

  // 行の設定
  let itemGroup = pivotTable.addRowGroup(1); //addRowGroupの引数はdataシートの年が格納されている列数。
  itemGroup.showTotals(true).sortAscending;
  itemGroup.showRepeatedLabels();
  itemGroup = pivotTable.addRowGroup(2); //addRowGroupの引数はdataシートの月が格納されている列数。
  itemGroup.showTotals(false).sortAscending;

こんな感じで設定される。

年の行は showRepeatedLabels() で「行ラベルを繰り返す」にチェックをいれている。
月の行は showTotals() の引数をfalseにすることで統計を非表示にしている。

3.列の設定

該当コード部分

sample.gs

  // 列の設定
  itemGroup = pivotTable.addColumnGroup(3); //addRowGroupの引数はdataシートの拠点が格納されている列数。
}

4.値の設定

該当のコード部分

sample.gs

  // 値の設定
  let pivotValue = pivotTable.addPivotValue(4, SpreadsheetApp.PivotTableSummarizeFunction.SUM);
  pivotValue.setDisplayName('売上金額');
  pivotValue = pivotTable.addPivotValue(1, SpreadsheetApp.PivotTableSummarizeFunction.COUNTA);
  pivotValue.setDisplayName('売上件数');
}

最初から最後まで実行するとこんな感じで出来上がる

addPivotValue() の第1引数はdataシートの列数を指定して、第2引数はどういう集計方法するかをしている。
集計方法は公式サイトに一覧があります。
このコードでは売上金額に関してはSUMで、売上件数にCOUNTAで集計。