I am currently running the following to hide columns depending on the value of a specific cell. It works, but I'm wondering if there's a more efficient way to achieve this? I notice there is 2 commands hideColumn and hideColumns which suggests there's a way of hiding more than one column in a single command:
resultsSheet.hideColumns(11);
resultsSheet.hideColumns(18);
resultsSheet.hideColumns(19);
resultsSheet.showColumns(26);
resultsSheet.showColumns(27);
resultsSheet.showColumns(28);
resultsSheet.showColumns(29);
I believe your goal as follows.
You want to reduce the process cost of the following script.
resultsSheet.hideColumns(11);
resultsSheet.hideColumns(18);
resultsSheet.hideColumns(19);
resultsSheet.showColumns(26);
resultsSheet.showColumns(27);
resultsSheet.showColumns(28);
resultsSheet.showColumns(29);
hideColumns
and showColumns
are used, the arguments are columnIndex, numColumns
. In this case, when the columns you want to hide and show are continuously existing like the column "A", "B" and "C", you can achieve this using one hideColumns
and showColumns
. But when the columns you want to hide and show are scattered, multiple hideColumns
and showColumns
are required to be used.hideColumns
and showColumns
can be used by one API call.The sample script is as follows.
Please copy and paste the following script to the script editor of Spreadsheet you want to use. Before you use this script, please enable Sheets API at Advanced Google services. And, please set the sheet name.
function myFunction() {
const hideColumns = [11, 18, 19];
const showColumns = [26, 27, 28, 29];
const sheetName = "Sheet1";
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheetId = ss.getSheetByName(sheetName).getSheetId();
const requests = [];
// Create requests for the hide columns.
if (hideColumns.length > 0) {
hideColumns.forEach(c =>
requests.push({ updateDimensionProperties: { properties: { hiddenByUser: true }, range: { sheetId: sheetId, dimension: "COLUMNS", startIndex: c - 1, endIndex: c }, fields: "hiddenByUser" } })
);
}
// Create requests for the show columns.
if (showColumns.length > 0) {
showColumns.forEach(c =>
requests.push({ updateDimensionProperties: { properties: { hiddenByUser: false }, range: { sheetId: sheetId, dimension: "COLUMNS", startIndex: c - 1, endIndex: c }, fields: "hiddenByUser" } })
);
}
// Request to Sheets API using the created requests.
if (requests.length > 0) Sheets.Spreadsheets.batchUpdate({requests: requests}, ss.getId());
}
hideColumns
is declared, the columns are hidden using hideColumns
. when only showColumns
is declared, the columns are shown using showColumns
. When both hideColumns
and showColumns
are declared, the columns are hidden and shown using hideColumns
and showColumns
.
hideColumns
and showColumns
are from your script