Google Spreadsheet Pivot Table Range Update Using

问题:

How do I edit the Data Range of a Pivot Table in A Google Docs Spreadsheet with a script?

Or...

Is it possible to use a named range for a Pivot Table range?

回答1:

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.

回答2:

I found a workaround that could help you, like I answered in the other Post:JavaScript Method to change PivotTable Data Range Google Docs Spreadsheet

Create a sheet dedicated to your data, row 1 = headers, all other rows = data.

Delete rows with no data.

If for example you use columns A through F, create a Pivot and use this range: SHEET_NAME!A:F

The pivot will "grow" to the new rows because the range is "all the data in the columns A to F".