CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Excelsson
Excelsson

Reputation: 195

Google Sheets Appscript pop up prompt response and hide sheets until response is correct

I'm looking for a script to launch a popup prompting the user for a code on startup, and it will keep all the sheets hidden but one until the user enters the correct code, if the user does not enter the correct code it will keep launching the user prompt and keep all the sheets hidden, pretty much a turn around for a password protection, this is what I have so far:

function onOpen() {


function hideAllSheetsExcept('Sheet1') {
  var sheets=SpreadsheetApp.getActiveSpreadsheet().getSheets();

  for(var i =0;i<sheets.length;i++){
    Logger.log(i);
    if(sheets[i].getName()!=sheetName){
      sheets[i].hideSheet();
    }
  }
var quest = Browser.inputBox("Enter the requested value");
if quest == '12345' then
  sheets[i].showSheet();
}
}

Upvotes: 0

Views: 486

Answers (1)

ziganotschka
ziganotschka

Reputation: 26836

On the open event of the document, you want to keep asking the user for the password until it's correct

Your code already goes into the right direction but needs the following modifications:

  • replace the simple trigger onOpen through an installable one. Browser.inputBox will not run on simple trigger.
  • Replace your if statement that checks for the correct password by a while loop that keeps asking for the password as long as it is not correct.
  • To show all your sheets, you need to loop through the same in the same way as you did when hiding them.
  • Your function hideAllSheetsExcept should not be nested in onOpen, but only be called from there.

See the following sample of how you can implement the desired functionality, when binding to the function called openSheets an installable onOpen trigger:

function openSheets(){   
  hideAllSheetsExcept('Sheet1')
}

function hideAllSheetsExcept(sheetName) {
  var sheets=SpreadsheetApp.getActiveSpreadsheet().getSheets();
  for(var i =0;i<sheets.length;i++){
    Logger.log(i);
    if(sheets[i].getName()!=sheetName){
      sheets[i].hideSheet();
    }
  }
  var quest="";
  while (quest!='12345'){
    quest = Browser.inputBox("Enter the requested value");
  }
  for(var i =0;i<sheets.length;i++){
    sheets[i].showSheet();   
  }
}

Upvotes: 2

Related Questions