初心者必見！GASでのピボットテーブル作成と更新方法

GASでのピボットテーブル作成の基本

まずは、GAS（Google Apps Script）を使ってどのようにピボットテーブルを作成するのか確認しよう。GASはGoogleスプレッドシートとシームレスに連携できるので、とても便利だ。

スプレッドシートを取得する

ピボットテーブルを作成するためには、まずGoogleスプレッドシートを取得する必要がある。

function createPivotTable() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
}

このコードでは、SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()を使って現在アクティブなシートを取得している。

ピボットテーブルの範囲を設定する

次に、ピボットテーブルをどこに作成するかを指定する。

function createPivotTable() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const range = sheet.getRange('A1:H10'); // データの範囲
}

ここでは、A1からH10までの範囲を指定している。この範囲に含まれるデータを元にピボットテーブルを作成する。

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

ピボットテーブルを作成するために、次のようにコードを追加する。

function createPivotTable() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const range = sheet.getRange('A1:H10');
  const pivotRange = sheet.getRange('J1'); // ピボットテーブルの開始位置

  const pivotTable = pivotRange.createPivotTable(range);
  pivotTable.addRowGroup(1); // 2列目を行グループに追加
  pivotTable.addColumnGroup(2); // 3列目を列グループに追加
  pivotTable.addPivotValue(3, SpreadsheetApp.PivotTableSummarizeFunction.SUM); // 4列目のデータを合計
}

– pivotRange.createPivotTable(range): 指定したデータ範囲からピボットテーブルを作成
– addRowGroup(columnIndex): 指定した列を行グループに追加
– addColumnGroup(columnIndex): 指定した列を列グループに追加
– addPivotValue(columnIndex, function): 指定した列のデータを関数を用いて集計

GASでのピボットテーブル更新方法

作成したピボットテーブルを更新するための方法も知っておこう。特にデータが変更されたときに役立つ。

範囲を再設定して更新

まずはデータの範囲を再設定してから更新する。

function updatePivotTable() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const range = sheet.getRange('A1:H15'); // 新しいデータ範囲
  const pivotRange = sheet.getRange('J1');

  const pivotTable = pivotRange.getPivotTable();
  pivotTable.setDataRange(range); // 新しい範囲にセット
}

ここでは、範囲をA1からH15に変更している。pivotTable.setDataRange(range)を使って新しい範囲を設定できる。

練習問題

• データ範囲をA1からF20に変更し、G1にピボットテーブルを作成して、3列目を行グループ、4列目を列グループ、5列目のデータを平均で集計するスクリプトを書いてください。

解答・解説

function practicePivotTable() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const range = sheet.getRange('A1:F20'); // 新しいデータ範囲
  const pivotRange = sheet.getRange('G1'); // ピボットテーブルを開始する位置

  const pivotTable = pivotRange.createPivotTable(range);
  pivotTable.addRowGroup(2); // 3列目を行グループに追加
  pivotTable.addColumnGroup(3); // 4列目を列グループに追加
  pivotTable.addPivotValue(4, SpreadsheetApp.PivotTableSummarizeFunction.AVERAGE); // 5列目を平均で集計
}

ここでは、getRange(‘A1:F20’)で新しい範囲を指定し、createPivotTable()でピボットテーブルを作成。3列目と4列目をそれぞれ行グループと列グループに設定し、5列目はAVERAGE関数で集計している。