SpreadJS 14
Formula Reference / Formula Functions / Lookup and Reference Functions / GETPIVOTDATA
In This Topic
    GETPIVOTDATA
    In This Topic

    This function returns visible data from a Pivot Table. It queries the pivot table and gets the specific data based on the pivot table structure, instead of cell references. The main advantage of using this function is that it ensures that the correct data is returned, even if the pivot table layout is changed.

    Syntax

    =GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], ...)

    Arguments

    This function has the following arguments:

    Arguments Descriptions
    data_field The name of the PivotTable field that contains the data you want to retrieve. This needs to be in double quotes.
    pivot_table 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] Up 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.

    Usage

    You can enter the GETPIVOTDATA function by using = (the equal sign) in the cell in which you want to return the value. You can also use this function with other functions.

    Remarks

    The following points hold true while working with GETPIVOTTABLE function:

    Return value

    Returns visible data from a pivot table.

    Examples

    The following images show the usage of GETPIVOTDATA function.

    Usage of GETPIVOTDATA

    The following sample code show the usage of GETPIVOTDATA function.

    C#
    Copy Code
    $(document).ready(function () {
         // initializing Spread
         var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 2 });
         spread.suspendPaint();
         // get sheets
         var pivotLayoutSheet = spread.getSheet(0);
         var dataSourceSheet = spread.getSheet(1);
         // set sheet name
         pivotLayoutSheet.name("PivotLayout");
         dataSourceSheet.name("DataSource");
         // set row count
         dataSourceSheet.setRowCount(245);
         // set datasource
         dataSourceSheet.setArray(0, 0, pivotDB_UseCase);
         // add table to dataSourceSheet
         dataSourceSheet.tables.add('tableSales', 0, 0, 245, 8);
         spread.resumePaint();
         // initialize pivottable
         initPivotTable(pivotLayoutSheet);
         // auto fit columns in both the sheets
         autoFit(pivotLayoutSheet);
         autoFit(dataSourceSheet);
     });
    
     function initPivotTable(sheet) {
         // add pivottable
         var myPivotTable = sheet.pivotTables.add("myPivotTable", "tableSales", 0, 0, GC.Spread.Pivot.PivotTableLayoutType.tabular, GC.Spread.Pivot.PivotTableThemes.dark3);
         myPivotTable.suspendLayout();
         // show rowHeader and columnHeader for PivotTable
         myPivotTable.options.showRowHeader = true;
         myPivotTable.options.showColumnHeader = true;
         // add column fields
         myPivotTable.add("Category", "Category", GC.Spread.Pivot.PivotTableFieldType.columnField);
         // add row fields
         myPivotTable.add("Region", "Region", GC.Spread.Pivot.PivotTableFieldType.rowField);
         myPivotTable.add("City", "City", GC.Spread.Pivot.PivotTableFieldType.rowField);
         // add value field with SubtotalType Sum
         myPivotTable.add("Quantity", "Sum of quantity", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
         // add filter field
         myPivotTable.add("OrderDate", "OrderDate", GC.Spread.Pivot.PivotTableFieldType.filterField);
              
         var formula1 = '=GETPIVOTDATA("Quantity",$A$3)';
         sheet.setValue(3, 10, 'Total Quantity');
         sheet.setStyle(3, 10, 'introSec');
         sheet.setFormula(3, 11, formula1);
    
         var formula2 = '=GETPIVOTDATA("Quantity",$A$3,"Category","Bakery")';
         sheet.setValue(4, 10, 'Total Bakery Quantity');
         sheet.setStyle(4, 10, 'introSec');
         sheet.setFormula(4, 11, formula2);
    
         var formula3 = '=GETPIVOTDATA("Quantity",$A$3,"Region","West")';
         sheet.setValue(5, 10, 'West Region');
         sheet.setStyle(5, 10, 'introSec');
         sheet.setFormula(5, 11, formula3);
    
         var formula4 = '=GETPIVOTDATA("Quantity",$A$3,"Region","East","City","Jersey")';
         sheet.setValue(6, 10, 'Quantity in Jersey');
         sheet.setStyle(6, 10, 'introSec');
         sheet.setFormula(6, 11, formula4);
         myPivotTable.resumeLayout();
         return myPivotTable;
     }
    
     function autoFit(sheet) {
         // auto fit columns
         let columnCount = sheet.getColumnCount();
         for (let i = 0; i < columnCount; i++) {
             sheet.autoFitColumn(i);
                }
            }