2

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?

Rubén
  • 34,714
  • 9
  • 70
  • 166
Cristiano Boschi
  • 21
  • 1
  • 3
  • There is no such option available yet in Apps Script. You may file a feature request here http://code.google.com/p/google-apps-script-issues/issues/entry?template=Feature%20request%20from%20user – Waqar Ahmad Jul 04 '12 at 11:39
  • https://stackoverflow.com/questions/11326172/pivot-table-with-google-script/52601364#52601364 – BuffK Oct 02 '18 at 02:50

3 Answers3

2

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

Max Makhrov
  • 17,309
  • 5
  • 55
  • 81
1

A Pivot Table has been created though script, although its not Google's one that you mentioned in your post. See the Pivot Table through script by Romain Vialard which is available in the spreadsheet script galary (just go to the Tools>Script galary from the spreadsheet's menu; once there do a search for Pivot Table, and Romain's comes up.)

David Tew
  • 1,441
  • 1
  • 10
  • 12
  • 1
    Tools->script gallery is no longer on the menu. Can you provide a direct link to the script? – MCW Mar 10 '14 at 14:15
0

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());
SAAD
  • 759
  • 1
  • 9
  • 23