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?

What method are you using to get the range from Sheet? By using [sheet.getDataRange()](https://developers.google.com/apps-script/reference/spreadsheet/sheet#getDataRange()), you can get the data range from the updated sheet. Hope that helps! — KRR, Jun 01 '15 at 19:33
Sorry I might not have been clear. I am using the built in option in Google Sheets to create a pivot table. When I click that, it creates a table with all the information I currently have in the sheet, but I would like it to update as more rows are added to the sheet. i cannot find an option for it to do this. — Ben, Jun 01 '15 at 19:58
In that case you have to manually change the range whenever needed. This is not even possible pragmatically. Check this question http://stackoverflow.com/questions/27550329/create-pivot-table-using-google-apps-script — KRR, Jun 01 '15 at 20:52
Alright thanks. You would think that google would include a pretty basic functionality like that with the spreadsheet. Just like a check box or something that says "Update automatically with new data". — Ben, Jun 02 '15 at 11:39

score 14 · Answer 1 · answered Jun 17 '16 at 14:24

14

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.

answered Jun 17 '16 at 14:24

merlot

610
1
6
14

5

Note that this does not automatically add new items to "Filters." If you have blank cells filtered out, you will have to re-pick cells (Select all, uncheck blanks) every time. – Michael Aug 16 '16 at 13:27
1

@MitchLillie I happened to notice today that a new pivot table had filter options for specific values or a formula! Hooray! – Michael Dec 18 '19 at 18:38

score 2 · Answer 2 · edited Dec 18 '19 at 18:46

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.

edited Dec 18 '19 at 18:46

Michael

8,362
6
61
88

answered Jun 05 '15 at 00:24

Zig Mandel

19,571
5
26
36

1

"...then there is no way." There is always a way. – Aleister Tanek Javas Mraz Aug 06 '19 at 15:46
1

@AleisterTanekJavasMraz I only said so for that case of forms. That was in 2015, likely enough has changed since then that now its possible with other ways. – Zig Mandel Aug 08 '19 at 04:29

score 1 · Answer 3 · edited Jun 20 '20 at 09:12

I encountered the same problem with the Google Sheet linked to a form. How I solved it:

Edit the Pivot table range to include the empty row after the last row in your sheet.

If your pivot table data range is 'Sheet Name'!A1:S100, change it to 'Sheet Name'!A1:S101.
Add a filter to your pivot table for a column (present in the data range) that will never be blank and set the condition for this column - 'Is not empty'

If you have a column 'Timestamp' (and you're sure it will never be empty), then add this column to the filter, select 'filter by condition' and set the condition to 'Is not empty'.

These steps will filter out the last (empty) row. When you will add a new row to your data, the pivot table will update automatically and you won't have to update the data range again.

score 0 · Answer 4 · answered Jan 10 '20 at 15:53

In my case what was interfering were the fields in the Pivot's Filters section (highlighted in red below).

Make sure you test without any filters... see if it works. Then add your filters one by one and play with the pivot. Probably the filter(s) is\are influencing the pivot to not update.

Google Sheets Pivot Table Not Updating

4 Answers4

Linked