Google Sheets Hide Rows Across Multiple Sheets

2
votes

I'm new to this whole script coding thing and am having trouble refining a script I currently have running. I found the code I'm using here. I was able to modify it to run on column E of my worksheet and it works beautifully on that one sheet.

I'd like to have it run on multiple sheets in the workbook but I'm not familiar enough with the coding language/syntax to make it work. I've tried pulling bits of code from other posts with a similar goal, only to have the script error on a part of the code that was working previously.

The code is as follows:

function onOpen() {
    var s = SpreadsheetApp.getActive()
        .getSheetByName('BV421');
    s.showRows(1, s.getMaxRows());
    s.getRange('E:E')
        .getValues()
        .forEach(function (r, d) {
            if (r[0] !== '' && r[0].toString()
                .charAt(0) == 0) s.hideRows(d + 1)
        });
}

I know the problem (at least in part) is with the .getSheetByName('BV421') but I'm not sure how to get it to select multiple sheets. I have other sheets (e.g., Alexafluor488, PerCP, FITC, etc.) that I would like to run the script on but not all of the sheets in the workbook.

Any help you can provide would be greatly appreciated!

2
google-apps-scriptgoogle-sheets
Possible duplicate of Google script to work across multiple sheets – Rubén
Related: How do I run Google script on multiple sheets? – Rubén
Hi Matt, you can get multiple sheets in GAS using SpreadsheetApp.getSheets(). This will return an array of all your sheets. You can then loop through them and manipulate them. Will put a bit of some code for you as an answer below. – New_2_Code

2 Answers

0
votes

Sample Code:

    function getSheets(){

    var ss = SpreadsheetApp.getActiveSpreadsheet(); //Gets the Spreadsheet
    var sheets = ss.getSheets(); //Gets all the sheets in the spreadsheet

      //For the number of sheets, do the following:
      for (var i = 0; i < sheets.length; i++){

      var currentSheet = sheets[i];

      Logger.log(currentSheet.getRange(1, 1, 1, 1).getValue());


      }

    }

If you don't want to start at the first sheet, change the value of "i". If you don't want the script to go over all sheets, change sheets.length to a fixed value.

Hope this helps.

Edit: Other members please feel free to edit this post to add links to documentation or just explain it better. (Have work I need to attend to.)

0
votes

Sample Code 2:

function onOpen() {
  var ss = SpreadsheetApp.getActive();
  ['BV421', 'Alexafluor488', 'PerCP', 'FITC'].forEach(function(sheetName) {
    var s = ss.getSheetByName(sheetName);
    // here do everything you need with the sheet s
    s.showRows(1, s.getMaxRows());
    s.getRange('E:E').getValues().forEach(function (r, d) {
      if (r[0] !== '' && r[0].toString().charAt(0) == 0) s.hideRows(d + 1);
    });
  });
}

You can repeat the code fragment for each sheet listed in array, if you get the sheet reference - s.