Google Sheets spreadsheet not updated

Question

I have a google script application that sends information to an organized worksheet and wants to create a pivot table with all the information in the worksheet. I can do this, but whenever I send a new row of data to a worksheet, it does not automatically get included in the pivot table. I have to manually change the pivot table range every time I send a new row of data. Is there a way to make a sheet / pivot table table automatically include a new row of data?

Answer 1

I worked on this issue by specifying only a range of columns.

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

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

Answer 2

there is a way. ive done exactly what 3 years ago on the sheets.

if you submit newlines using google forms then there is no way.

if you are trying to programmatically update a range, there is no way either because named ranges must be removed and then added again, which causes #ref on the vault.

now for the good part. if you add a new script line, do not add it to the end. instead, hold the fake line at the end (with zero values, so it doesn’t affect the rotation) and insert a new line directly in front of it. your range (with or without a name) will be updated.

even for the case of Google forms, you can make it work by first adding all the empty lines to the answer sheet and make the named range empty too. enjoy it!