How do I best protect my google sheet model from being copied and shared with persons it was not intended for?

Question

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?

Answer 1

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.