4

I would like to allow users to use my spreadsheet but not copy it as it contains intellectual property. I tried going to sharing settings and disabling:

  1. Editors can change permissions and share

  2. Viewers and commenters can see the option to download, print, and copy

But the sheet can still be copied. Ideas?

Max Makhrov
  • 17,309
  • 5
  • 55
  • 81
michaeldon
  • 507
  • 3
  • 11

3 Answers3

8

Unfortunately, it is not possible to disable copy / download for editors.

You can only do that for commenters and viewers.

As a workaround, I would advice you to keep your sensitive information into one master file and then importrange or copy via a script the shareable information into another file. So even if they copy or download the latter your sensitive information won't be copied / downloaded.

Related questions:

Marios
  • 26,333
  • 8
  • 32
  • 52
  • 1
    Thanks for the reply Marios. It's actually the code and functionality I want to protect. I guess I could set up a library and put some of the functionality away from the front end... – michaeldon Nov 30 '20 at 10:29
  • 2
    that's even simpler then. Do exactly as my suggestion. Have a `master` file with all the formulas and code, and use an importrange to get that information into another file that you are going to share. So the functionality and the code won't be visible to anyone but you and the second file will contain the `frontend` or the information you want to provide. If you want them to edit also the secondary file, then am afraid you need a script to regularly copy the values (not formulas) from the `master` spreadsheet to the secondary one. Let me know what you think. @michaeldon – Marios Nov 30 '20 at 10:36
  • 2
    Yes it's only the scripts that I need to hide as that's where all the processing happens. You could attach a script to a front end sheet that triggers a script in the back end sheet through a Web App, and you could import the results into the front end using Importrange. It's a bit of a mess though :) – michaeldon Nov 30 '20 at 11:30
  • Hi Mario, thank you very much, I give a bounty to @TheMaster, but I'm still grateful for your work. Here's a related Q: https://stackoverflow.com/questions/63275687/onopen-hide-sheets-doesnt-work-for-viewers-of-a-google-sheet. And I've made my own research. Basically, your idea is correct at the moment, but I hope Google would provide an Admin role to sheets, so editors won't have all Admin permissions. Thanks again and sorry for awarding another answer! – Max Makhrov Nov 16 '21 at 07:54
2

There isn't any sure way to hide your data. Once something is published on the internet, you should consider it saved on many devices all over the world. Consider some ways to get hidden spreadsheet data

Attack scenarios:

  • By far the easiest way is CTRLC and CTRLV(Copy and Paste)
  • Editor menu options: File->Copy and File->Export
  • Once your file id is visible, any editor or even viewer with access to the file can easily copy the file itself through
  • Screenshot and use OCR(Optical character recognition)
  • View source code in the browser and directly copy the table
  • Simulate browser using

Hiding data:

Data may be hidden from naive users. Data cannot be hidden from users, who know the basics of how the web works.

  • Add ?rm=minimal to url, when sharing the sheets file. This hides all menu options.See here
  • Frame the edior in a iframe in your own website and use to hide the top portion of the web page.

Hiding Logic:

You may still be able to hide logic of your code.

  • IMPORTRANGE: This is a very basic and easy way to hide your logic. But there are limitations and any editor can access any part of your master spreadsheet.

  • You can implement a IMPORTRANGE like logic using custom functions and webapps. This gives more control over the connector and secures your master spreadsheet much better than IMPORTRANGE. Here,

    • Two web apps are created, each associated with a spreadsheet(Master and client).
    • You use two KEYs to communicate between them. One for access and other for encryption.
    • Once access is verified, the data from master spreadsheet is encrypted and sent back to the custom function. Simultaneously the encryption key is posted to the client webapp.
    • The key here is the Master/Server webapp posts the encryption key only to the published client web app link. So, no other sheet or anything else can intercept the key or decrypt the data. Furthermore, a random key is generated for each access.

  • Another option is to let go off the spreadsheet completely and use a single webapp to show the data. This hides the logic in server scripts and linked spreadsheets.

Comment thoughts:

Create a script onOpen to kill sheets if the file is wrong?

onOpen cannot post data anywhere without the new copy owner permission. It's not possible to kill sheets. But data can be erased.

/**
 * Deletes all sheets on the copy, if a copy is made 
 */
const onOpen = () => {
  const ss = SpreadsheetApp.getActive();
  const id = ss.getId();
  const sheets = ss.getSheets();
  ss.insertSheet(sheets.length);//insert a blank sheet at the end
  if (id !== '###Original ID###') sheets.forEach(s => ss.deleteSheet(s));//will fail at the last sheet(doesn't matter)
};

But editor can modify the original script before making a copy. And a revision of the original spreadsheet will still be available. The new owner can revert to the original version, use api endpoints mentioned above to get the data. Also mobile apps don't support onOpen. New owners can simply use mobile versions to access data.

Use formula web-app to notify file owner, ?

Possible, but data is already copied and there's no specific information that can be used to accurately identify the new owner. You maybe able to get locale information though.

TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • Hi @TheMaster, thanks for the reply! I thought of a Web-App or Library that can access the ID of a Master Spreadsheet. If a user creates their own copy of the Master file, the copy will surely have a wrong ID. BTW nice trick with `minimal` part of URL, this sort of URL works: `https://docs.google.com/spreadsheets/d/ID/edit?rm=minimal#gid=0`. I'll reward your answer as it considers different options and seems to be the best description of different ideas at the moment. Thank you very much for your time and knowledge! – Max Makhrov Nov 16 '21 at 07:46
  • 1
    @MaxMakhrov Yes, Webapps -> custom function is a option. https://i.stack.imgur.com/bsqrS.jpg From my answer: `The key here is the Master/Server webapp posts the encryption key "only" to the published client web app link`. But we can bypass the client webapp link to a direct spreadsheet link. Webapp->Webapp is permission less and anonymous. Webapp->Spreadsheet needs speadsheet/drive permission depending on how the webapp is published. Thanks for the bounty. I'd be happy to discuss any other ideas/methods regarding this. – TheMaster Nov 16 '21 at 08:34
  • 1
    There is a new question about how to fix the onOpen code for the OP--> https://stackoverflow.com/q/71637632/1595451 – Rubén Mar 27 '22 at 16:48
2

I think the simplest solution would be to copy and paste from the master file the range of values you want to share with the other document. In this scenario the editors of the other document won't have access to neither the code nor the full data of the master file since the latter won't be shared with them.

The copy and paste part can be done automatically via a script and a trigger mechanism to update the data automatically so you won't have to do anything manually and the master file won't be exposed to any user.

Marios
  • 26,333
  • 8
  • 32
  • 52