43

I have written some code for my Google spreadsheet with the script editor.

I want to share this spreadsheet with my clients but I don't want to share the code I have written.

This code adds a menu to my spreadsheet which contains useful functions that should work/run when my clients open this spreadsheet with condition applied that: they shouldn't be able to see its code.

This is easy with Excel, but with Google spreadsheet I don't know. . I have searched a lot on Google but they all gives the idea about how to share a spreadsheet. For example in "View" mode, but in that case there is a problem: my menu function, which adds a menu, is "onOpen" and doesn't start when my clients open it.

How to implement this in Google Spreadsheet?

namit
  • 6,780
  • 4
  • 35
  • 41
  • 2
    You cannot really hide GAS code. This is a FAQ! Possible dup of [Q14901758](http://stackoverflow.com/questions/14901758/how-to-keep-google-apps-script-secret), [Q15869708](http://stackoverflow.com/questions/15869708/spreadsheet-script-access), [Q11915118](http://stackoverflow.com/questions/11915118/how-to-hide-library-source-code-in-google-way), and probably more. – Mogsdad Apr 18 '13 at 13:43
  • 1
    Have you considered using code obfuscation or a minifier? – le3th4x0rbot Jul 28 '17 at 20:40
  • 1
    Here is a link to the Apps Script Issue tracker for a feature request. Please "star" the issue to get more attention. [Link to Issue Tracker](https://issuetracker.google.com/issues/36764223) – Alan Wells Dec 20 '18 at 17:36

6 Answers6

29

Short Answer

Publish your script as an editor add-on or as a Google Workspace add-on. Bear in mind that you could make it private, by selecting unlisted or making it available only for your G Suite / Google Workspace organization.

Explanation

Add-ons were added on 2014. This is better than using a library because there will be nothing shared from your add-on.

Please note that

  • it's not possible to access Google Apps Script server-side code by using Chrome Developer Tools or another similar software.
  • there are some restrictions for add-ons, study them carefully as maybe this alternative will not work for you.
  • nowadays add-ons require a Google Cloud Project and an oAuth consent screen. If the add-on will be shared with external users it will requires an oAuth verification and an app review.

If publishing as add-on is not an option for you, you should rethink your solution considering the use of a web application, using the Apps Script API or the Google Sheets API.

Related questions

References

Rubén
  • 34,714
  • 9
  • 70
  • 166
  • 2
    I think that your answer is now the best answer. I would like to upvote this. If there is other workaround, although this depends on the process that the owner wants to run, I had thought of use of Web Apps. When users run a function, the function retrieves the results from Web Apps and show them on the spreadsheet. In this case, the spreadsheet which is used by users and the project that Web Apps is deployed are separated. So users cannot see the script of Web Apps. – Tanaike Jan 05 '18 at 00:37
  • 3
    add ons have a time driven trigger restriction of once per hour at most.https://developers.google.com/apps-script/guides/triggers/installable ... that is an annoying restriction. – user2677034 Mar 19 '18 at 20:08
  • 1
    G Suite Editors Add-Ons now are only published using the G Suite Marketplace. Unfortunately it do not support sharing and add-on with a group. – Rubén Apr 25 '20 at 16:49
8

make use of Library the documentation explains how to use it and there are a few interesting post on the subject as well

Community
  • 1
  • 1
Serge insas
  • 45,904
  • 7
  • 105
  • 131
  • 13
    Just to be clear. Using a library will not prevent the code to be seen (although it makes it difficult), just really protects against editing. – Henrique G. Abreu Apr 18 '13 at 17:42
  • 4
    Yes ,step in can exposed the code in the library ,however I wonder would be able for google to offer at least a password protect level at the script editor similar to the Excel protected macro for the minimal protection or not? – TSG anti SO dark forces Mar 17 '16 at 03:42
  • Libraries may not be suitable for "UI-heavy scripts" as pointed in [Best Practices](https://developers.google.com/apps-script/guides/support/best-practices#avoid_libraries_in_ui-heavy_scripts). –  Jun 13 '18 at 01:37
4

Make a special library file containing only the script for your client. Your client must have at least a read-level access. So he is able to see your script. Remove your script and make a new innocent script in that file and save this as a new version. Now your client sees only this new script. Because his application is still working on the old version of your library, the original script will do his job as usual.

Rob
  • 157
  • 2
  • 8
  • This is a great idea, but there is something else that must also be done. You want to avoid a situation in which the user with VIEW access, could open the script in the Apps Script code editor, and look at the version history, and see previous changes to your file. In order to deal with this problem, you could save lots of changes with meaningless content, which would populate the version history with content that is unusable and has nothing to do with the code that is actually used. – Alan Wells Dec 20 '18 at 17:51
  • 1
    But even if you do what I've suggested above, there is still a problem, because anyone with VIEW access can open the file in the code editor, and then look at the version history. But there is a way to deal with that problem also. You make about 25 changes, and 25 new saves to your code. That fills the version history with content that is not your code. The version history is immaterial to the code that actually runs. Actually, if the user of the library uses the debugger and steps into the code, then the entire code is shown in the code editor. So, because of that you can't protect it. – Alan Wells Dec 20 '18 at 19:02
1

Apart from the options listed under 'Publish' menu in the script editor window, the below 3 options may also keep code private to the developer.

Option1:
3 files are to be created:
1.1 A spreadsheet to receive the user input, this serves as a user interface, and is to be shared with the user.
1.2 A standalone library script file to receive the input from the UI spreadsheet.
1.3 A standalone private script file to process the data which the library file receives.

Option 1 Spreadsheet

As a reference for this option is a spreadsheet at the above link.
When 'Submit' is clicked, the spreadsheet ID is sent to the library script file and the developer is given editor permission. The private script file then opens the file and processes the input.

Option 2:
5 files are to be created
2.1 A spreadsheet which serves as a login screen. This is shared with the user.
2.2 A spreadsheet which serves as the actual application that contains the data. A user interface. This spreadsheet is made available to the user after successful login. The user logs-out of the application from this sheet.
2.3 A spreadsheet which maintains the login status of the user. This is private to the developer.
2.4 A library script file which updates the login status of the user in the spreadsheet created in the step above and changes the access permission to the private script file. This script file is included as a library resource in both the login spreadsheet and user interface spreadsheet.
2.5 A library script file which processes the data entered in the user interface sheet. This script file contains code that the developer wants to keep private. This file is included as a library resource in the user interface spreadsheet only.

Option 2 Spreadsheet

As a reference for this option is a spreadsheet at the above link.
When the user clicks the 'Get Started' button the user is logged into the application after updating the cell value in spreadsheet 2.3.The library script file which contains private code is set to shareable after successful Login by using addViewer method. A link to the application is presented to the user and the user interacts with the application while 'Logged in'. When the user logs-out from the application removeViewer method is called to make the script file private again.
In this option, the private code file is accessible to the users while they use the application. The file may be kept private by using timers, script triggers, prompts, alerts, etc.

In both of my spreadsheets I use time based triggers set to run every minute to process the applications. This may cause the output to be delayed. The triggers are created on the server side and not at the client side.

Option 3: Private shared folder

2 files are to be created:
3.1 A spreadsheet to receive the user input, this serves as a user interface, and is to be shared with the user.
3.2 A standalone private script file to process the spreadsheet.

Please refer this link on how to create a private shared folder. In this option, no script is visible to the user. Even the shared spreadsheet may not contain code.

sharken
  • 57
  • 4
0
  1. Create 2 libraries; one is dummy (I call it Lib1) and another core library that has all the secret codes (I call it Lib2).
  2. Add Lib2 as a library to Lib1 and use in it the necessary methods that are fully implemented in Lib2.
  3. Share both libraries publicly and set their permission to Viewer.
  4. From the sharing setting page of Lib1, there is a gear symbol. make sure the following option is unchecked "Viewers and commenters can see the option to download, print, and copy"
  5. Add Lib1 to your clients' script (maybe a Spreadsheet script).
  6. Share the script of your client as editor or viewer.

If the client could get Lib1 ID by accessing it from Resources/Libraries menu of his script, he won't be able to access the Resources/Libraries menu of Lib1. He also cannot make another copy of Lib1 because of the unchecked option done in step 4, preventing him from possibly seeing the ID of Lib2.

Moayad Hani Abu Rmilah
  • 800
  • 2
  • 8
  • 17
  • Have you verified that the libraries code isn't displayed when debugging and isn't dowloaded by using CLASP? – Rubén Nov 09 '20 at 19:28
0

There is a similar question here. Answer is accepted there.

You can create a standalone apps script project. And then from your google sheet script editor call that standalone apps script project functions with proper parameters. That way, your secrets in standalone project will be protected.

k.b
  • 157
  • 1
  • 2
  • 13