1

I am aware of the protect sheet/range function in google spreadsheet but what I want to do is to hide sheets completely from certain users. I have found an answer to this with the code below. I am able to hide certain tabs/sheets to the specified user automatically upon opening the spreadsheet but that doesn't stop them from unhiding the tabs/sheets again. Any recommendations to restrict them from unhiding those tabs?

function onOpen() {
  var adminUsers = ['sample@google.com'];
  var Users = ['sample@gmail.com'];

  if (adminUsers.indexOf(Session.getEffectiveUser().getEmail()) >= 0) {
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SETTINGS').showSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('INSTRUCTION').showSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('EMPLOYEES').showSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('LEAVE').showSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('TEAM DASHBOARD').showSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('EMS').showSheet();
  }

  if (Users.indexOf(Session.getEffectiveUser().getEmail()) >= 0) {
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SETTINGS').hideSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('EMPLOYEES').hideSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('LEAVE').hideSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('EMS').hideSheet();

    }
}
Rubén
  • 34,714
  • 9
  • 70
  • 166
Zyre Soriano
  • 575
  • 4
  • 13
  • 25
  • 1
    Use a better security model than hidden sheets - such as removing edit access from people you don't want to modify your workbook. – tehhowch May 04 '18 at 12:49
  • You should check this out https://developers.google.com/apps-script/reference/spreadsheet/protection – Liora Haydont May 04 '18 at 12:52
  • Yup I already set a list of people who have edit access and those who doesn't and protected my sheets as well. The only thing I want right now is for those who don't have edit access to not see the hidden sheets or unhide them at all. basically I am now able to hide the specified sheets automatically every time the sheet is opened and my only concern now is they are still able to unhide them. – Zyre Soriano May 04 '18 at 13:10
  • If you want Viewers to only ever be able to see certain sheets, and never be able to see sheets that would normally be hidden, you should consider using IMPORTRANGE to get data from certain tabs into a new file, and use that new file for Viewers. Then on the original sheet only allow Editors and remove all Viewers. – Mr Shane Nov 05 '22 at 03:17

1 Answers1

-1

Viewers can't make any change to Google Sheet spreadsheets. In order to prevent that a viewer make a copy to get access to hidden sheets and rows, block the spreadsheet to be printed/copied/downloaded. For details see Stop, limit, or change sharing.

It's worth to note that hiding or showing a sheet will make it effective to all users who are viewing the spreadsheet.

Rubén
  • 34,714
  • 9
  • 70
  • 166