How to password protect a google sheet

pervis Published at Dev

pervis

I have a Google Sheet with data that is protected - the user can't edit the sheet. When the AskUser function is ran, a pop-up box appears that asks the user if they wish to "unprotect" the sheet. When they click "No", they will be told nothing will happen. When they click "Yes", they will be told they need to submit a password. I have three problems:

I don't know how to set a password.
I don't know how bring up a dialogue box where the user can submit a password.
I don't know how to make it check if it's the correct password.

I've tried downloading pre-password protected sheets and checking the code, but they use a weird HTML format. Here's what I have so far:

  SpreadsheetApp.getUi() 
      .createMenu('Custom Menu')
      .addItem('Show alert', 'showAlert')
      .addToUi();
}

function AskUser() {
  var ui = SpreadsheetApp.getUi(); 

  var result = ui.alert(
     'Worksheet Protected!',
     'Do you want to unprotect the worksheet?',
      ui.ButtonSet.YES_NO);


  if (result == ui.Button.YES) {
    //This is where I want the password dialogue box prompt
    ui.alert('The worksheet will be unprotected after you submit the right password.');
  } else {

    ui.alert('The worksheet will not be unprotected.');
  }
}

I'm completely new to Javascript and Google Sheets.

WilliamNHarvey

Setting a password

Use PropertiesService to store a password

function setPassword(password) {
  let prop = PropertiesService.getUserProperties();
  prop.setProperty("sheetpassword", password);
}

Bringing up password form

Create an html form in a file, eg. passwordform.

Then when you want to show the form, call createHtmlOutputFromFile

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
spreadsheet.show(HtmlService.createHtmlOutputFromFile('passwordform'));

On form submit, call the check password function

onclick='google.script.run.checkPassword(document.getElementById("passwordInput"));'

Checking password

Get the property from PropertiesService and see if it equals

function checkPassword(password) {
  let prop = PropertiesService.getUserProperties();
  if (prop.getProperty("sheetpassword") === null || prop.getProperty("sheetpassword") !== password) {
    return false; // Doesn't match DB
  } 
}

Collected from the Internet

Please contact [email protected] to delete if infringement.