Skip to content

Instantly share code, notes, and snippets.

@theicfire
Created January 21, 2014 07:47
Show Gist options
  • Save theicfire/8535877 to your computer and use it in GitHub Desktop.
Save theicfire/8535877 to your computer and use it in GitHub Desktop.
Hide Old Rows for Google Docs using Apps Script
/**
* Looks for rows with old dates, and hides them if so.
* It finds the date column my checking the first column for the word 'Date'.
*/
function hideOldRows(colNum) {
var sheet = SpreadsheetApp.getActiveSheet();
var header = sheet.getRange(1, 1, 1, sheet.getLastColumn());
var colNum = header.getValues()[0].indexOf('Date') + 1;
if (colNum === 0) {
Logger.log(header.getValues());
throw Error('Could not find the word Date on the first row');
}
var rows = sheet.getRange(1, colNum, sheet.getLastRow(), 1);
var numRows = rows.getNumRows();
var values = rows.getValues();
for (var i = 0; i <= numRows - 1; i++) {
var row = values[i];
if (isDate(row[0]) && isOld(row[0])) {
sheet.hideRows(i + 1);
}
}
};
function isDate(dateString) {
return (new Date(dateString)) != "Invalid Date"; // notice not !==
}
function isOld(dateString) {
assert(isDate(dateString));
var inDate = (new Date(dateString)).getTime();
var yesterday = new Date();
yesterday.setDate(yesterday.getDate() - 1);
return yesterday > inDate;
}
function assert(b, msg) {
msg = msg || "default message";
if (!b) {
throw Error(msg)
}
}
function test() {
assert(isOld('Fri Jan 04 00:00:00 GMT-05:00 2013'));
assert(!isOld('Fri Jan 04 00:00:00 GMT-05:00 2018'));
}
/**
* Adds a custom menu to the active spreadsheet, containing a single menu item
* for invoking the readRows() function specified above.
* The onOpen() function, when defined, is automatically invoked whenever the
* spreadsheet is opened.
* For more information on using the Spreadsheet API, see
* https://developers.google.com/apps-script/service_spreadsheet
*/
function onOpen() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var entries = [{
name : "Hide Old Rows",
functionName : "hideOldRows"
}];
sheet.addMenu("Scripts", entries);
};
@kakafon
Copy link

kakafon commented Mar 3, 2015

Hello. I have tried this script in one of my spreadsheets and it works great. But if i want the script to work for all sheets in a spreadsheet, how would I do that? I am new to github, so please excuse me if this isn't the right place for newbee questions. I will then try elsewhere.

What I have tried, is to merge your script whith the second script ("edit:working") in this tread:
http://stackoverflow.com/questions/16992771/how-can-i-run-a-google-script-automatically-on-multiple-sheets-in-the-same-sprea

But I haven't succeded yet. Thank you in advance for any help.

@tsroque
Copy link

tsroque commented Mar 3, 2017

Hi theicfire!

How would you do the above but minus 7 days? So in other words, I'd like to show rows with "TODAY-7 Days".

Thank you!

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