How to hide row in app script
Hiding and Deleting Rows and Columns on Google Spreadsheet using Google Apps Script Show These are the sample scripts for hiding and deleting rows and columns on Google Spreadsheet using Google Apps Script. I sometimes see the questions for hiding and deleting rows and columns on Spreadsheet at Stackoverflow. So here, I would like to introduce the sample scripts for this. In this case, when the process costs of
the scripts created by using Spreadsheet service and Sheets API are compared, the cost of script created by Sheets API is much lower than that of script created by Spreadsheet service. So when the rows and columns of your Spreadsheet is large and you can use Sheets API, I recommend to use Sheets API. As the sample situation, the following sample Spreadsheet is used. The 1st row and the column "A" are used as the header row and header column, respectively. Each header has the checkboxes. This sample Spreadsheet is used for the following scripts. Hide rowsThe rows of checked checkboxes of the column "A" are hidden. Sample script 1In this sample script, // Hidden rows using Spreadsheet service. function hideRows_with_SpreadsheetService() { const checkBoxColumn = 1; const sheetName = "Sheet1"; const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName); for (let r = 2; r <= sheet.getLastRow(); r++) { const range = sheet.getRange(r, checkBoxColumn); if (range.isChecked()) sheet.hideRow(range); } } Sample script 2In this sample script, // Hidden rows using Sheets API. function hideRows_with_SheetsAPI() { const checkBoxColumn = 1; const sheetName = "Sheet1"; const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheet = ss.getSheetByName(sheetName); const spreadsheetId = ss.getId(); const sheetId = sheet.getSheetId(); const values = sheet .getRange(2, checkBoxColumn, sheet.getLastRow() - 1) .getValues(); const requests = values.reduce((ar, [a], i) => { if (a === true) { const offset = i + checkBoxColumn; ar.push({ updateDimensionProperties: { range: { sheetId: sheetId, startIndex: offset, endIndex: offset + 1, dimension: "ROWS", }, properties: { hiddenByUser: true }, fields: "hiddenByUser", }, }); } return ar; }, []); Sheets.Spreadsheets.batchUpdate({ requests: requests }, spreadsheetId); } Hide columnsThe columns of checked checkboxes of the 1st row are hidden. Sample script 1In this sample script, // Hidden columns using Spreadsheet service. function hideColumns_with_SpreadsheetService() { const checkBoxRow = 1; const sheetName = "Sheet1"; const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName); for (let c = 2; c <= sheet.getLastColumn(); c++) { const range = sheet.getRange(checkBoxRow, c); if (range.isChecked()) sheet.hideColumn(range); } } Sample script 2In this sample script, // Hidden columns using Sheets API. function hideColumns_with_SheetsAPI() { const checkBoxRow = 1; const sheetName = "Sheet1"; const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheet = ss.getSheetByName(sheetName); const spreadsheetId = ss.getId(); const sheetId = sheet.getSheetId(); const values = sheet .getRange(checkBoxRow, 2, 1, sheet.getLastColumn() - 1) .getValues()[0]; const requests = values.reduce((ar, e, i) => { if (e === true) { const offset = i + checkBoxRow; ar.push({ updateDimensionProperties: { range: { sheetId: sheetId, startIndex: offset, endIndex: offset + 1, dimension: "COLUMNS", }, properties: { hiddenByUser: true }, fields: "hiddenByUser", }, }); } return ar; }, []); Sheets.Spreadsheets.batchUpdate({ requests: requests }, spreadsheetId); } Delete rowsThe rows of checked checkboxes of the column "A" are deleted. As an important point, when the rows are deleted, the row index is changed. By this, when the rows are deleted from the bottom row, the script becomes simpler. Sample script 1In this sample script, // Delete rows using Spreadsheet service. function deleteRows_with_SpreadsheetService() { const checkBoxColumn = 1; const sheetName = "Sheet1"; const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName); for (let r = sheet.getLastRow(); r >= 2; r--) { const range = sheet.getRange(r, checkBoxColumn); if (range.isChecked()) sheet.deleteRow(r); } } Sample script 2In this sample script, // Delete rows using Sheets API. function deleteRows_with_SheetsAPI() { const checkBoxColumn = 1; const sheetName = "Sheet1"; const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheet = ss.getSheetByName(sheetName); const spreadsheetId = ss.getId(); const sheetId = sheet.getSheetId(); const values = sheet .getRange(2, checkBoxColumn, sheet.getLastRow() - 1) .getValues(); const requests = values .reduce((ar, [a], i) => { if (a === true) { const offset = i + checkBoxColumn; ar.push({ deleteDimension: { range: { sheetId: sheetId, startIndex: offset, endIndex: offset + 1, dimension: "ROWS", }, }, }); } return ar; }, []) .reverse(); Sheets.Spreadsheets.batchUpdate({ requests: requests }, spreadsheetId); } Delete columnsThe columns of checked checkboxes of the 1st row are deleted. As an important point, when the columns are deleted, the column index is changed. By this, when the columns are deleted from the end of column, the script becomes simpler. Sample script 1In this sample script, // Delete columns using Spreadsheet service. function deleteColumns_with_SpreadsheetService() { const checkBoxRow = 1; const sheetName = "Sheet1"; const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName); for (let c = sheet.getLastColumn(); c >= 2; c--) { const range = sheet.getRange(checkBoxRow, c); if (range.isChecked()) sheet.deleteColumn(c); } } Sample script 2In this sample script, // Delete columns using Sheets API. function deleteColumns_with_SheetsAPI() { const checkBoxRow = 1; const sheetName = "Sheet1"; const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheet = ss.getSheetByName(sheetName); const spreadsheetId = ss.getId(); const sheetId = sheet.getSheetId(); const values = sheet .getRange(checkBoxRow, 2, 1, sheet.getLastColumn() - 1) .getValues()[0]; const requests = values .reduce((ar, e, i) => { if (e === true) { const offset = i + checkBoxRow; ar.push({ deleteDimension: { range: { sheetId: sheetId, startIndex: offset, endIndex: offset + 1, dimension: "COLUMNS", }, }, }); } return ar; }, []) .reverse(); Sheets.Spreadsheets.batchUpdate({ requests: requests }, spreadsheetId); } Note
References
|