How to filter data in columns using google app script editor

Question

I'm facing some issues related to filter data in the columns using google app script editor.

I'm able to set a filter in columns using google app script as you can see in the above screenshot. but problem is when I'm trying to get the filtered data. it returns some number series instead of actual data as you can see below :

[20-03-09 18:19:48:395 IST] [1,2,4,5,6,8,9,10,11,12,13,14,15,19,20,21,22,23,24,26,27,28,29,30]

To set a filter :

function setFilter() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var filterSettings = {};

  // The range of data on which you want to apply the filter.
  // optional arguments: startRowIndex, startColumnIndex, endRowIndex, endColumnIndex
  filterSettings.range = {
    sheetId: ss.getActiveSheet().getSheetId()
  };

  // Criteria for showing/hiding rows in a filter
  // https://developers.google.com/sheets/api/reference/rest/v4/FilterCriteria
  filterSettings.criteria = {};
  var columnIndex = 2;
  filterSettings['criteria'][columnIndex] = {
    'hiddenValues': ["England", "France"]
  };

  var request = {
    "setBasicFilter": {
      "filter": filterSettings
    }
  };
  Sheets.Spreadsheets.batchUpdate({'requests': [request]}, ss.getId());
}

To get the filtered data:

function getFilteredRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssId = ss.getId();
  var sheetId = ss.getActiveSheet().getSheetId();
  let data = getIndexesOfFilteredRows(ssId,sheetId);
  Logger.log(JSON.stringify(data));
}

function getIndexesOfFilteredRows(ssId, sheetId) {
  var hiddenRows = [];

  // limit what's returned from the API
  var fields = "sheets(data(rowMetadata(hiddenByFilter)),properties/sheetId)";
  var sheets = Sheets.Spreadsheets.get(ssId, {fields: fields}).sheets;  
  for (var i = 0; i < sheets.length; i++) {
    if (sheets[i].properties.sheetId == sheetId) {
      var data = sheets[i].data;
      var rows = data[0].rowMetadata;
      for (var j = 0; j < rows.length; j++) {
        if (rows[j].hiddenByFilter) hiddenRows.push(j);
      }
    }
  }
  return hiddenRows;
}

How to set a filter in columns and get the filtered data using google app script.
Please help me with this.

Tanaike Tanaike · Accepted Answer · 2020-03-09T23:46:24

In your case, the script for filtering has already worked. You want the script for retrieving the values from the filtered sheet in the Spreadsheet.
You want to achieve this using Sheets API with Google Apps Script.

If my understanding is correct, how about this modification? Please think of this as just one of several possible answers.

In your case, the function of getIndexesOfFilteredRows is modified. Using hiddenByFilter, the hidden rows and shown rows are retrieved as an object.

Modified script:

function getIndexesOfFilteredRows(ssId, sheetId) {
  var object = {hiddenRows: [], hiddenRowValues: [], shownRows: [], shownRowValues: []};

  // limit what's returned from the API
  var fields = "sheets(data,properties/sheetId)";
  var sheets = Sheets.Spreadsheets.get(ssId, {fields: fields}).sheets;  
  for (var i = 0; i < sheets.length; i++) {
    if (sheets[i].properties.sheetId == sheetId) {
      var data = sheets[i].data;
      var rows = data[0].rowMetadata;
      for (var j = 0; j < rows.length; j++) {
        var r = [];
        if (data[0].rowData[j] && Array.isArray(data[0].rowData[j].values)) {
          r = data[0].rowData[j].values.map(function(e) {
            var temp = "";
            if (e.hasOwnProperty("userEnteredValue")) {
              if (e.userEnteredValue.hasOwnProperty("numberValue")) {
                temp = e.userEnteredValue.numberValue;
              } else if (e.userEnteredValue.hasOwnProperty("stringValue")) {
                temp = e.userEnteredValue.stringValue;
              }
            }
            return temp;
          });
        }
        if (r.length > 0) {
          if (rows[j].hiddenByFilter) {
            object.hiddenRows.push(j);
            object.hiddenRowValues.push(r);
          } else {
            object.shownRows.push(j);
            object.shownRowValues.push(r);
          }
        }
      }
    }
  }
  return object;
}

Result:

When above script is run for the filtered sheet, the following object which has the hidden row numbers, hidden row values, shown row numbers and shown row values is returned.

{
    "hiddenRows":[0,1],
    "hiddenRowValues":[["a1","b1","c1"],["a2","b2","c2"]],
    "shownRows":[2,3],
    "shownRowValues":[["a3","b3","c3"],["a4","b4","c4"]]
}

Reference:

DimensionProperties

If I misunderstood your question and this was not the direction you want, I apologize.

Added:

How about this sample script? In this sample script, the values filtered with filterValues can be retrieved as an object. In this case, the result is the same with your setFilter() and the modified getIndexesOfFilteredRows(). But the basic filter is not used. So please be careful this.

function myFunction() {
  var filterValues = ["England", "France"]; // Please set the filter values.
  var column = 3; // In this case, it's the column "C". Please set the column number.
  var sheetName = "Sheet1";  // Please set the sheet name.

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetName);
  var values = sheet.getDataRange().getValues();
  var object = values.reduce(function(o, e, i) {
    if (filterValues.indexOf(e[column - 1]) > -1) {
      o.hiddenRows.push(i + 1);
      o.hiddenRowValues.push(e);
    } else {
      o.shownRows.push(i + 1);
      o.shownRowValues.push(e);
    }
    return o;
  }, {hiddenRows: [], hiddenRowValues: [], shownRows: [], shownRowValues: []});

  Logger.log(object)
}

If you want to retrieve only the filtered values, this script might be suitable.
In this case, the script can run with and without V8. But when V8 is enabled, the loop speed will be fast. Ref