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 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).
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:
- 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
- Simply hide the materials tab
- Not useful, becuase it can be unhidden easily
- 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.