Syntax
Argument
Description
data_field
(Required) The name of the PivotTable field that contains the data that you want to retrieve. This needs to be in quotes.
pivot_table
(Required) A reference to any cell, range of cells, or named range of cells in a PivotTable. This information is used to determine which PivotTable contains the data that you want to retrieve.
field1, item1, field2, item2…
(Optional) 1 to 126 pairs of field names and item names that describe the data that you want to retrieve. The pairs can be in any order. Field names and names for items other than dates and numbers need to be enclosed in quotation marks.
Usage notes
The GETPIVOTDATA function returns visible data from a PivotTable.
To extract data from a cell in a pivot table, you can enter a normal cell link, such as =B5, or you can use the GetPivotData formula, which is specially designed to extract data from a pivot table. This formula will be created automatically when you reference a cell in a PivotTable.
You can also select whether to display the GETPIVOTDATA formula or display normal cell reference through the workbook pivotAreaReference option
Benefits
The advantage of using the GETPIVOTDATA function is that it uses criteria to ensure that the correct data is returned, even if the pivot table layout is changed.
Extension
The GETPIVOTDATA function supports spill, so you can get a range of pivot data based on field and item references that you defined.
window.onload = function () {
var spread = new GC.Spread.Sheets.Workbook(_getElementById('ss'), { sheetCount: 2 });
initSpread(spread);
var pivotLayoutSheet = spread.getSheet(0);
initPivotTable(pivotLayoutSheet);
initStyles(spread);
setGetPivotDataFunction(pivotLayoutSheet);
};
function initSpread(spread) {
spread.suspendPaint();
spread.options.allowDynamicArray = true;
var sheet = spread.getSheet(1);
sheet.name("DataSource");
sheet.setRowCount(117);
sheet.setColumnWidth(0, 120);
sheet.getCell(-1, 0).formatter("YYYY-mm-DD");
sheet.getRange(-1,4,0,2).formatter("$ #,##0");
sheet.setArray(0, 0, pivotSales);
let table = sheet.tables.add('tableSales', 0, 0, 117, 6);
for(let i=2;i<=117;i++)
{
sheet.setFormula(i-1,5,'=D'+i+'*E'+i)
}
table.style(GC.Spread.Sheets.Tables.TableThemes["none"]);
var sheet0 = spread.getSheet(0);
sheet0.name("PivotLayout");
spread.resumePaint();
}
function initPivotTable(sheet) {
var myPivotTable = sheet.pivotTables.add("myPivotTable", "tableSales", 1, 1, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.light8);
myPivotTable.suspendLayout();
myPivotTable.options.showRowHeader = true;
myPivotTable.options.showColumnHeader = true;
myPivotTable.add("salesperson", "Salesperson", GC.Spread.Pivot.PivotTableFieldType.rowField);
myPivotTable.add("car", "Cars", GC.Spread.Pivot.PivotTableFieldType.rowField);
let groupInfo = { originFieldName: "date", dateGroups: [{ by: GC.Pivot.DateGroupType.quarters }] };
myPivotTable.group(groupInfo);
myPivotTable.add("Quarters (date)", "Qt", GC.Spread.Pivot.PivotTableFieldType.columnField);
myPivotTable.add("total", "Totals", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
let itemList = ["Alan","John", "Tess"];
myPivotTable.labelFilter("Salesperson", { textItem: { list: itemList, isAll: false } });
myPivotTable.sort("Salesperson", { sortType: GC.Pivot.SortType.asc });
let carList = ["Audi","BMW","Mercedes"];
myPivotTable.labelFilter("Cars", { textItem: { list: carList, isAll: false } });
myPivotTable.sort("Cars", { sortType: GC.Pivot.SortType.asc });
let style = new GC.Spread.Sheets.Style();
style.formatter = "$ #,##0";
myPivotTable.setStyle({dataOnly: true}, style);
myPivotTable.resumeLayout();
myPivotTable.autoFitColumn();
}
function setGetPivotDataFunction(sheet) {
sheet.setColumnWidth(8, 20);
sheet.setValue(1, 9, 'GETPIVOTDATA basic usage');
sheet.setStyle(1, 9, 'intro');
var formula1 = '=GETPIVOTDATA("Totals",$B$2,"Salesperson","Tess","Cars","Audi","Qt","Qtr2")';
sheet.setValue(3, 9, 'Returns the total Audi sales made by Tess in the second quarter');
sheet.setStyle(3, 9, 'introSec');
sheet.setValue(4, 9, formula1);
sheet.setStyle(4, 9, 'formula');
sheet.getCell(5,9).formatter("$ #,##0");
sheet.setFormula(5, 9, formula1);
sheet.setStyle(5, 9, 'result');
var formula2 = '=GETPIVOTDATA("Totals",$B$2,"Salesperson","Alan","Cars","BMW")';
sheet.setValue(7, 9, 'Returns the total BMW sales made by Alan');
sheet.setStyle(7, 9, 'introSec');
sheet.setValue(8, 9, formula2);
sheet.setStyle(8, 9, 'formula');
sheet.getCell(9,9).formatter("$ #,##0");
sheet.setFormula(9, 9, formula2);
sheet.setStyle(9, 9, 'result');
// Invoked formula
var formula3 = '=GETPIVOTDATA("Totals",$B$2,"Qt","Qtr1")';
sheet.setValue(11, 9, 'Returns the total sales for the first quarter');
sheet.setStyle(11, 9, 'introSec');
sheet.setValue(12, 9, formula3);
sheet.setStyle(12, 9, 'formula');
sheet.getCell(13,9).formatter("$ #,##0");
sheet.setFormula(13, 9, formula3);
sheet.setStyle(13, 9, 'result');
var formula4 = '=GETPIVOTDATA("Totals",$B$2)';
sheet.setValue(15, 9, 'Returns the grand total');
sheet.setStyle(15, 9, 'introSec');
sheet.setValue(16, 9, formula4);
sheet.setStyle(16, 9, 'formula');
sheet.getCell(17,9).formatter("$ #,##0");
sheet.setFormula(17, 9, formula4);
sheet.setStyle(17, 9, 'result');
var formula5 = '=GETPIVOTDATA("Totals",$B$2,"Salesperson",{"Alan";"John";"Tess"},"Cars",C10:C12,"Qt",E3:G3)';
sheet.setValue(19, 9, 'Returns the spill range of total Audi/BMW/Mercedes sales made by Alan/John/Tess in the Qtr2/Qtr3/Qtr4 quarter');
sheet.setStyle(19, 9, 'introSec');
sheet.setValue(20, 9, formula5);
sheet.setStyle(20, 9, 'formula');
sheet.setFormula(21, 9, formula5);
sheet.getRange(21, 9, 3, 3).hAlign(0);
}
function initStyles(spread) {
var introStyle = new GC.Spread.Sheets.Style();
introStyle.name = 'intro';
introStyle.font = 'normal bold 13px Segoe UI';
introStyle.foreColor = "#172b4d";
spread.addNamedStyle(introStyle);
var introSecStyle = new GC.Spread.Sheets.Style();
introSecStyle.name = 'introSec';
introSecStyle.font = 'normal bold 12px Segoe UI';
introSecStyle.foreColor = "#000";
spread.addNamedStyle(introSecStyle);
var formulaStyle = new GC.Spread.Sheets.Style();
formulaStyle.name = 'formula';
formulaStyle.font = 'normal bold 12px Consolas';
formulaStyle.foreColor = "#c00000";
spread.addNamedStyle(formulaStyle);
var resultStyle = new GC.Spread.Sheets.Style();
resultStyle.name = 'result';
resultStyle.hAlign = 0;
spread.addNamedStyle(resultStyle);
}
function _getElementById(id) {
return document.getElementById(id);
}
<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets-shapes/dist/gc.spread.sheets.shapes.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets-pivot-addon/dist/gc.spread.pivot.pivottables.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/data/pivot-data.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script>
<script src="app.js" type="text/javascript"></script>
<link rel="stylesheet" type="text/css" href="styles.css">
</head>
<body>
<div class="sample-tutorial">
<div id="ss" class="sample-spreadsheets"></div>
</div>
</body>
</html>
.sample-tutorial {
position: relative;
height: 100%;
}
.sample-spreadsheets {
width: 100%;
height: 100%;
overflow: hidden;
float: left;
}
.options-container {
float: right;
width: 210px;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
}
.option-row {
font-size: 14px;
padding: 5px;
margin-top: 10px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
#app {
height: 100%;
}
