Hide all but today's columns on four sheets (Google Spreadsheets script)

Question

Goal: I'm trying to create a behavior tracker for four classes in Google Spreadsheets. The tracker has nine sheets: Class7A, Class7B, Class8A, Class8B, and Mon-Fri summary sheets. The goal was for each ClassXX sheet to have behavior tracking information for an entire week, but for the default view to show only the current day's information.

Attempts: During initial workup (with only the Class7A sheet created), I got this to work using a modification of the script found here (Thank you Jacob Jan Tuinstra!): Optimize Google Script for Hiding Columns

I modified it to check the value in the third row of each column (which held a 1 for Monday, 2 for Tuesday, etc), and if it did not match the numerical equivalent for the day of the week (var d = new Date(); var n = d.getDay();), then it would hide that column. This process was somewhat slow - I'm assuming because of the iterating through each column - but it worked.

Quite excited, I went ahead and added the rest of the sheets, and tried again - but the code as written, seems to affect only the current sheet. I tried modifying it by replacing var sheet = ss.getSheets()[0]; with for script that iterated through the columns, until i>4 (I've since lost that piece of code), with no luck.

Deciding to go back and try adapting the original version of the script to instead explicitly run multiple times for each named sheet, I found the that script no longer seems to work at all. I get various version of "cannot find XX function in sheet" or "cannot find XX function in Range."

Source: A shared version (with student info scrubbed) can be found here: https://docs.google.com/spreadsheets/d/1OMq4a4_Gh_xyNk_IRy-mwJn5Hq36RXmdAzTzx7dGii0/edit?usp=sharing (editing is on).

Stretch Goal: Ultimately, I need to get this to reliably show only the current day's columns (either through preset ranges (same for each sheet), or the 1-5 values), and I need it to do so for all four ClassXX sheets, but not the summary pages (and preferably more quickly than the iterations). If necessary, I can remove the summary pages and set them up externally, but that's not my first preference. I would deeply appreciate any help with this; so far my attempts have seemed to only take me backwards.

Thanks!

Current code:

function onOpen() {
  // get active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // create menu
  var menu = [
    {name: "Show Today Only", functionName: "hideColumn"},
    {name: "Show All Days", functionName: "showColumn"},
    {name: "Clear Week - WARNING will delete all data", functionName: "clearWeek"}
  ];

  // add to menu
  ss.addMenu("Show Days", menu);
}
var d = new Date();
var n = d.getDay();


function hideColumn() {

  // get active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // get first sheet
  var sheet = ss.getSheets()[0];

  // get data
  var data = sheet.getDataRange();

  // get number of columns
  var lastCol = data.getLastColumn()+1;

  Logger.log(lastCol);

  // itterate through columns
  for(var i=1; i<lastCol; i++) {
     if(data.getCell(2, i).getValue() != n) {
        sheet.hideColumns(i);
     }
  }
}


function showColumn() {
  // get active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // get first sheet
  var sheet = ss.getSheets()[0];

  // get data
  var data = sheet.getDataRange();

  // get number of columns
  var lastCol = data.getLastColumn();

  // show all columns
  sheet.showColumns(1, lastCol);
}

Robin Gertenbach Robin Gertenbach · Accepted Answer · 2016-09-21T20:32:18

I cannot recreate the problem of the script not working at all, it's working fine for Class7A so that part is working fine.

So let's look at the two other problems:

Applying this to all Sheets
Speeding up the script

First let's create some globals we use in both functions

var d = new Date();
var n = d.getDay();
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheetNames = ss.getSheets().map(function(sheet) {return sheet.getName();});
var classSheets = sheetNames.filter(function(sheetName) {return sheetName.match("Class")});

Now we can iterate over classSheets and get the sheet by name and hide columns in each.
However hiding each individual column is very slow.
The sheet is built very structured, every week has 12 columns (except for friday which doesn't have the grey bar), so we can just calculate the ranges we want to hide.

function hideColumn() {
  classSheets.map(function(sheetName){
    var sheet = ss.getSheetByName(sheetName);

    if (n == 1) {
      // Hide everything after the first three columns + Monday
      sheet.hideColumns(3 + 11, 12 * 4); 
    } else if (n == 5) { 
      // Hide everything to the left except the leftmost three columns 
      sheet.hideColumns(3, 4 * 12);
    } else {
      // Hide everything left of the current day
      sheet.hideColumns(3, (n - 1) * 12);
      // Hide everything after the current day
      sheet.hideColumns(3 + n * 12, (5 - n) * 12 - 1);
    }
  });
}

Lastly we can shorten showColumn

function showColumn() {
  classSheets.map(function(sheetName){
    var sheet = ss.getSheetByName(sheetName);
    var lastCol = sheet.getLastColumn();
    sheet.showColumns(1, lastCol);
  });
}

Hide all but today's columns on four sheets (Google Spreadsheets script)

1 Answers