Google Sheets Pivot Table Not Updating

Question

I have a google app script which submits info to an organized sheet and would like to create a pivot table with the all information in the sheet. I can do this, but whenever I submit a new row of data to the sheet, it is not automatically included in the pivot table. I have to manually change the range of the pivot table every time I submit a new row of data. Is there any way I can make the sheet/pivot table automatically include the new row of data?

Answer 1

I worked around this issue by only specifying the column range.

For example, if you have row data in columns A to F, set the range of the pivot table to SHEET!A:F

If you add rows now, the new data in those columns will be added to the pivot table.

Answer 2

There is a way. I did exactly that 3 years ago on Sheets.

If you are submitting the new rows using Google Forms, then there is no way.

If you try to programmatically update the range, there is no way either because named ranges need to be deleted then re-added, causing #REF on the pivot.

Now for the good part: If you are adding the new row with a script, do not append it to the end. Instead, keep a fake row at the end (with zero values so pivot is not affected) and insert the new row just before it. Your range (named or not) will update.

Even for the Google Forms case, you might get it to work by pre-adding all blank rows to the response sheet and make the named range include the blank ones too.