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:
Status | Group | Week | Notes |
---|
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();
}