Password for Sheet

[Robert Lee]

Hello, I have a problem that I'm concerned about: how to set a password for Google Sheet. Besides setting permissions with the "Share" button, is there any solution for sheets with passwords? Thanks.

[Michael O'Shaughnessy]

Hello Robert,

Please give us a little more info... are you trying to keep people from "opening the sheet"?  That is done with permissions as you know.  Are you trying to "protect a range"?  That can be done.  

While you are giving us some more information, take a look at this site:

https://support.google.com/docs/answer/1218656?hl=en&co=GENIE.Platform%3DDesktop#zippy=%2Cprotect-a-range-or-sheet

It may meet your needs.

On Mon, Nov 24, 2025 at 3:58 AM Robert Lee <leminhng...@gmail.com> wrote:

Hello, I have a problem that I'm concerned about: how to set a password for Google Sheet. Besides setting permissions with the "Share" button, is there any solution for sheets with passwords? Thanks.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/af6482b6-0297-424e-8c8b-62330cc68c15n%40googlegroups.com.

[Robert Lee]

Hi Michael, 

Actually I know there is "protect range" and "hide sheet". However, I want to build a feature similar to a login page where the user has to input the correct password before editing anything on the sheet. 

This helps the community when you can "share anyone with a link". Just input the correct password. We don't need to share one by one. That consumes time. 

I really want to dig in and learn more if there is any way I can use appscript for this. I think the google sheets community will be very happy with this feature.

Best regards,

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAHNYQLhaoNhC5Ep5fXzQ_AKGB9sZFEuoOBbi%3D8NB5AtOvLxT%2BA%40mail.gmail.com.

--

[Michael O'Shaughnessy]

Hello Robert,

I sort of figured you were aware of the protect and hide.  

With the help of Claude.ai i have some intriguing code that you might want to take a look at.  I have to play around with it some more but it proves to be interesting.  

Here it is:

// Set your password here
const CORRECT_PASSWORD = "your_password_here";

function onOpen() {
  // Lock the sheet immediately when opened
  lockAllSheets();
 
  // Add a custom menu for unlocking
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('🔒 Sheet Access')
      .addItem('Unlock Sheet', 'showPasswordPrompt')
      .addItem('Lock Sheet', 'lockAllSheets')
      .addToUi();
}

function lockAllSheets() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const me = Session.getEffectiveUser();
 
  spreadsheet.getSheets().forEach(function(sheet) {
    // Remove existing protections first
    const protections = sheet.getProtections(SpreadsheetApp.ProtectionType.SHEET);
    protections.forEach(function(protection) {
      if (protection.canEdit()) {
        protection.remove();
      }
    });
   
    // Add new protection
    const protection = sheet.protect().setDescription('Password Protected');
    protection.setWarningOnly(false);
   
    // Remove all editors
    protection.removeEditors(protection.getEditors());
    if (protection.canDomainEdit()) {
      protection.setDomainEdit(false);
    }
  });
}

function showPasswordPrompt() {
  const ui = SpreadsheetApp.getUi();
  const response = ui.prompt(
    'Password Required',
    'Enter password to unlock and edit this sheet:',
    ui.ButtonSet.OK_CANCEL
  );
 
  if (response.getSelectedButton() == ui.Button.OK) {
    const enteredPassword = response.getResponseText();
   
    if (enteredPassword === CORRECT_PASSWORD) {
      unlockAllSheets();
      ui.alert('✅ Access Granted', 'The sheet is now unlocked. You can edit freely.', ui.ButtonSet.OK);
    } else {
      ui.alert('❌ Access Denied', 'Incorrect password. The sheet remains protected.', ui.ButtonSet.OK);
    }
  }
}

function unlockAllSheets() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 
  spreadsheet.getSheets().forEach(function(sheet) {
    const protections = sheet.getProtections(SpreadsheetApp.ProtectionType.SHEET);
    protections.forEach(function(protection) {
      if (protection.canEdit()) {
        protection.remove();
      }
    });
  });
}

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAA2EpLDpzJ5ZJ5nmMvNzS%2B_-o5toVKqBWuxbRDs_Ra5pzGUeEw%40mail.gmail.com.

[Robert Lee]

Hi Michael, 

Thanks for your response !. I've tried to use your script in my sheet. It's work perfectly with owner's account (The owner of the sheet). 

However, when I try to share it for another account, It's can't be triggered the "onOpen" function. 

I think The problem is "protection feature". Once the owner has added protection, only the owner can remove the protection from the sheet.   

If you need understand, please see my sheet below for further information.

My Sheet: https://docs.google.com/spreadsheets/d/1KJHwlqK9oc0HAB-gPTlahGX92AyWDTgRJwNUzqu2MEY/edit?gid=893719367#gid=893719367

Thanks & Best Regard,

[Keith Andersen]

Yes, a login system is possible in a couple ways ... each being fairly complicated. 

1 create a web app page with login in feature 

2 create a spreadsheet with sidebar login feature

Each method utilizes a standalone app script as a web app or library to match login username and password to specific sheet(s) permissions for that user granting sole use of a spreadsheet until logout frees that sheet up for use by others.

Sheets would have share access only when given by the script - thereby not needing to be shared universally with "anyone with a link". 

Both can be made very secure.

Again, fairly complicated...but doable.

Keith 

My website: https://sites.google.com/view/klaweb/
Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/b2a04284-a7da-45a4-877d-761f2b83e73bn%40googlegroups.com.

[Robert Lee]

Hi Keith,

I've noted this for my team and I will try your way, 

The limit permission is a complicated issues in AppScript. So I'm still find a easier solution for us. 

Btw, Your idea is really cool. Appreciate that.

Best Regards,

[Keith Andersen]

Robert,

I have actually experimented with this method although I have not completed it yet, and can share a few more insights.

You basically have a admin user login spreadsheet and a user login spreadsheet or appscript web app.

A stand-alone app script contains the code that will be imported into the user login spreadsheet as a library. The standalone script will act as a bridge between the admin login sheet and the user login sheet processing the security check for an actual proper username and password and then based on that users permission profile - Will be granted editor access or view only access to any particular spreadsheet. Once they log off, access will be removed and the sheet opened up to access by other users.

You can set up an additional timed trigger, say 20 minutes, to see if the spreadsheet is still active and if not, remove permissions so that it is not locked open to that one user only.

If set up correctly, it is incredibly secure. 

By using a sidebar in a spreadsheet to both allow them to register and log in, no username or passwords are recorded within the sheets history so they can't go back and find out username and passwords. If you simply use the spreadsheet as a form to both register or login - that data would be captured in Google Sheets, history and therefore unsecure. Sidebars are more secure.

Additionally, the standalone script although shared as a library and is visible to anyone using the user login sheet, it does not open up the admin user login sheet to view through the user login sheet. So the admin user sheet would not be shared but the user login sheet would be shared to anyone with a link. All other spreadsheets given permissions through the user admin would be invisible until proper login was performed. 

In a nutshell, the script gives people either editor privileges or view only privileges based on their proper login and profile permissions in the admin user page. Very secure. The beauty is that it is a hands-off operation allowing you to have secure shares without having to answer requests for shares manually. 

Anyway, hope that information helps. your team develope a system. Feel free to reach out if there's any other questions. 

Keith 

My website: https://sites.google.com/view/klaweb/
Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/768377fb-9f03-4913-9173-ffe0d48cf2a9n%40googlegroups.com.

[Keith Andersen]

And spend some time since I messed around with this.... But thinking a little further I would use a Web app script over a sidebar if you were opening up to anyone with a link - This would be much more secure than the sidebar.

If you were opening it up to trusted users - then sidebar would be sufficient.

Anyway...cheers.

My website: https://sites.google.com/view/klaweb/
Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!

[Brett Grear]

If your only requirement is not wanting to share individually, wouldn't it take just as long to share the password with authorized users? Is it because you have multiple documents that need similar permissions? If so, could you set up a Google group with authorized users and then only share the spreadsheet with that group?

[Michael O'Shaughnessy]

@Brett - Interesting idea!  I do something similar to a apps script library.  Rather than dealing with adding people to the library I just manage a group.  Nice idea with this situation: if you are NOT a member of the group you cannot get into the spreadsheet.

And on this thought, shared drives can do something similar.  Put the sheet in a shared drive and control access by members to the drive.

@Keith - I agree the "web app" approach would be better.  If you work on any "prototype" code please share.  I would be very interested in seeing your thought process on how to make this work.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/e036001e-c190-4087-9ee6-c949ef20a0f2n%40googlegroups.com.

[Robert Lee]

@Brett - Yeah, "The group" will work when I share spreadsheets internally. "Password for the sheet" is a backup plan, in case someone in the group shares the spreadsheet for personal usage out of my control. So I think the password is the final layer of protection for the spreadsheet to avoid information leakage.

@Keith - I'm looking forward to seeing your "webapp" as soon as possible.

Thanks,

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAHNYQLg%3DPBihQJ6YmAdRBXeFQh9naBe%3DgYiWHqfjKd-u%2BtMnmA%40mail.gmail.com.

--

[Keith Andersen]

Robert,

I'll diligently work on it and try and finish it this week. 

Cheers

Keith 

My website: https://sites.google.com/view/klaweb/
Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAA2EpLCq2_aah--SO8QLc2s1EYhmQSihCv1od3Gs06NxEekV5g%40mail.gmail.com.

[Kildere S Irineu]

Technical Report – Analysis and Secure Alternatives for the User’s Request

1. Purpose of the Report

This report provides a technical analysis of a user request posted in the Google Apps Script Community, based on the content extracted from Gmail-Apps-Script-Password-for-Sheet.pdf.

The user (Robert Lee) asked:

“How can I set a password on a Google Sheet, besides the native sharing permissions, so that anyone with the link can access, but only those who know the password can edit?”

This document evaluates:

1. The technical limitations of Google Sheets and Apps Script for implementing “password protection”.

2. The risks of creating a script-based password layer.

3. Secure, realistic alternatives to achieve the user's goal.

---

2. Case Summary & Timeline

From the PDF:

22 Nov 2025 – Robert Lee

Asks if it is possible to set a password on a Google Sheet beyond native sharing.

24 Nov 2025 – Response from Michael O’Shaughnessy

Requests more details and explains that:

• sharing permissions

• sheet/range protection
  are the only existing ways to restrict access.

25 Nov 2025 – Robert clarifies

He wants:

• a login-like page asking for a password before editing;

• “anyone with the link” should be able to access;

• Apps Script should enforce the password.

---

3. Requirement Analysis

3.1 Functional Requirements

• Public access via link.

• Ability to edit only after entering a password.

• Avoid manually managing sharing permissions for each editor.

3.2 Non-functional Requirements

• Reasonable security.

• Simple user experience.

• Maintainable and not easily breakable.

---

4. Technical Analysis: Why a Password Layer Is Not Possible

4.1 How Google Sheets Security Works

Google Sheets access is governed only by:

• Drive file sharing permissions.

• Google Account authentication.

Apps Script cannot override or add new authentication layers to the document.

4.2 Specific Limitations

1. No “pre-access password” mechanism
   Apps Script triggers (onOpen) only run after the user already has access.

2. Permissions override scripts
   If shared with edit rights, the user can:

   • Remove the script,

   • Copy the file,

   • Export the file,

   • Delete protected ranges.

3. Scripts are visible to editors
   Any editor can open Apps Script and read or remove the validation logic.

4. Public editing and password control are incompatible
   When a file is set to “Anyone with the link can edit,” Apps Script cannot restrict editing.

4.3 Conclusion

A password implemented via Apps Script cannot provide real security.
It can only create a visual barrier, not actual protection.

---

5. Risks of Implementing a Script-Based Password

If you try to enforce a password inside the Sheet:

• Users can simply make a copy of the file and bypass restrictions.

• Passwords stored in script are visible to editors.

• Users can disable the script.

• The file can be downloaded and edited offline.

• It provides a false sense of security, which is worse than no security.

---

6. Real Secure Alternatives

Below are stable, secure approaches that actually achieve the user’s goal.

---

6.1 Use Google Workspace Groups + Sharing Permissions

How it works:

1. Create a Google Group (e.g., sheet-...@domain.com).

2. Share the Sheet with the group as editor.

3. Share the Sheet publicly only as viewer (if needed).

4. Add/remove editors by managing the Group, not per person.

Pros:

• Strong, native security.

• No code.

• Scalable for organizations.

Cons:

• Requires accounts (preferably same domain).

• Not suitable for public anonymous editors.

---

6.2 Use an Apps Script Web App as a Secure Front-End (Recommended)

Architecture:

• The Sheet is not shared with editors.

• A Web App manages:

  • login/password entry,

  • form-like editing,

  • validation logic,

  • writing data to the Sheet via script.

How it works:
Users access a Web App link, not the Sheet.
Only the script (Web App) writes to the Sheet.

Example (conceptual):

const SHEET_ID = 'YOUR_SHEET_ID';
const PASSWORD = 'StrongPassword123';

function doGet() {
  return HtmlService.createTemplateFromFile('index')
    .evaluate()
    .setTitle('Access Control');
}

function validatePassword(pass) {
  return pass === PASSWORD;
}

function writeData(data, pass) {
  if (!validatePassword(pass)) throw new Error('Invalid password');

  const ss = SpreadsheetApp.openById(SHEET_ID);
  const sh = ss.getSheetByName('Data');
  sh.appendRow([new Date(), data.field1, data.field2]);
}

Pros:

• The Sheet is fully protected.

• Password logic stays within the Web App.

• Any public user can access the interface.

Cons:

• Users do not edit the sheet directly – they use a UI.

• Password must be secured (hashing recommended).

---

6.3 Use Google Forms + Validation Logic

Workflow:

1. Users submit data through a Google Form.

2. Apps Script validates an “access code”.

3. Valid responses are written to the Sheet.

4. The Sheet is not directly edited by users.

Pros:

• Very easy to implement.

• The Sheet stays protected.

• Code-based filtering possible.

Cons:

• Does not allow free-form sheet editing (only submissions).

---

6.4 Domain-Based Access (Google Workspace)

If the users are within the same organization:

• Set the Sheet to “Anyone in the domain with the link can edit”.

• Enforce 2FA, SSO, or identity controls at the domain level.

Pros:

• Strongest authentication.

• No need for a password layer.

Cons:

• Not usable for anonymous or external users.

---

6.5 Logging, Monitoring, and Auditing (Optional)

You can complement any solution with:

• onEdit logs recording:

  • user who edited

  • before/after values

  • timestamps

• Email or Chat alerts for sensitive changes.

• Admin SDK Reports integration (activity tracking).
  Relevant to the Admin SDK Reports Service in the provided files.

These do not prevent editing but provide governance.

---

7. Comparison of Alternatives

Method	Security	Setup Effort	User Experience	Works with Public Link?
Script-based password inside the sheet	Very Low	Low	Similar to desired	Yes, but insecure
Workspace Groups + sharing permissions	High	Medium	Native Google UX	Partially (view only)
Apps Script Web App front-end	High	Medium/High	Controlled UI	Yes
Google Forms + validation	High	Low/Medium	Easy	Yes
Domain-based access	High	Low	Transparent	Yes, inside domain
Audit logs/alerts	Medium	Medium	Invisible	Works with all

---

8. Final Conclusion and Recommendation

Conclusion

Google Sheets does not support password protection beyond Drive permissions.
Apps Script cannot enforce secure access restrictions if the Sheet is shared publicly for editing.
Any “password inside the sheet” is easily bypassed and offers no real security.

Recommended Solution

To achieve the user’s goal:

---

✔ Best Overall Option

Use an Apps Script Web App as a password-protected front-end.

• Keep the Sheet fully private.

• Users interact with a secure UI.

• Editing occurs only after password or code validation.

---

✔ Best Simple/Corporate Option

Use Google Groups + Drive permissions.

• Editors managed via group membership.

• Public can still view the file if needed.

---

✔ Best Lightweight Option

Use Google Forms + Apps Script validation.

Em seg., 24 de nov. de 2025 às 05:58, Robert Lee <leminhng...@gmail.com> escreveu:

Hello, I have a problem that I'm concerned about: how to set a password for Google Sheet. Besides setting permissions with the "Share" button, is there any solution for sheets with passwords? Thanks.

--

[Keith Andersen]

Kildere,

Would you be interested in thoroughly testing my stem (web app)?

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAJ-AUmgfEDQkv2XAYmO7oFXLB4yLKYLgNFT_7ae4ACTVSNhWJg%40mail.gmail.com.

--

My Site: https://www.sites.google.com/view/klaweb/

Passions: God, Family, Friends, Scripture, Data Management, Google Sheets + App Script, MS Access, Programing, sharing and much more.

[Brett Grear]

If a person shares the file won't that other person also know the password.  Couldn't you just change the share settings so only you can share it and also prevent others from copying or printing?

[Brett Grear]

I'm interested in testing this webapp version

[Keith Andersen]

Awesome Brett. Thanks. Almost done with it. 

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/705ed4e2-e53d-44da-a1db-85268cb06d09n%40googlegroups.com.