Google Sheets: Hiding Columns based on date in row 1

Question

I have no experience with scripting in Excel or Google Sheets, so I'm trying to branch out a bit and see if there's a solution to my problem. We use Google Sheets for a weekly calendar at our kitchen remodeling business. We organize the weeks from left to right and list the jobs we're currently working on in those columns. I would like to automatically hide all columns that have a date older than 4 weeks, so when the sheet opens, we're not starting with a date from a year ago. I can hide these columns manually each week, but when I do need to go back and look at previous weeks, I'm forced to unhide all thosecolumns and then highlight all of the columns I want to rehide. Having a script seems like the better solution.

Is there a way to have a script run every time the file is open so that we're always only displaying the previous 4 weeks and everything in the future? If so, would you be willing to help me understand how I might write that and get it working? Again, I'm a novice when it comes to anything beyond formulas, but very interested in learning more about the scripting capabilities.

Thank you!

Answer 1

Because you don't have a "database like" source it will be very difficult, but you can try to create a very complicated

QUERY()

you should filter the dates in another sheet (and you may face a dead end).

So I will suggest using this kind of structure and it will also allow you to make other kinds of filters (or Pivot Tables) in the future (maintainable and scalable).

Answer 2

With Apps Script via Tools->Script Editor, you could create a menu with an onOpen() function. The function in the menu (e.g. hidePast), would then need to check a given value in each column (to see what date that column refers to), and then flag it to be hidden or not. The onOpen function, because it is a "simple trigger", cannot do anything that requires "authorization" (such as interacting with non-local Spreadsheet data), hence the intermediate method. By creating a menu, you can make it easy for anyone using the spreadsheet to authorize and activate the function.

Example:

/* @OnlyCurrentDoc */
function onOpen() {
    SpreadsheetApp.getActive().addMenu("Date Tools",
        [{name:"Hide Past", functionName:"hidePast"},
         {name:"Show All", functionName:"showAll"}]);
}
function showAll() {
    var ss = SpreadsheetApp.getActive();
    var sheet = ss.getActiveSheet();
    sheet.unhideColumn(sheet.getDataRange());
    ss.toast("All columns unhidden.");
}
function hidePast() {
    var ss = SpreadsheetApp.getActive();
    var sheet = ss.getActiveSheet();
    // Acquire the 1st row of all used columns as an array of arrays.
    var datelist = sheet.getSheetValues(1, 1, 1, sheet.getLastColumn());

    // Drop the hours, minutes, seconds, etc. from today.
    var now = new Date();
    var today = new Date(Date.UTC(now.getUTCFullYear(), now.getUTCMonth(), now.getUTCDate()));

    // Inspect the datelist and compare to today. Start from the rightmost
    // column (assuming the dates are chronologically increasing).
    var col = datelist[0].length;
    while(--col >= 0) {
        var then = new Date(datelist[0][col]);
        if(then < today) {
            break;
        }
    }

    // Bounds check, and convert col into a 1-base index (instead of 0-base).
    if(++col < 1) return;

    // col now is the first index where the date is before today.
    // Increment again, as these are 2-column merged regions (and
    // the value is stored in the leftmost range). If not incremented,
    // (i.e. hiding only part of a merged range), spreadsheet errors will occur.
    sheet.hideColumn(sheet.getRange(1, 1, 1, ++col));
    ss.toast("Hid all the columns before today.");
}