Retrieving Values from Filtered Sheet in Spreadsheet using Google Apps Script

28 Jul 2019, 17:05

GasTips

Google Apps Script / Spreadsheet / filter

This is a sample script for retrieving values from filtered Sheet in Spreadsheet using Google Apps Script. When the values are retrieved the filtered sheet by the basic filter, if setValues() and setDisplayValues() are used, all values without the filter are retrieved. In this script, I would like to introduce the method for retrieving the values from the filtered sheet using Google Apps Script.

In order to retrieve the values from the filtered sheet, one method has already been proposed. That method retrieved the values from the filtered sheet by retrieving columnMetadata and rowMetadata of the method of spreadsheet.get of Sheets API. In this case, the rows and columns hidden by the filter can be retrieved.

In this sample script, columnMetadata and rowMetadata of the method of spreadsheet.get of Sheets API are not used. The values are directly retrieved.

Spreadsheet

At first, the original sheet and the filtered sheet are shown as follows.

Original sheet

Filtered sheet

By the basic filter, the values less than 6 are shown. In this sample script, this displayed values are retrieved.

Sample script

When you run this script, please set the variables of spreadsheetId and sheetId.

function myFunction() {
  var spreadsheetId = "###"; // Please set Spreadsheet ID.
  var sheetId = "###"; // Please set Sheet ID.

  var url =
    "https://docs.google.com/spreadsheets/d/" +
    spreadsheetId +
    "/gviz/tq?tqx=out:csv&gid=" +
    sheetId +
    "&access_token=" +
    ScriptApp.getOAuthToken();
  var res = UrlFetchApp.fetch(url);
  var array = Utilities.parseCsv(res.getContentText());
  Logger.log(array);

  // SpreadsheetApp.getActiveSpreadsheet(); // This comment line is put for automatically detecting the scopes.
}

When the Spreadsheet is published, the values are the filtered values. In this sample script, I used this. And in this case, the access token is used. So the Spreadsheet is not required to be published and shared.

Result

[
  [1, 1, 1],
  [2, 2, 2],
  [3, 3, 3],
  [4, 4, 4],
  [5, 5, 5]
]

Other method

If Sheets API is used, the script is as follows. When you use this script, please enable Sheets API at Advanced Google services. When this script is used for above sample Spreadsheet, the same result with the above sample script can be retrieved.

function myFunction() {
  var spreadsheetId = "###"; // Please set Spreadsheet ID.
  var sheetName = "Sheet1"; // Please set Sheet name.

  var res = Sheets.Spreadsheets.get(spreadsheetId, {
    ranges: [sheetName],
    fields: "sheets/data"
  });
  var array = res.sheets[0].data[0].rowMetadata.reduce(function(ar, e, i) {
    if (!e.hiddenByFilter && res.sheets[0].data[0].rowData[i]) {
      ar.push(
        res.sheets[0].data[0].rowData[i].values.map(function(col) {
          return col.userEnteredValue[Object.keys(col.userEnteredValue)[0]];
        })
      );
    }
    return ar;
  }, []);
  Logger.log(array);
}

Note

Of course, when the endpoint is used, the values from the filtered sheet can be also retrieved by other languages.