Create & update Pivot Tables

The Google Sheets API documentation contains:

- A guide describing how and why to use the Google Sheets API to create pivot tables in your spreadsheets:
- https://developers.google.com/sheets/api/guides/pivot-tables
- Examples illustrating how you can achieve some common pivot table operations with the API:
- https://developers.google.com/sheets/api/samples/pivot-tables

The Apps Script documentation also contains a code sample to create a pivot table.

Here are more code samples, showing how to create a simple Pivot Table or update an existing one with Apps Script and the Advanced Sheets Service.

Create a simple Pivot Table

This example is based on the following spreadsheet and creates a pivot table with minimal parameters (the source range, a grouping by row and values summarized by COUNTA) to display the number of entries / people by country.

function createPivotTable() {

var ss = SpreadsheetApp.getActiveSpreadsheet();

// The name of the sheet containing the data you want to put in a table.

var sheetName = "Data";

var pivotTableParams = {};

// The source indicates the range of data you want to put in the table.

// optional arguments: startRowIndex, startColumnIndex, endRowIndex, endColumnIndex

pivotTableParams.source = {

sheetId: ss.getSheetByName(sheetName).getSheetId()

};

// Group rows, the 'sourceColumnOffset' corresponds to the column number in the source range

// eg: 0 to group by the first column

pivotTableParams.rows = [{

sourceColumnOffset: 2,

sortOrder: "ASCENDING"

}];

// Defines how a value in a pivot table should be calculated.

pivotTableParams.values = [{

summarizeFunction: "COUNTA",

sourceColumnOffset: 2

}];

// Create a new sheet which will contain our Pivot Table

var pivotTableSheet = ss.insertSheet();

var pivotTableSheetId = pivotTableSheet.getSheetId();

// Add Pivot Table to new sheet

// Meaning we send an 'updateCells' request to the Sheets API

// Specifying via 'start' the sheet where we want to place our Pivot Table

// And in 'rows' the parameters of our Pivot Table

var request = {

"updateCells": {

"rows": {

"values": [{

"pivotTable": pivotTableParams

}]

"start": {

"sheetId": pivotTableSheetId

"fields": "pivotTable"

}

};

Sheets.Spreadsheets.batchUpdate({'requests': [request]}, ss.getId());

}

Edit / Update an existing Pivot Table

The documentation indicates that "the entire pivotTable field must be supplied to make edits. Essentially, editing a pivot table requires replacing it with a new one."

https://developers.google.com/sheets/api/samples/pivot-tables#edit_pivot_table_columns_and_rows

In this example, we use the API to get settings of an existing Pivot Table, update one of those settings (the source range) and push this change via a new API call.

function updatePivotTable() {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var pivotTableSheetName = "Pivot Table 3";

var pivotTableSheetId = ss.getSheetByName(pivotTableSheetName).getSheetId();

var fields = "sheets(properties.sheetId,data.rowData.values.pivotTable)";

var sheets = Sheets.Spreadsheets.get(ss.getId(), {fields: fields}).sheets;

for (var i in sheets) {

if (sheets[i].properties.sheetId == pivotTableSheetId) {

var pivotTableParams = sheets[i].data[0].rowData[0].values[0].pivotTable;

break;

}

// Update source range:

pivotTableParams.source.endRowIndex = 40;

// Send back the updated params