Created
August 22, 2015 23:31
-
-
Save LiamKarlMitchell/81cef19a530261c4af93 to your computer and use it in GitHub Desktop.
Google sheets toggle, hide, show rows.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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