4

I want to build pivot table with dynamic range in "Google Sheets". Please help me with the same.

In Microsoft Excel we can achieve this with Offset function in pivot table. I am looking for similar solution.

I have tried using Named Range, Offset Function and also app script but none of them is working.

Sai Krishna
  • 115
  • 1
  • 9
  • 1
    Thanks for your question; it is typical to provide some sample script/formula to show what you have tried and any errors or issues you have based on that. – Aleister Tanek Javas Mraz Aug 06 '19 at 15:33
  • 1
    I just tried in random way with knowledge of Microsoft Excel but that is not right way. So I can say I don't know how to achieve it. – Sai Krishna Aug 06 '19 at 15:42
  • Take a look at this Q&A from StackOverflow: https://stackoverflow.com/questions/30574709/google-sheets-pivot-table-not-updating – Aleister Tanek Javas Mraz Aug 06 '19 at 15:44
  • There is an exmple [here](https://sites.google.com/site/scriptsexamples/learn-by-example/google-sheets-api/pivot) about updating pivot tables, in essence you're building it again with a new range, is this what you are trying to do? – AMolina Aug 07 '19 at 10:06
  • Thanks Aleister. My data was starting from 2nd row. I changed it to 1st and followed this solution. – Sai Krishna Aug 18 '19 at 13:03
  • Thanks AMolina. I tried with app script to re-create but did not get required solution. So I gave entire like A:F. – Sai Krishna Aug 18 '19 at 13:05

2 Answers2

4

This feature is not available in Sheets in the same way it is in Excel. I recommend looking into the query() formula, which takes some more setup but is more powerful in many ways.

The Sheets documentation can be found here and I've found this tutorial from Ben Collins to be extremely helpful in the past.

Luke Allpress
  • 170
  • 1
  • 2
  • 8
  • 1
    I now need to amend that Sheets has recently introduced their own Pivot support. Go to Data > Pivot table and you'll get some of the same functionality as Excel. Happy pivoting! – Luke Allpress Sep 12 '19 at 15:49
0

If you are filling the entire Google spreadsheet - you can just use COLUMN names for full height and skip row numbers: your-source-sheet!A:Z Make sure you do not have empty rows at the bottom of the spreadsheet.

specifying full height for pivot range

Paktas
  • 312
  • 3
  • 10