Pivot table with Google Script

Question

I use Script in Google spreadsheet and it is very great.

I had a problem.

I tried to create (from a piece of data already loaded) to create a Pivot Report .. no problem with the menu selection in the spreadsheet (Menu "date" -> "Pivot Report") ... I could not find a way to create a script that I can do this automatically.

How to create a pivot table in Google Spreadsheet Script?

And 'possible?

Answer 1

There's a new feature in Sheets: Advanced Sheets Service.

It has some more features like creating a Pivot table

This is an advanced service that must be enabled before use.

This means you need to enable it twice:

1. In Script menu: Resources ? Advanced Google Services
2. In Google Api console: https://console.developers.google.com/apis/ in Google Apps APIs ? Sheets Api

Here's the code:

https://developers.google.com/apps-script/advanced/sheets#create_a_pivot_table

Answer 2

You can use createDataSourcePivotTable from the Range class as shown in the doc:

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var anchorCell = spreadsheet.getSheets()[0].getRange('A1');
var dataSource = spreadsheet.getDataSources()[0];

var pivotTable = anchorCell.createDataSourcePivotTable(dataSource);
pivotTable.addRowGroup('dataColumnA');
pivotTable.addColumnGroup('dataColumnB');
pivotTable.addPivotValue('dataColumnC', SpreadsheetApp.PivotTableSummarizeFunction.SUM);
pivotTable.addFilter('dataColumnA', SpreadsheetApp.newFilterCriteria().whenTextStartsWith('A').build());