Recording a macro to modify a pivot

Working with Pivot Tables in Google Sheets using Google Apps Script

Pivot tables are a quick and powerful way to analyse data. Using Google Apps Script, there is an ability to build and modify pivot tables in Google Sheets. In this post I’ll share some tips and tricks for interacting with pivot tables in Google Sheets.

Developer documentation

Previously to add and edit pivot tables you needed to make calls to the Google Sheets API or use the Sheets Advanced Service. This changed in 2018 with the release of the following classes to the Spreadsheet Service (see full release notes):

‘No code’ approaches to pivot table scripts

A quick way to explore the various pivot methods is with the Macro Recorder. With macros, your interactions with a pivot table can be automatically turned into code. Below is a sequence for recording a macro that adjusts the filter on an existing pivot table followed by the resulting code:

Recording a macro to modify a pivot
Macro adding filter to pivot
function filterPivot() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A1').activate();
  var sourceData = spreadsheet.getRange('Data!A1:H36');
  var pivotTable = spreadsheet.getRange('A1').createPivotTable(sourceData);
  var pivotValue = pivotTable.addPivotValue(3, SpreadsheetApp.PivotTableSummarizeFunction.COUNTA);
  var pivotGroup = pivotTable.addRowGroup(3);
  var criteria = SpreadsheetApp.newFilterCriteria()
  .setVisibleValues(['Accounting'])
  .build();
  pivotTable.addFilter(6, criteria);
};

When to update or replace an existing pivot with Apps Script

It is worth noting that the above code snippet isn’t the only way to add a filter to an existing pivot and also macros won’t always give you the ‘best’ code. When interacting with pivots rather than replacing the entire pivot table, which the macro above does, you can also update the pivot with the new configuration settings. For example, below is some code that also modifies a pivot filter by first removing all existing filters before adding a new one:

function filterPivotExisting(){
  // Get active sheet
  const sheet = SpreadsheetApp.getActiveSheet();
  // Get existing pivot table in the sheet
  // @See https://developers.google.com/apps-script/reference/spreadsheet/sheet#getpivottables
  const pivotTable = sheet.getPivotTables()[0];
  // Get all existing filters and remove
  // @See https://developers.google.com/apps-script/reference/spreadsheet/pivot-table#getfilters
  const filters = pivotTable.getFilters();
  filters.map(f => f.remove());
  // Build new filter criteria
  // See https://developers.google.com/apps-script/reference/spreadsheet/filter-criteria
  const criteria = SpreadsheetApp.newFilterCriteria()
  .setVisibleValues(['Accounting'])
  .build();
  // Add filter to pivot
  // @See https://developers.google.com/apps-script/reference/spreadsheet/pivot-table#addfiltersourcedatacolumn,-filtercriteria
  pivotTable.addFilter(6, criteria);
}

Modifying the pivot table source data range

There are also instances where you have no choice between updating or replacing an existing pivot table. If you need to change the data range used for your pivot, currently there is no method to do this other than replacing the existing pivot with a new data range.

If you encounter the scenario mentioned above rather than recreating the pivot you alternatively might be able to trick the pivot to change the data range by using Apps Script to modify the number of rows/columns in the source data sheet.

Creating pivot tables with Google Apps Script

As highlighted above using the macro recorder is a useful way to surface the code you can use to create a pivot table. To learn more about creating pivot tables, the example below constructs a basic summary table.

Pivot with summary of countries
Pivot with summary of countries

This example is based on the ‘Create & update Pivot Tables’ post which illustrates how you can construct pivot tables in Google Apps Script with the Google Sheets API, which was the only option for doing this before the Pivot classes were added to SpreadsheetApp.

Using the following Google Sheet data the code below creates a pivot table with a count of the country (this is the same example as in the macro recording at the beginning of this post):

function createPivot(){
  const doc = SpreadsheetApp.getActive();
  const sheet = doc.getSheetByName('Data');
  // Optional helper to get sourceDataColumn for pivot groups and values.
  // This snippet assumes column headers are unique and in row 1
  // sourceDataColumn is the number of the column this group summarizes.
  // This index represents the absolute number of the column in the spreadsheet;
  // 1 representing column "A," 2 representing column B, etc.
  const headers = sheet.getRange('1:1').getValues()[0];
  const colIdx = headers.reduce((o, k, i) => {
      o[k] = i+1;
      return o;
  }, {});
  // Add new sheet for our Pivot table
  const pivots_sheet = doc.insertSheet();
  // Create new pivot
  const pivotTable = pivots_sheet.getRange("A1").createPivotTable(sheet.getDataRange());
  // Add the country column as a row group
  // @See https://developers.google.com/apps-script/reference/spreadsheet/pivot-table#addrowgroupsourcedatacolumn
  pivotTable.addRowGroup(colIdx['Country']);
  // Add the country column as a row group
  // @See https://developers.google.com/apps-script/reference/spreadsheet/pivot-table#addpivotvaluesourcedatacolumn,-summarizefunction
  pivotTable.addPivotValue(colIdx['Country'], SpreadsheetApp.PivotTableSummarizeFunction.COUNTA);
}

Summary

Hopefully this post has given you an insight into how you can update and create pivot tables in Google Sheets using Google Apps Script. Given the power of pivot tables there are a lot of opportunities to automate data analysis whilst keeping your data in Google Sheets. My top tip given the number of options around pivot table customisation is if you are struggling to find the method you need in your script, record a macro and see what code is generate. Feel free to get in touch with me if you have any questions.

chevron_left
chevron_right
css.php