How to hide tabs and protect them across multiple sheets (Google Sheets)

0
votes

I am using the current function (triggered my a menu button) to hide a range of columns. I wanted to add some script which will also protect this hidden range (so that only the owner can edit).

My question is, how can I add the protection element to this function and also force the function to run over all of my tabs excluding X, Y, Z etc... I have been trying to get this working for hours but just cant quite seem to crack it.

function hideResults() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  sheet.hideColumns(3,27); // B-D, three columns starting from 2nd 
  sheet.hideRows(4,1)
  sheet.hideRows(32,2)
}
1
javascriptgoogle-apps-scriptgoogle-sheets

1 Answers

1
votes

Explanation:

Two things you might need to modify in the following code:

  • Select the sheets you want to exclude from this operation:

    const sheets_to_exclude = ["Sheet2","Sheet4","X","Y"];

  • Select the ranges you want to protect:

    const ranges_to_protect = ["C:AC3","C4:AC4","C32:AC33"];

The rest of the code uses a forEach loop to iterate over the sheets and the ranges and the protection class to protect the selected ranges.

Solution:

function hideResults() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  //add here the sheets you want to exclude
  const sheets_to_exclude = ["Sheet2","Sheet4","X","Y"]; 
  // add the ranges you want to protect
  const ranges_to_protect = ["C:AC3","C4:AC4","C32:AC33"];
  sheets.forEach(sheet=>{
        if(!sheets_to_exclude.includes(sheet.getName())){
             sheet.hideColumns(3,27); 
             sheet.hideRows(4,1);
             sheet.hideRows(32,2);
             let ranges = sheet.getRangeList(ranges_to_protect).getRanges();
             ranges.forEach(rng=>{           
                let protection = rng.protect();
                let me = Session.getEffectiveUser();
                protection.addEditor(me);
                protection.removeEditors(protection.getEditors());
                if (protection.canDomainEdit()) {
                      protection.setDomainEdit(false);
                };            
             });  
        };
  }); 
}