Skip to content

Instantly share code, notes, and snippets.

@carlbergman
Created July 16, 2015 09:38
Show Gist options
  • Save carlbergman/1a1dd786425bc9180b29 to your computer and use it in GitHub Desktop.
Save carlbergman/1a1dd786425bc9180b29 to your computer and use it in GitHub Desktop.
Get filtered/non-filtered values from Google Sheet with Google Apps Script
/**
* Get values in range
*
* @param {String} rangeA1 The range in A1 notation
*
* @return {Array} array with non-filtered values
*/
function getValues (rangeA1) {
// Get range
var range = SpreadsheetApp.getActiveSheet().getRange(rangeA1);
// Get values
var values = range.getValues();
// Prepare an array for the non-filtered values.
var v = [];
// Get the font color of each cell in our range.
var oldColors = range.getFontColors();
// Create a new array with different font colors.
var colors = [];
for (var r = 0; r < oldColors.length; r++) {
colors[r] = oldColors[r].slice(0);
// Modify the color slightly from black
// (I figure that's the most common color)
for (var c = 0; c < colors[r].length; c++) {
colors[r][c] = (oldColors[r][c] == '#000001') ? '#000002' : '#000001';
}
}
// Set the font colors to the generated colors.
range.setFontColors(colors);
// Get the font colors at this point.
// They should have changed. If they haven't that means
// the cell was filtered!
var newColors = range.getFontColors();
// Check filter status cell for cell
for (var r = 0; r < colors.length; r++) {
for (var c = 0; c < colors[r].length; c++) {
// Store the value if it wasn't filtered
if ((oldColors[r][c] != newColors[r][c])) {
v.push(values[r][c]);
}
}
}
// Change the background colors back to what they were initially
range.setFontColors(oldColors);
return v;
}
@carlbergman
Copy link
Author

This grabs the values and puts them in an array, but it should be quite easy to modify the code for other purposes.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment