GoogleスプレッドシートのピボットテーブルをGASで自動集計

投資成果の確認のため評価損益の記録は、Googleアカウントがあれば無料で使用できるGoogleスプレッドシートをおすすめします。

記録したデータはGoogleスプレッドシートのピボットテーブルを使うと見やすく集計できます。

ピボットテーブルをGoogle Apps Script（GAS）で自動化できるとさらに利便ですよね。

というこで、今回はGASでピボットテーブルを操作するコードをご紹介します。

評価損益のデータ記録の自動化はこちらをご覧ください。

python（パイソン）でSBI証券にログイン

運用成果を定期的に記録するのは大変ですがプログラミングで自動化できます。 この記事ではpythonを使ってSBI証券へ自動ログインをする方法を紹介しています。 興味のあるかたはご覧ください。

fremilli.com

2020.07.09

GoogleスプレッドシートのピボットテーブルをGASで自動集計

ピボットテーブルの構成要素は次の５つがあります。

これらを指定することで、集計は自由自在です。

GASでの指定方法を説明していきます。

ピボットテーブルをGASで操作するコード

スプレッドシート名「pivot」の「シート1」に以下のデータがあり、「シート2」にピボットテーブルで集計するコードを紹介します。

集計後のピボットテーブル

ピボットテーブルで集計するコードです。

//-----------------------------------------------------------------------
//集計元のデータとピボットテーブルの表示場所を設定
//-----------------------------------------------------------------------
//ピボットテーブルを表示するシートを指定
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var PivotSheet = spreadsheet.getSheetByName('シート2');

//集計もとデータの範囲を指定
var sourceData = PivotSheet.getRange('シート1!A1:E7');

//ピボットテーブルの作成
var pivotTable = PivotSheet.getRange('A1').createPivotTable(sourceData);

//-----------------------------------------------------------------------
//行の項目を設定
//-----------------------------------------------------------------------
//集計もとデータの1から数えて何列目かを数値で指定←誤り
//スプレッドシートの列の絶対数←正しい（ご指摘ありがとうございました。）

var pivotGroup = pivotTable.addRowGroup(1);//日付
pivotGroup.showTotals(false).sortDescending();//合計なし、降順

//-----------------------------------------------------------------------
//列の項目を設定
//-----------------------------------------------------------------------
//集計もとデータの1から数えて何列目かを数値で指定
pivotGroup = pivotTable.addColumnGroup(2);//銘柄
pivotGroup.showTotals(false).sortAscending();//合計なし、降順

//-----------------------------------------------------------------------
//集計する値の項目を設定
//-----------------------------------------------------------------------
//集計もとデータの1から数えて何列目かを数値で指定
//PivotTableSummarizeFunctionの後に集計したいものを指定
//SUMは合計、AVERAGEは平均、MAXは最大など
var pivotValue = pivotTable.addPivotValue(3, SpreadsheetApp.PivotTableSummarizeFunction.SUM);
pivotValue.setDisplayName('取得価格');
var pivotValue = pivotTable.addPivotValue(4, SpreadsheetApp.PivotTableSummarizeFunction.SUM);
pivotValue.setDisplayName('評価額');
var pivotValue = pivotTable.addPivotValue(5, SpreadsheetApp.PivotTableSummarizeFunction.SUM);
pivotValue.setDisplayName('評価損益');

詳しく説明していきます。

集計元のデータとピボットテーブルの表示場所を設定

//ピボットテーブルを表示するシートを指定
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var PivotSheet = spreadsheet.getSheetByName('シート2');

//集計元のデータの範囲を指定
var sourceData = PivotSheet.getRange('シート1!A1:E7');

//ピボットテーブルの作成
var pivotTable = PivotSheet.getRange('A1').createPivotTable(sourceData);

コード中のコメントの通りです。

ピボットテーブルを表示するシートを指定して、集計元のデータ範囲を指定しピボットテーブルを作成します。

行の項目を設定

//-----------------------------------------------------------------------
//行の項目を設定
//-----------------------------------------------------------------------
//集計元データの1から数えて何列目かを数値で指定←誤り
//スプレッドシートの列の絶対数←正しい（ご指摘ありがとうございました。）


var pivotGroup = pivotTable.addRowGroup(1);
pivotGroup.showTotals(false).sortDescending();//合計なし、降順

//複数指定する場合は同じ指定を繰り返す
pivotGroup = pivotTable.addRowGroup(2);//1から数えて何列目かを指定
pivotGroup.showTotals(true).sortAscending();//合計あり、昇順

行の項目の設定は、addRowGroup(数値)で行います。

引数の数値は集計元のデータを左から数えた列数を指定します。

スプレッドシートの列の絶対数を指定します。

Yoshi

左から数えた数値は誤りで、正しくはスプレッドシートの列の絶対数です。

ご指摘ありがとうございました。

お詫びして、訂正させていただきます。

今回は日付を行の項目にしていしているので引数は「1」です。

項目は総計の表示・並び替えを指定できます。

showTotals(false)で総計の非表示、trueで表示。

sortAscending()は昇順、sortDescending()が降順です。

同じ指定を繰り返すと、列の項目を追加できます。

列の項目を設定

//-----------------------------------------------------------------------
//列の項目を設定
//-----------------------------------------------------------------------
//集計もとデータの1から数えて何列目かを数値で指定
pivotGroup = pivotTable.addColumnGroup(2);
pivotGroup.showTotals(true).sortAscending();//合計なし、降順

列の項目の設定も「行の設定」とほぼ同じ。

列の項目の設定は、addColumnGroup(数値)で行います。

引数の数値は集計元のデータを左から数えた列数を指定するのは「行の項目」と同じ。

項目の総計の表示・並び替えの指定も同じです。

値の項目を設定

//-----------------------------------------------------------------------
//集計する値の項目を設定
//-----------------------------------------------------------------------
//集計元データの1から数えて何列目かを数値で指定
//PivotTableSummarizeFunctionの後に集計したいものを指定
//SUMは合計、AVERAGEは平均、MAXは最大など
var pivotValue = pivotTable.addPivotValue(3, SpreadsheetApp.PivotTableSummarizeFunction.SUM);
pivotValue.setDisplayName('取得価格');
pivotValue = pivotTable.addPivotValue(4, SpreadsheetApp.PivotTableSummarizeFunction.SUM);
pivotValue.setDisplayName('評価額');
pivotValue = pivotTable.addPivotValue(5, SpreadsheetApp.PivotTableSummarizeFunction.SUM);
pivotValue.setDisplayName('評価損益');

値の設定は次の通り。

addPivotValue(数値, SpreadsheetApp.PivotTableSummarizeFunction.集計内容)

数値は行・列と同じで集計元データの列数。

集計内容は合計は「SUM」平均は「AVERAGE」最大値は「MAX」など。

表示名称を変更するにはsetDisplayName(表示名)。

今回は値を３つ指定しているので同じ設定を繰り返します。

フィルタ（抽出条件）の設定

//setVisibleValuesでフィルタで抽出したい文字を指定
//addFilterは集計元データの1から数えて何列目かを数値で指定
var criteria = SpreadsheetApp.newFilterCriteria()
criteria = SpreadsheetApp.newFilterCriteria().setVisibleValues(['抽出する項目']).build();
pivotTable.addFilter(3, criteria);

今回の例では設定していませんがフィルタの設定もご紹介します。

setVisibleValues(['抽出する項目'])でフィルタで抽出する項目を指定できます。

addFilter(数値, criteria)で集計元データの列数を数値で指定。

最後に

ピボットテーブルをGASで操作するコードを紹介しました。

このコードを応用するとさまざまな集計が可能になります。

Googleスプレッドシートでの運用成果の記録・集計の手助けになれば嬉しいです。

GASで運用成果の記録は、こちらをご覧ください。

Google Apps Script（GAS）でSBI証券にログイン

証券口座の評価損益を自動で取得してGoogleスプレッドシートで管理できると便利ですよね？ SBI証券へGoogleAppsScript（GAS）でログインするためのするためのスクリプトコードをご紹介します。 参考にしていただければ嬉しいです。

fremilli.com

2020.06.26

よりGASを学ぶならテックアカデミーがおすすめです。

まずは無料体験から。