Retrieving Values from Sheet Filtered by Slicer in Spreadsheet using Google Apps Script

GasTips / Spreadsheet
Google Apps Script / Spreadsheet / Filter / Slicer

Gists

Overview

This is a sample script for retrieving values from a sheet filtered by Slicer in Spreadsheet using Google Apps Script.

Description

By the update of Google side at November 6, 2019, Class Slicer was added. And also, for Sheets API, AddSlicerRequest and UpdateSlicerSpecRequest were added. By this, Slicer of Spreadsheet got to be able to be managed with Google Apps Script and other languages.

Here, I would like to introduce the method for retrieving values from a sheet filtered by Slicer in Spreadsheet using Google Apps Script.

Principle

Here, I would like to show the principle of this method. Unfortunately, even when Class Slicer was added, the filtered values of Slicer cannot be directly retrieved. So as a workaround, I used the following flow.

  1. Retrieve the current Slicer.
  2. Retrieve the range, column position and the filter criteria from the Slicer.
  3. Create new basic filter using the retrieved range, column position and the filter criteria.
    • This is used for only retrieving the filtered values.
  4. Retrieve the filtered values from the basic filter.
    • About the method for retrieving the filtered values from the basic filter, you can see it at here.
  5. Delete the basic filter.

Usage

1. Create Slicer on Spreadsheet

Here, as a test case, please manually create new Slicer on a sheet in the Spreadsheet. The script retrieves the values filtered by the created Slicer. This sample script supposes that only one Slicer is put in the active sheet.

2. IMPORTANT: Save Setting of Slicer

When Set current filters as default is selected (clicked), the setting of Slicer is saved. Please save the setting of current Slicer by selecting Set current filters as default. You can see Set current filters as default at the following figure.

If Set current filters as default is not selected, the setting of the current Slicer is not saved. By this, the filtered values also cannot be retrieved. Please be careful this. The official document can be seen at here.

3. Run sample script

There are 2 sample scripts for this situation. Both sample scripts retrieve the same result. When the function of myFunction() is run, the filtered values of Slicer on the active sheet can be retrieved.

Sample script 1

In this sample script, only Spreadsheet Service is used.

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var slicer = sheet.getSlicers()[0];
  var range = slicer.getRange();
  var filter = range
    .createFilter()
    .setColumnFilterCriteria(
      slicer.getColumnPosition(),
      slicer.getFilterCriteria()
    );
  var result = range.getValues().filter(function(_, i) {
    return !sheet.isRowHiddenByFilter(i + 1);
  });
  filter.remove();
  Logger.log(result);
}
  • In this case, when return !sheet.isRowHiddenByFilter(i + 1); is modified to return sheet.isRowHiddenByFilter(i + 1);, the hidden rows can be retrieved.

Sample script 2

In this sample script, Spreadsheet Service and Sheets API are used. If there are a lot of rows in the sheet, the process cost of this sample script will be lower than that of above script.

Before you run the script, please enable Sheets API at Advanced Google services.

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var slicer = sheet.getSlicers()[0];
  var range = slicer.getRange();
  var filter = range
    .createFilter()
    .setColumnFilterCriteria(
      slicer.getColumnPosition(),
      slicer.getFilterCriteria()
    );
  var values = Sheets.Spreadsheets.get(sheet.getParent().getId(), {
    ranges: [sheet.getSheetName()],
    fields: "sheets/data"
  });
  filter.remove();
  var result = values.sheets[0].data[0].rowMetadata.reduce(function(ar, e, i) {
    if (!e.hiddenByFilter && values.sheets[0].data[0].rowData[i]) {
      ar.push(
        values.sheets[0].data[0].rowData[i].values.map(function(col) {
          return col.userEnteredValue[Object.keys(col.userEnteredValue)[0]];
        })
      );
    }
    return ar;
  }, []);
  Logger.log(result);
}

References

 Share!