google-sheetsgoogle-apps-script

How to Show/Hide rows in google sheets using a script?


I'm trying to make a calendar. I want rows 2-37 hidden if someone presses a shape in January, 39-74 if they press it in February etc. The script should take into account the sheet as well. So it only hides rows 2-37 on the active sheet not the sheet next to it.

A link or checkbox would be better than a shape but not sure if that's possible.

Here's the spreadsheet

current attempts

function toggleRowVisibility(startRow, endRow) {
 var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

 for (var i = startRow; i <= endRow; i++) {
var row = sheet.getRange(i + ':' + i);

if (row.isHidden()) {
  row.setHidden(false); // Unhide the row if it's currently hidden
} else {
  row.setHidden(true); // Hide the row if it's currently visible
}
}} toggleRowVisibility('2:37');

function toggleRows() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

for (var i = 2; i <= 37; i++) {
var row = sheet.getRange(i, 1);

if (sheet.isRowHidden(i)) {
  sheet.showRows(i); // If row is hidden, show it
} else {
  sheet.hideRows(i); // If row is visible, hide it
}}}

Solution

  • Show/Hide rows in google sheets using a script

    Assigning parameters in Assign Script is not possible but here is the modified version of your code.

    // Toggle Shape Button Assigned Script
    function toggleRowVisibilityJanuary() {
      var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
      sheet.isRowHiddenByUser(2) ? sheet.showRows(2, 36) : sheet.hideRows(2, 36);
    } 
    

    I use Ternary condition to check if the Row 2 is hidden and return a boolean if it is true then it will Show Rows from Row 2 to 36 and if it's false then it will Hide Rows Row 2 to 36.


    Here's the output

    Sample Output


    Reference: