Pivot layouts drift when people drag fields after every CSV import. Apps Script can recreate a known-good pivot after data lands.
This example deletes PivotByRegion, inserts a fresh tab, and calls Range.createPivotTable() on the full SalesData used range.
Row groups use 1-based column indexes; values use PivotTableSummarizeFunction for SUM on Amount and AVERAGE on Units.
Run it from the editor after imports, or attach a time-driven trigger once OAuth succeeds once.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Sheet | Column | Purpose |
|---|---|---|
| SalesData | A Region | First row group |
| SalesData | B Product | Second row group |
| SalesData | C Quarter | Spare column (optional group) |
| SalesData | D Amount | SUM value |
| SalesData | E Units | AVERAGE value |
| PivotByRegion | A1 | Pivot anchor (recreated each run) |
What this script does
createPivotTable() wipes PivotByRegion and rebuilds groups/values so dashboard extracts stay consistent.
Filters drop blank Region rows so empty imports do not inflate category totals.
Prerequisites
Edit access to the spreadsheet and a SalesData tab with headers plus at least one data row.
- Numeric Amount/Units
- Stable column order
- OK to recreate PivotByRegion
Walkthrough
Resolve SalesData, delete any existing destination, insert a new sheet, then createPivotTable on A1.
addRowGroup / addPivotValue configure layout; flush() renders before exit.
Edge cases
Empty sources throw. Wrong indexes silently mislabel groups — verify headers match code.
- Deleting the tab drops local formatting
- Huge ranges may approach interactive time limits
How to test
Seed ~20 rows, run the function, compare Region totals to SUMIF on SalesData.
Hardening for production
Catch errors to an Audit sheet; rename instead of delete if Looker Studio points at the tab.
Variations
Add COUNTUNIQUE on CustomerId, or read sheet names from Script Properties.
Full code: createPivotTable()
Run createPivotTable() after SalesData has headers and rows. Adjust constants and column indexes first.
/**
* Build a pivot table on a destination sheet from SalesData.
*/
const SOURCE_SHEET = "SalesData";
const PIVOT_SHEET = "PivotByRegion";
function createPivotTable() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const source = ss.getSheetByName(SOURCE_SHEET);
if (!source) throw new Error("Missing sheet: " + SOURCE_SHEET);
let dest = ss.getSheetByName(PIVOT_SHEET);
if (dest) ss.deleteSheet(dest);
dest = ss.insertSheet(PIVOT_SHEET);
const lastRow = source.getLastRow();
const lastCol = source.getLastColumn();
if (lastRow < 2) throw new Error("Source has no data rows");
const range = source.getRange(1, 1, lastRow, lastCol);
const pivot = dest.getRange("A1").createPivotTable(range);
pivot.addRowGroup(1); // Region
pivot.addRowGroup(2); // Product
pivot.addPivotValue(4, SpreadsheetApp.PivotTableSummarizeFunction.SUM);
pivot.addPivotValue(5, SpreadsheetApp.PivotTableSummarizeFunction.AVERAGE);
pivot.addFilter(1, SpreadsheetApp.newFilterCriteria().whenCellNotEmpty().build());
SpreadsheetApp.flush();
Logger.log("Pivot created on %s (%s data rows)", PIVOT_SHEET, lastRow - 1);
}- Line 4: Change tab names to match your workbook.
- Line 13: Removes the previous pivot so old groups never stack.
- Line 7: Creates the pivot anchored at destination A1.
- Line 23: Column 1 of the source range becomes the Region group.
- Line 25: Sums Amount like the UI SUM value field.
- Line 27: Keeps only rows with a Region value.
- Line 29: Forces the pivot to paint before the script exits.
Deploy this example
- 01
Open Apps Script
In the bound spreadsheet: Extensions → Apps Script. For standalone projects, create one at script.google.com and link your Sheet by ID.
- 02
Paste and save
Add a .gs file, paste the code below, rename constants at the top (sheet names, column letters, API property keys), then save.
- 03
Authorize once
Run the main function from the editor. Accept OAuth scopes when prompted — triggers cannot run until authorization succeeds once.
- 04
Add the trigger
Triggers → Add trigger → choose the handler function and event (time-driven, on edit, or on form submit). Delete test triggers before production.
Before you run: pivot setup
- 1SalesData exists with row-1 headers
- 2Columns match Region/Product/Quarter/Amount/Units (or update indexes)
- 3Amount and Units are numeric
- 4Test on a copy first
- 5Authorize SpreadsheetApp once from the editor
- 6Confirm nothing depends on old PivotByRegion formatting
Frequently asked questions
Yes. Authorize once in the editor, then attach a daily trigger. The trigger account needs edit access.
Clearing cells leaves stale pivot config. Recreating makes the code the source of truth.
No — pivot group/value indexes are 1-based within the source range.
Put helper columns on SalesData, then pivot those columns. The Script pivot API covers groups, values, and filters.
No. createPivotTable is on SpreadsheetApp.
Stack them into one staging sheet first, then pivot the combined range.
Groups are case-sensitive. Normalize case on import for stable labels.