Google-sheets – How to block access to sheet in Google Sheets spreadsheet

google sheets

Scenario

A friend's work currently uses Google Sheets to make quotes: this sheet has about 10 tabs, including one tab for "materials", which includes their cost, their markup %, and sold-to-customer value. This sheet is shared as read only to a few other people, who make a copy of this "template", which they can then edit to be the actual quote. These other tabs are quite in-depth, talk to eachother, and most importantly, work to build the quote accurately.

One of the tabs works by putting in the material number, which adds it to the quote, as well as the cost, markup etc.

Every week, the manager edits the template to update all the material pricing.

The issue

The manager has decided that it would be better if the material tab were not visible to everyone in bulk. They acknowledge that, regardless of which way they end up going, they'll be able to see the cost / markup etc one material at a time, but want to prevent a bulk "copy-paste" or download of the material tab, presumably to prevent the case in which it gets shared to someone it's not supposed to (my presumption).

The Question

Is there a way to achieve this? Block one tab from being viewed? Can anyone think of a different way to get this solved? My thoughts so far are:

  1. Create a separate Google Sheet JUST for materials, which the first references.
    • This seems like it won't work, because if there's a link in the template, it can be used to access the new material sheet via URL with the sheet key
  2. Simply hide the materials tab
    • Not useful, becuase it can be unhidden easily
  3. Create an add-on which uses a script to reference the new material sheet instead
    • This is my favourite option so far, as it links to the new sheet WITHOUT revealing the Sheet Key to the end-user
    • The user would use a function (ie "retreiveCost(materialCode)") to get the value
    • The Sharing permissions for the sheet, however, would likely have to be set to "public / anyone with the link can view", which means that this may be for naught if someone randomly stumbles across it.

They used to use Excel, but shifted to G-Sheets for ease of sharing around. If need be, they may be able to shift back to Excel if it solves the problem.

Any ideas? Anything would be much appreciated.

Best Answer

In case it helps anyone else in the future, the solution I've ended up pushing for is creating an Add-On to reference the sheet they wanted hidden. The other workbook / Spreadsheet is:

  • set to only "people in the domain with the link can VIEW"
  • The tab / sheet is hidden and protected, and
  • The document is set to be copy, download, and print protected, just in case someone does find it via spreadsheet key

The add-on itself will be a domain-only add-on, and auto-installed via policy for those who should have access. I believe this should be sufficient protection for what they need.

Related Topic