How do I edit the Data Range of a Pivot Table in A Google Docs Spreadsheet with a script?
Is it possible to use a named range for a Pivot Table range?
Unfortunately, at this time (31 July 2012), I believe the answers are "you can't" and "no". :-(
The only workarounds I can suggest are: 1. use the QUERY spreadsheet function, which supports open-ended ranges that will accommodate growing data, to generate the table, or 2. use Google Apps Script itself to generate the table. Both of which require more effort than if you could just modify the source data ranges with script (like you can with Excel/VBA). On the other hand, the general implementation of Pivot Tables in GSheets is still a bit buggy, so it's arguable that you might want to explore one of the other options for this reason.