-1

Is there a method in Google Apps script for Pivot Table? I can't find anything in the documentation that creates a Pivot Table from a data range in a sheet using Google Apps Script.

Thanks in advance.

Rubén
  • 34,714
  • 9
  • 70
  • 166
Doria
  • 17
  • 2
  • 3

3 Answers3

2

Try google query with pivot. That should work. For example:

=QUERY(Sheet1!A1:C, "select B, count(C) group by B pivot A")
Ed Nelson
  • 10,015
  • 2
  • 27
  • 29
  • If that worked for you, please approve the answer. Thanks. – Ed Nelson Mar 31 '16 at 17:27
  • Hi Ed Nelson! You already posted it! I followed your example. – Doria Apr 01 '16 at 18:20
  • This doesn't work because it includes the empty cells into the pivoting results. – Kan Li Mar 24 '17 at 19:55
  • @KanLi That is how Pivot Tables work. I answered the question. What is your issue. – Ed Nelson Mar 24 '17 at 23:38
  • @EdNelson, I don't think it answers the question. The question asks about the data range of the sheet, not the entire sheet. My previous comment was saying pivoting table generated from the data range of the sheet will be different to that generated from the entire sheet. – Kan Li Mar 27 '17 at 18:38
2

This is now possible. Google provides an example in their Advanced Google Services tutorial. The solution presumes you enable the Sheets API in "Advanced Google services..." for the project at hand and also enable the API in the Developer Console.

That said, there is one caveat here. It seems there is a bug with integrating pivot filter criteria, as mentioned here: Pivot Filter Criteria not Properly Applied

tibtib
  • 86
  • 7
1

There is no programmatic way to create a pivot table from Apps Script. You can add support for this feature by "staring" the issue at:
https://code.google.com/p/google-apps-script-issues/issues/detail?id=3158

Spencer Easton
  • 5,642
  • 1
  • 16
  • 25
  • Now this is possible by using Sheets API as an advanced service. See [this answer](https://stackoverflow.com/a/44594198/1595451) – Rubén Apr 07 '18 at 21:45