This is an alternative implementation of @JSmith's answer, using the Sheets REST API to more efficiently hide & unhide a large number of sheets.
To use the Sheets REST API from Apps Script, you will first need to enable it, as it is an "advanced service."
The Sheets API approach enables you to work with the JavaScript representation of the data, rather than needing to interact with the Spreadsheet Service repeatedly (e.g. to check each sheet's name). Additionally, a batch API call is processed as one operation, so all visibility changes are reflected simultaneously, while the Spreadsheet Service's showSheet()
and hideSheet()
methods flush to the browser after each invocation.
var MENUSHEET = "Menu";
function onEdit(e) {
if (!e) return;
const edited = e.range,
sheet = edited.getSheet();
if (sheet.getName() === MENUSHEET && edited.getA1Notation() === "A2")
hideUnselected_(e.source, e.value);
}
function hideUnselected_(wb, choice) {
const initial = Sheets.Spreadsheets.get(wb.getId(), {
fields: "sheets(properties(hidden,sheetId,title)),spreadsheetId"
});
const pattern = new RegExp("^" + choice, "i");
const rqs = [];
initial.sheets.forEach(function (s) {
var rq = { fields: "hidden", properties: {sheetId: s.properties.sheetId} };
if (s.properties.title === MENUSHEET || pattern.test(s.properties.title))
rq.properties.hidden = false;
else
rq.properties.hidden = true;
if ((!!s.properties.hidden) !== (!!rq.properties.hidden))
rqs.push( { updateSheetProperties: rq } );
});
if (rqs.length) {
rqs.sort(function (a, b) { return a.updateSheetProperties.properties.hidden - b.updateSheetProperties.properties.hidden; });
Sheets.Spreadsheets.batchUpdate({requests: rqs}, initial.spreadsheetId);
}
}
There are a fair number of resources to be familiar with when working with Google's various REST APIs:
A little testing in a workbook with 54 sheets, in which I used the Sheets API to apply some changes and @JSmith's code to revert the changes, showed the API approach to be about 15x faster, as measured with console.time
& console.timeEnd
. API changes took from 0.4 to 1.1s (avg 1s), while the Spreadsheet Service method took between 15 and 42s (avg 20s).