Skip to content

Instantly share code, notes, and snippets.

@LiamKarlMitchell
Created August 22, 2015 23:31
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save LiamKarlMitchell/81cef19a530261c4af93 to your computer and use it in GitHub Desktop.
Save LiamKarlMitchell/81cef19a530261c4af93 to your computer and use it in GitHub Desktop.
Google sheets toggle, hide, show rows.
function onOpen() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var entries = [{
name: "Toggle Rows",
functionName: "toggleRows"
},{
name: "Hide Rows",
functionName: "hideRows"
},{
name: "Show Rows",
functionName: "showRows"
}];
sheet.addMenu("Script", entries);
};
function toggleRows() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var maxRows = sheet.getMaxRows();
var r = sheet.getRange(1, 1, maxRows+1);
var values = r.getValues();
var hideRanges = [];
var showRanges = [];
var toggleValue = null;
var startRow = null;
var tmp;
var len = values.length;
var i;
for (i = 0; i < len; i++) {
tmp = values[i][0];
if (startRow === null && (tmp === '--' || tmp === '||')) {
startRow = i + 1;
toggleValue = tmp;
} else if (startRow !== null && tmp !== toggleValue) {
if (toggleValue === '--') {
hideRanges.push([startRow, (i + 1) - startRow]);
} else {
showRanges.push([startRow, (i + 1) - startRow])
}
if (tmp === '--' || tmp === '||') {
startRow = i + 1;
toggleValue = tmp;
} else {
startRow = null;
}
}
}
var customRange = null;
var range = null;
i = hideRanges.length;
while (i--) {
customRange = hideRanges[i];
range = sheet.getRange(customRange[0], 1, customRange[1]);
range.setValue('||');
sheet.hideRows(customRange[0], customRange[1]);
}
i = showRanges.length;
while (i--) {
customRange = showRanges[i];
range = sheet.getRange(customRange[0], 1, customRange[1]);
range.setValue('--');
sheet.showRows(customRange[0], customRange[1]);
}
};
function hideRows() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var maxRows = sheet.getMaxRows();
var r = sheet.getRange(1, 1, maxRows+1);
var values = r.getValues();
var startRow = null;
var tmp;
var len = values.length;
var i;
for (i = 0; i < len; i++) {
tmp = values[i][0];
if (startRow === null && (tmp === '--' || tmp === '||')) {
startRow = i + 1;
} else if (startRow !== null && (tmp !== '--' && tmp !== '||')) {
var numRows = (i + 1) - startRow;
sheet.getRange(startRow, 1, numRows).setValue('||');
sheet.hideRows(startRow, numRows);
startRow = null;
}
}
};
function showRows() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var maxRows = sheet.getMaxRows();
var r = sheet.getRange(1, 1, maxRows+1);
var values = r.getValues();
var startRow = null;
var tmp;
var len = values.length;
var i;
for (i = 0; i < len; i++) {
tmp = values[i][0];
if (startRow === null && (tmp === '--' || tmp === '||')) {
startRow = i + 1;
} else if (startRow !== null && (tmp !== '--' && tmp !== '||')) {
var numRows = (i + 1) - startRow;
sheet.getRange(startRow, 1, numRows).setValue('--');
sheet.showRows(startRow, numRows);
startRow = null;
}
}
};
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment