Hide / show specific Sheets tabs for specific users on shared file

Question

My google doc should prompt a login box upon opening the Google Sheets file and, based on the password, it should display a specific tab. E.g. for "password1" it should allow the user to see only tab "Sheet1" and other sheets should be hidden. Similarly, for "password2" it should allow the user to see and work on tab "Sheet2" only.

I tried to run the following code, however it shows some errors.

function showLoginDialog() {
    var sheet3 = SpreadsheetApp.getActiveSheet('Sheet3');
    sheet3.hideSheet();
    var sheet2 = SpreadsheetApp.getActiveSheet('Sheet2');
    sheet2.hideSheet();
    var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet('Sheet1');      
    var ui = SpreadsheetApp.getUi();      
    var prompt = ui.prompt('Password','Enter Password',ui.ButtonSet.OK_CANCEL);      
    var response = prompt.getResponseText();      
    var button = prompt.getSelectedButton();  

    if(button==ui.Button.OK) {       
    if(response=='pwd3') {
            sheet3.activate();
        }//end of inner if
        if(response=='pwd2'){
            sheet2.activate();
        }
    }//end of main if
}//end of function

The Google Sheets file should ask for the password upon opening it, and it should display the respective sheet based on the password.

Answer 1

• You want to open only one of all sheets in the Spreadsheet by the inputted value from the dialog.
• For example, when the value of pwd2 is inputted, you want to open only "Sheet2".
• You want to open the dialog when the Spreadsheet is opened.

If my understanding is correct, how about this modification? Please think of this as just one of several answers.

The flow of the modified script is as follows.

1. Open only "Sheet1" as a default, when the Spreadsheet is opened.
2. Open a dialog.
3. When pwd2 is inputted, only "Sheet2" is opened.
4. When pwd3 is inputted, only "Sheet3" is opened.

If you want to open the dialog when the Spreadsheet is opened, please install OnOpen event trigger to RunOnOpen().

How to install OnOpen trigger:

• Open the script editor.
  • Edit -> Current project's triggers.
  • Click "Add Trigger".
  • Set RunOnOpen for "Choose which function to run".
  • Set "From spreadsheet" for "Select event source".
  • Set "On open" for "Select event type".

Modified script:

function RunOnOpen() {
  // Updated
  var openSheet = function(ss, sheets, sheet) {
    var s = ss.getSheetByName(sheet);
    s.showSheet();
    ss.setActiveSheet(s);
    for (var i = 0; i < sheets.length; i++) {
      if (sheets[i].getSheetName() != sheet) {
        sheets[i].hideSheet();
      }
    }
  };

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  openSheet(ss, sheets, "Sheet1"); // Open only "Sheet1" when this function is run.
  var ui = SpreadsheetApp.getUi();
  var prompt = ui.prompt('Password','Enter Password',ui.ButtonSet.OK_CANCEL);
  var response = prompt.getResponseText();
  var button = prompt.getSelectedButton();

  if (button == ui.Button.OK) {
    var sheet = "";
    if(response == 'pwd3') {
      openSheet(ss, sheets, "Sheet3");
    } else if(response == 'pwd2') {
      openSheet(ss, sheets, "Sheet2");
    }
  }
}

Note:

• In this modified script, when RunOnOpen() is manually run at the script editor, the script works. In order to do this, I didn't use the event object of OnOpen event trigger.

References:

• hideSheet()
• showSheet()
• Installable Triggers

If I misunderstood your question and this was not the result you want, I apologize.

Updated:

When I tested several times for this script, there are the case that "Sheet1" sheet and another sheet are opened. In order to avoid this situation, I updated the script. Could you please confirm above script again?

Edit:

• You want to make users use the specific sheet of Spreadsheet by inputting each password.
• You don't want to publish the script for users.
• You want to make several users use the Spreadsheet, simlutaneously.

If my understanding for your goal is correct, how about this workaround?

• Create each Spreadsheet for each user.
  • By this, the simultaneous access for the Spreadsheet can be achieved.
• Use Web Apps in order to open each Spreadsheet by inputting the password.

The flow of this workaround is as follows.

1. An user, who logged in Google, open Web Apps.
2. The user input the password and click "OK".
3. Checking the password at the server side and return the URL of Spreadsheet corresponding to the password.
4. Open the Spreadsheet from the URL.

By this flow, I think that top 3 aims can be achieved.

Sample script:

1. Before you use this script, please split the Spreadsheet for each sheet, and retrieve each URL of Spreadsheet.
2. When you use this, please deploy Web Apps with the following condition. If you want to know how to deploy Web Apps, please check this.
   • Execute the app as: Me.
   • Who has access to the app: Anyone
   • By this condition, users are required to log in to Google for accessing to Web Apps.

Users accesses to the URL of Web Apps.

Google Apps Script: Code.gs

function doGet() {
  return HtmlService.createHtmlOutputFromFile("index");
}

function selectSpreadsheet(value) {
  var url = "";
  if (value == "pwd2") {
    url = "### URL of Spreadsheet for password of pwd2 ###";
  } else if (value == "pwd3") {
    url = "### URL of Spreadsheet for password of pwd3 ###";
  }
  return url;
}

HTML & Javascript: index.html

<input type="text" id="value" >
<input type="button" value="ok" onclick="openSpreadsheet()">

<script>
function openSpreadsheet() {
  var value = document.getElementById("value").value;
  google.script.run.withSuccessHandler((url) => {
    if (url) window.open(url,'_top');
  }).selectSpreadsheet(value);
}
</script>

Note:

• When you modified the script, please deploy the project as new version. By this, the latest script is reflected to Web Apps. Please be careful this.
• I think that when the each Spreadsheet is shared for only each user, the security will be high.
• The maximum number of the simultaneous access for Web Apps is 30. Ref
• This is a simple sample script. So if you use this, please modify it for your situation.

• From TheMaster's somment, Although it's highly unlikely that users are able to retrieve the passwords from source code, I recommend using private functions and/or properties service for storing of passwords for a extra layer of security.

  • As a sample, you can use like below by saving the passwords and URLs to PropertiesService.

    function selectSpreadsheet(value) {
      return PropertiesService.getUserProperties().getProperty(value);
    }
    

References:

• Class google.script.run
• Web Apps
• Taking advantage of Web Apps with Google Apps Script

Answer 2

I don't think this is secure for the following reasons:

• Sheets' hidden/visible property is global and not restricted to a single user. If user1 and user2 login one after another at almost the same time, user1's sheet is visible after user1's login; As soon as user2 logs in, user1's sheet will be hidden for both users and user2's sheet will be visible to both user1 and user2

• It's easy to unhide sheets. user1 can unhide user2's sheet at any time he wishes to, provided he has edit access to the spreadsheet(which is needed, if you want to display the login dialog).