Google Sheets API v4（Java）を使用して、
スプレットシートのセルにピボットテーブルを設定する方法
についてご紹介します。
ピボットテーブルとは
セル内のデータを見たい項目を軸に分析・集計できる表を作成する機能です。
ピボットテーブルはPivotTableクラスで指定できますが、
ここでは指定可能な内容のみを記事にしています。
実際に使用したい場合は下記の記事と合わせてご覧ください。
⧉[Google Sheets API] セルの内容(値、メモ、リッチテキスト、表示形式(書式)、ピボットテーブル、入力規則)を設定する
その他のGoogle Sheets APIは、下記の記事を参照ください。
⧉[Google Sheets API] JavaでスプレットシートのメニューとAPIを関連づけてみた
| No | 目次 | ||
|---|---|---|---|
| 1 | リストの方向の設定 | ||
| 2 | 範囲の設定 | ||
| 3 | 行、列の設定 | ||
| 1 | グループルールの設定 | ||
| 1 | 1 | DateTimeルール | |
| 1 | 2 | ヒストグラムルール | |
| 1 | 3 | マニュアルルール | |
| 2 | グループバケットの設定 | ||
| 3 | メタデータの設定 | ||
| 4 | 値の設定 | ||
| 5 | フィルタの設定 |
サンプル コード
ピボットテーブルに設定可能な内容を示します。
各々の設定内容の詳細は、「設定できる内容」を参照ください。
public PivotTable getPivotTable() {
PivotTable pivot = new PivotTable();
//値を水平方向 (列として) or 垂直方向 (行として)
pivot.setValueLayout("HORIZONTAL");
//ピボット テーブルがデータを読み取る範囲
pivot.setSource(getSource());
//ピボット テーブル内の各行をグループ化
pivot.setRows(getPivotGroup());
//ピボット テーブル内の各列をグループ化
pivot.setColumns(getPivotGroup());
//ピボット テーブルに含める値のリスト
pivot.setValues(getPivotValue());
//ソース列オフセットごとのフィルターのオプションのマッピング
pivot.setCriteria(getPivotFilterCriteria());
return pivot;
}
1. リストの方向の設定
値を水平方向 (列として) か、垂直方向 (行として) でリストするかを設定できます。
| layout | 方向 |
|---|---|
| HORIZONTAL | 水平方向 |
| VERTICAL | 垂直方向 |
2. 範囲の設定
ピボットテーブルで使用するソースデータのセルの範囲を設定できます。
private GridRange getSource() {
GridRange range = new GridRange();
range.setSheetId(1); //シートID
range.setStartRowIndex(0); //開始行(0スタート)
range.setEndRowIndex(6); //終了行(1スタート) 省略可能
range.setStartColumnIndex(1); //開始列(0スタート)
range.setEndColumnIndex(4); //終了列(1スタート) 省略可能
return range;
}
3. 行、列の設定
行、列の指定を設定できます。
setSortOrder("")で、グループ内の値を並べ替える順序が設定できます。
| order | 並び順 |
|---|---|
| ASCENDING | 昇順 |
| DESCENDING | 降順 |
private List<PivotGroup> getPivotGroup(){
List<PivotGroup> groups = new ArrayList<>();
PivotGroup group = new PivotGroup();
//この行/列グループに適用するグループ ルール
group.setGroupRule(getPivotGroupRule());
//カスタマイズ可能な行/列グループに使用するラベル
group.setLabel("");
//このピボット グループの見出しを繰り返す必要があるか？
group.setRepeatHeadings(true);
//ピボット テーブルにこのグループの合計を含める必要があるか？
group.setShowTotals(true);
//このグループ内の値を並べ替える順序
group.setSortOrder("ASCENDING");
//このグループ化の基になるソース範囲の列オフセット
group.setSourceColumnOffset(1);
//並べ替えの基準となる反対側のピボット グループのバケット
group.setValueBucket(getPivotGroupSortValueBucket());
//グループ化内の値に関するメタデータ
group.setValueMetadata(getPivotGroupValueMetadata());
groups.add(group);
return groups;
}
3.1. グループルールの設定
行または列グループに適用するグループ ルールを設定します。
以下の3つのルールを設定します。
・DateTimeルール
・ヒストグラムルール
・マニュアルルール
private PivotGroupRule getPivotGroupRule() {
PivotGroupRule groupRule = new PivotGroupRule();
//DateTimeルール
groupRule.setDateTimeRule(getDateTimeRule());
//ヒストグラムルール
groupRule.setHistogramRule(getHistogramRule());
//マニュアルルール
groupRule.setManualRule(getManualRule());
return groupRule;
}
3.1.1. DateTimeルール
DateTimeルールを設定します。
日付または時刻値の選択した部分に基づいて、ソース データ列の日付/時刻値を
バケットに整理できます。
setType("")で適用する日時グループ化のタイプを設定できます。
| type | グループ化タイプ |
|---|---|
| SECOND | 日付を0〜59までの秒単位でグループ化 |
| MINUTE | 日付を0〜59までの分ごとにグループ化 |
| HOUR | 24時間制を使用し0〜23までの時間ごとに日付をグループ化 |
| HOUR_MINUTE | 24時間制を使用し日付を時間と分でグループ化 |
| HOUR_MINUTE_AMPM | 12時間制を使用し日付を時間と分でグループ化 |
| DAY_OF_WEEK | 日付を曜日ごとにグループ化 |
| DAY_OF_YEAR | 日付を1〜366 までの日付ごとにグループ化 |
| DAY_OF_MONTH | 日付を1〜31 までの日付ごとにグループ化 |
| DAY_MONTH | 日付を日と月ごとにグループ化 |
| MONTH | 日付を月ごとにグループ化 |
| QUARTER | 日付を四半期ごとにグループ化 |
| YEAR | 日付を年ごとにグループ化 |
| YEAR_MONTH | 日付を年と月ごとにグループ |
| YEAR_QUARTER | 日付を年と四半期ごとにグループ化 |
| YEAR_MONTH_DAY | 日付を年、月、日ごとにグループ化 |
private DateTimeRule getDateTimeRule() {
DateTimeRule dateTimeRule = new DateTimeRule();
//グループ化のタイプ
dateTimeRule.setType("HOUR");
return dateTimeRule;
}
3.1.2. ヒストグラムルール
ヒストグラムルールを設定します。
ソース データ列の数値を一定サイズのバケットに編成できます。
private HistogramRule getHistogramRule() {
HistogramRule histogramRule = new HistogramRule();
//作成されるバケットのサイズ
histogramRule.setInterval(0d);
//アイテムを一定サイズのバケットに入れる最大値(オプション)
histogramRule.setEnd(0d);
//アイテムを一定サイズのバケットに入れる最小値(オプション)
histogramRule.setStart(0d);
return histogramRule;
}
3.1.3. マニュアルルール
マニュアルルールを設定します。
ソース データ列の値を、選択した名前のバケットに手動で整理できます。
private ManualRule getManualRule() {
ManualRule manualRule = new ManualRule();
List<ManualRuleGroup> manualRuleGroups = new ArrayList<>();
ManualRuleGroup manualRuleGroup = new ManualRuleGroup();
//グループ名
manualRuleGroup.setGroupName(new ExtendedValue().setStringValue("name"));
//このグループに配置する必要があるソース データ内の項目
manualRuleGroup.setItems(Arrays.asList(new ExtendedValue().setNumberValue(1d));
manualRuleGroups.add(manualRuleGroup);
//グループ名と、各グループ名にマップされるソース データの対応する項目のリスト
manualRule.setGroups(manualRuleGroups);
return manualRule;
}
3.2. グループバケットの設定
並べ替えの基準となる反対側のピボット グループのバケットを設定します。
private PivotGroupSortValueBucket getPivotGroupSortValueBucket() {
PivotGroupSortValueBucket valueBucket = new PivotGroupSortValueBucket();
//値をソートするために選択するバケット
List<ExtendedValue> buckets = new ArrayList<>();
buckets.add(new ExtendedValue().setNumberValue(100.23d));
valueBucket.setBuckets(buckets);
//このグループ内の値の並べ替えに使用するPivotTable.valuesリスト内のオフセット
valueBucket.setValuesIndex(0);
return valueBucket;
}
3.3. メタデータの設定
グループ化内の値に関するメタデータを設定します。
private List<PivotGroupValueMetadata> getPivotGroupValueMetadata(){
List<PivotGroupValueMetadata> valueMetadata = new ArrayList<>();
PivotGroupValueMetadata meta = new PivotGroupValueMetadata();
//値に対応するデータが折りたたまれている場合か？
meta.setCollapsed(true);
//メタデータが対応する計算値
meta.setValue(new ExtendedValue().setNumberValue(100.23d));
valueMetadata.add(meta);
return valueMetadata;
}
4. 値の設定
ピボット テーブルに含める値のリストの指定を設定できます。
setCalculatedDisplayType("")で計算結果の表示方法を指定できます。
| type | 表示方法 |
|---|---|
| PERCENT_OF_ROW_TOTAL | ピボット値を行の合計値の%として表示 |
| PERCENT_OF_COLUMN_TOTAL | ピボット値を列の合計値の%として表示 |
| PERCENT_OF_GRAND_TOTAL | ピボット値を総計値の%として表示 |
setSummarizeFunction("")で値を集計する関数を指定できます。
setFormula()とsetSourceColumnOffset()の設定有無によって指定できる関数が変わります。
| 関数名 | setFormula() が設定されている場合 |
setSourceColumnOffset() が設定されている場合 |
|---|---|---|
| SUM | o | o |
| COUNTA | X | o |
| COUNT | X | o |
| COUNTUNIQUE | X | o |
| AVERAGE | X | o |
| MAX | X | o |
| MIN | X | o |
| MEDIAN | X | o |
| PRODUCT | X | o |
| STDEV | X | o |
| STDEVP | X | o |
| VAR | X | o |
| VARP | X | o |
| CUSTOM | o | X |
private List<PivotValue> getPivotValue(){
List<PivotValue> values = new ArrayList<>();
PivotValue v = new PivotValue();
//指定した場合、ピボット値が別のピボット値を使用した計算の結果として表示される
v.setCalculatedDisplayType("PERCENT_OF_ROW_TOTAL");
//値を計算するためのカスタム式
v.setFormula("");
//値に使用する名前。
v.setName("");
//この値の読み取り元となるソース範囲の列オフセット
v.setSourceColumnOffset(0);
//値を集計する関数
v.setSummarizeFunction("SUM");
values.add(v);
return values;
}
5. フィルタの設定
ソース列オフセットごとのフィルターのオプションのマッピングを設定できます。
private Map<String,PivotFilterCriteria> getPivotFilterCriteria(){
Map<String,PivotFilterCriteria> criteria = new HashMap<>();
PivotFilterCriteria c = new PivotFilterCriteria();
//含める必要がある値。ここに記載されていない値は除外。
List<String> visibleValues = new ArrayList<>();
visibleValues.add("test");
c.setVisibleValues(visibleValues);
criteria.put("1", c);
return criteria;
}
おしまい。。