0

We have a google sheet model that we use for our customers. The customers need to be able to input data into the model themselves, so we normally create a copy of the model for the specific customer, and then share this model with them so they can edit it and input their data and assumptions.

We have spent a lot of time creating a detailed model, and we would like to protect our work and the model so the customers can’t just copy the model or share it with other persons so they can use the model. We would also like to limit the customers input to certain input cells, so they do not accidentally overwrite formulas etc.

For now, we have locked the different sheets in the model, so the users can only input data in the fields we want them to, and that seems to work fine. This also works in relation to protecting the model somewhat for being used by other persons, as important information in the models such as customer name, time periods etc. are locked and cannot be changed by the user.

But… we found out that the user can still choose to download the model in excel, and then all the restrictions and locked cells are gone…

So how do we best protect our google sheet model for being shared and used by other persons, while still being able to share it with the specific customers that needs to work with it and input data in it?

3
  • Welcome to Web Applications Stack Exchange. Nov 5, 2021 at 10:57
  • Welcome to Web Applications. Questions on this site should be specific, in other words, posts should be limited to a single question, also, posts should not contain greetings, thanks, signatures, and other "chit chat". Please add more details about your model like if it is using only built-in functions or if it's also using macros, custom functions or other scripts, if you are using gmail.com account or a Google Workspace account and a brief description of your search efforts. Ref. How to Ask, Expected behavior.
    – Rubén
    Nov 5, 2021 at 16:20
  • Related 1, 2, 3
    – Rubén
    Nov 5, 2021 at 16:27

1 Answer 1

0

If you grant a person edit access to a spreadsheet, they can always make a copy and discover everything in the spreadsheet.

The usual workaround is to create two spreadsheets: a 'View' spreadsheet for entering parameters and viewing the results, and a 'Calculation' spreadsheet that imports those parameters from the View spreadsheet and gets the needed results. The View spreadsheet in turn imports just the final results from Calculation. The Calculation spreadsheet does not need to be shared with viewers at all.

Use the importrange() function to transfer data from View to Calculation and back. This way, the formulas in Calculation are never revealed to viewers.

The downside of this approach is that a separate Calculation spreadsheet is required for every View spreadsheet. To use just one Calculation spreadsheet for everyone, implement the view bit with a web app. See the Web App Demo answer for more information and sample code.

1
  • Ok, thanks - that sounds like a good approach - I will try to make that work for the model, thanks for the input Nov 8, 2021 at 8:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.