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.
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.
Share Improve this question asked Aug 6, 2019 at 15:31 Sai KrishnaSai Krishna 1352 silver badges9 bronze badges 6- 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 Commented Aug 6, 2019 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 Commented Aug 6, 2019 at 15:42
- Take a look at this Q&A from StackOverflow: stackoverflow./questions/30574709/… – Aleister Tanek Javas Mraz Commented Aug 6, 2019 at 15:44
- There is an exmple here about updating pivot tables, in essence you're building it again with a new range, is this what you are trying to do? – AMolina Commented Aug 7, 2019 at 10:06
- Thanks Aleister. My data was starting from 2nd row. I changed it to 1st and followed this solution. – Sai Krishna Commented Aug 18, 2019 at 13:03
2 Answers
Reset to default 4This feature is not available in Sheets in the same way it is in Excel. I remend 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.
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.