Information Technology Grimoire

Version .0.0.1

IT Notes from various projects because I forget, and hopefully they help you too.

Google Sheets Expand Collapse Script

I wanted to expand/contract groups, like smartsheet - but did not want to pay for smartsheet or go to another platform.

It does rely on a specific structure in your sheet. Your columns should say:

StatusGroupWeekNotes

Additional Formulas

Column C will be the % complete of each task. If you have 4 tasks they will get equal weight, so 25% of their completion score will be used. If you have 5 tasks, 20%, etc.

In B3 (green), use this excel formula to calculate like this:

=IF(LEFT(C3,5)="Week ",
    AVERAGE(
        ARRAYFORMULA(
            IF(
                LEFT(
                    INDIRECT("B" & ROW()+1 & ":B" & 
                        MATCH(TRUE, INDEX(REGEXMATCH(C4:C99, "^(Week|Month)"),),0)+ROW()-1),
                    6
                )="Month ",
                "",
                IF(
                    ISBLANK(
                        INDIRECT("C" & ROW()+1 & ":C" & 
                            MATCH(TRUE, INDEX(REGEXMATCH(C4:C99, "^(Week|Month)"),),0)+ROW()-1)
                    ),
                    0,
                    VALUE(SUBSTITUTE(
                        INDIRECT("C" & ROW()+1 & ":C" & 
                            MATCH(TRUE, INDEX(REGEXMATCH(C4:C99, "^(Week|Month)"),),0)+ROW()-1),
                        "%",""
                    ))/100
                )
            )
        )
    ),
    ""
)

Your status, (in yellow, A2) has the following excel formula to give equal weight to each Summary score in Column B that matches that month. So if you have 3 summary scores, they would get 33.33% weight each and that will account for the yellow score. A blank is considered 0% in the calculations.

=AVERAGE(FILTER(
  INDIRECT("B" & (ROW()+1) & ":B" & 
    IF(IFERROR(MATCH("Month*", INDIRECT("B" & (ROW()+1) & ":B1000"), 0), 0) = 0,
      1000,
      INDEX(MATCH("Month*", INDIRECT("B" & (ROW()+1) & ":B1000"), 0) + ROW(), 1)-1
    )),
  REGEXMATCH(
    INDIRECT("C" & (ROW()+1) & ":C" & 
      IF(IFERROR(MATCH("Month*", INDIRECT("B" & (ROW()+1) & ":B1000"), 0), 0) = 0,
        1000,
        INDEX(MATCH("Month*", INDIRECT("B" & (ROW()+1) & ":B1000"), 0) + ROW(), 1)-1
      )),
    "^Week"
  )
))

The Google App Script

  • Open the sheet
  • go to “Extensions”
  • click on App Script
  • Create New Script/Overwrite Existing
  • Save
  • Run
  • Authorize
  • Reload the main sheet

A new menu item will appear called “Visibility”. Click on a main or week and hide/show.

function show1() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var cell = ss.getActiveCell();
  var row = cell.getRow();
  var col = cell.getColumn();
  
  var numRows = getRowsToProcess(sheet, row, col);
  
  if (numRows > 0) {
    sheet.showRows(row + 1, numRows);
  }
}

function hide1() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var cell = ss.getActiveCell();
  var row = cell.getRow();
  var col = cell.getColumn();
  
  var numRows = getRowsToProcess(sheet, row, col);
  
  if (numRows > 0) {
    sheet.hideRows(row + 1, numRows);
  }
}

function expandAll() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var lastRow = sheet.getLastRow();
  
  // Show all rows
  sheet.showRows(1, lastRow);
}

function collapseAll() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var lastRow = sheet.getLastRow();
  
  // Process each row
  var i = 1;
  while (i <= lastRow) {
    var groupValue = sheet.getRange(i, 2).getValue().toString();
    
    // If it's a Month row, hide everything until next Month or end
    if (groupValue.match(/Month \d+/)) {
      var numRows = getRowsToProcess(sheet, i, 2);
      if (numRows > 0) {
        sheet.hideRows(i + 1, numRows);
      }
      i += numRows + 1;
    } else {
      i++;
    }
  }
}

function getRowsToProcess(sheet, currentRow, currentCol) {
  var lastRow = sheet.getLastRow();
  
  // If clicked on a Month row (check if value contains ""Month"")
  if (sheet.getRange(currentRow, 2).getValue().toString().match(/Month \d+/)) {
    // Look for next Month or end of sheet
    for (var i = currentRow + 1; i <= lastRow; i++) {
      var value = sheet.getRange(i, 2).getValue().toString();
      if (value.match(/Month \d+/)) {
        return i - currentRow - 1;
      }
    }
    return lastRow - currentRow;
  }
  
  // If clicked on a Week row (check if value starts with ""Week"")
  if (sheet.getRange(currentRow, 3).getValue().toString().match(/^Week/)) {
    // Look for next Week/Month or end of section
    for (var i = currentRow + 1; i <= lastRow; i++) {
      var weekValue = sheet.getRange(i, 3).getValue().toString();
      var groupValue = sheet.getRange(i, 2).getValue().toString();
      if (weekValue.match(/^Week/) || groupValue.match(/Month \d+/)) {
        return i - currentRow - 1;
      }
    }
    return lastRow - currentRow;
  }
  
  return 0;
}

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Visibility')
    .addItem('Show Section', 'show1')
    .addItem('Hide Section', 'hide1')
    .addSeparator()
    .addItem('Expand All', 'expandAll')
    .addItem('Collapse All', 'collapseAll')
    .addToUi();
}
Last updated on 3 Feb 2025
Published on 3 Feb 2025