0

I hope I'm not again asking a question that's been answered, kindly bear with me if I am doing so.

I basically have been building a Google Sheet to be used by a number of users. I created a number of Script files and everything is working as expected, however editors of the Sheet will be able to see the source code from Script Editor menu option on Sheets.

There is a lot of sensitive information in the scripts that should be hidden from the editing users, how can I restrict them from editing the scripts and also not viewing sensitive information?

I found this answer which suggested publishing the Apps Script project as an Add-on. Unfortunately I am having a tough time comprehending how to go about it (my manifest hasn't been updated) and there are a few more answers that use different approaches.

Note: If there is an easy example showing how to go about Publishing then kindly share as I am struggling with Google's documentation.

My appsscript.json file:

{
  "timeZone": "Africa/Maputo",
  "dependencies": {
  },
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8"
}

Note2: The scripts' entry point is via an installable trigger On editing the spreadsheet

Note3: When switching to Legacy mode and Publish->Deploy from Manifest as in the examples from Google's documentation, I cannot see an option to install the addon - No entry points is indicated.

What would be the best way to achieve this security feature given I have multiple .gs files in my project and want the editors of the Spreadsheet not to have even viewing access of the source code.

Will greatly appreciate any feedback, Thanks.

Shalin
  • 105
  • 2
  • 14

3 Answers3

1

The behavior you are experiencing seems to be due to the fact that the script is bound to the sheet in question.

A bound script is nothing but a script which was directly created from the spreadsheet itself rather than separately.

What you can do in this situation

  1. Create a new Apps Script project which is not bound to any spreadsheet. A simple script.new in the browser bar will do the trick.

  2. Copy and paste the entire code to the new script.

  3. Replace any instructions similar to:

SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// Or whichever Apps Script service you are using

To:

SpreadsheetApp.openById("SPREADSHEET_ID").getActiveSheet();
// Or whichever Apps Script service you are using

Since the new script won't be attached to the spreadsheet, you will have to specify exactly which spreadsheet you want to open and work on, hence the use of openById method.

  1. Reinstall the trigger/s

You can install the triggers programmatically too! For your use-case, if you want to use an onEdit, just create the following function. This ends up creating an onEdit trigger and it attaches it to the function you want it to act as the trigger.

function createSpreadsheetEditTrigger() {
  var ss = SpreadsheetApp.openById('SPREADSHEET_ID');
  ScriptApp.newTrigger('TRIGGER_FUNCTION_NAME')
    .forSpreadsheet(ss)
    .onEdit()
    .create();
}

  1. Remove any code from the old script which was attached to the spreadsheet.

  2. Voilà!

Reference

ale13
  • 5,679
  • 3
  • 10
  • 25
  • Hi Ale, many thanks. I've followed you instructions up until the point where I have to Select event source for my trigger ... which is supposed to be whenever the spreadsheet is edited. I only have "Time-driven" and "From calendar" options available, there is not option "From spreadsheet" as was the case in the script bound to the spreadsheet. – Shalin Aug 27 '21 at 13:57
  • That shouldn't really be the case, but anyway, you can create the trigger programmatically: `function createSpreadsheetEditTrigger() { var ss = SpreadsheetApp.openById('SPREADSHEET_ID'); ScriptApp.newTrigger('TRIGGER_FUNCTION_NAME').forSpreadsheet(ss).onEdit().create(); } `. I tested the above scenario and everything worked as expected for me :) Let me know if you have any questions! – ale13 Aug 27 '21 at 14:10
  • Thanks Ale ... I am now able to run the standalone script by editing the Spreadsheet. Thanks. However, it seems to be referencing the first sheet in the Spreadsheet despite calling the getActiveSheet() function. `var ss = SpreadsheetApp.openByUrl(clientLoadRegUrl);` `console.log(ss.getName()) // correct` `console.log(ss.getId()) // correct` var as = ss.getActiveSheet(); console.log(as.getName()) // wrong, giving the first sheet – Shalin Aug 27 '21 at 14:44
  • `var ss = SpreadsheetApp.openByUrl(clientLoadRegUrl);` `console.log(ss.getName()) // correct` `console.log(ss.getId()) // correct` `var as = ss.getActiveSheet();` `console.log(as.getName()) // wrong, giving the first sheet` – Shalin Aug 27 '21 at 14:51
  • Use `getSheetByName(NAME_OF_THE_SHEET_YOU_WANT)` instead of `getActiveSheet()` – ale13 Aug 27 '21 at 15:29
  • There are multiple sheets within the spreadsheet which the end user can edit. So we won't know the name of the sheet beforehand. Is there a way to pass the active sheet through the trigger? – Shalin Aug 28 '21 at 03:29
  • There's a workaround but not efficient ... I have used a simple onSelectionChange trigger to update the name of the active sheet on cell A1 of the first sheet which can be hidden. It works but is quite slow and runs on every selection change. – Shalin Aug 28 '21 at 06:59
  • Is there any way we can get the UI object for the sheet? Cause even though I'm getting the active sheet, the active cell is A1 as well ... so this will be an ongoing issue. – Shalin Aug 28 '21 at 07:24
  • It looks like it would be best to ask a new question as this is a different situation from the one you presented here in this question :) – ale13 Aug 28 '21 at 08:56
1

Each Editor has access to all shared codes. Check this website To hide code for editors, you can only obfuscate the code.

Peter Csala
  • 17,736
  • 16
  • 35
  • 75
Max XBox
  • 11
  • 1
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jan 23 '22 at 07:08
0

You cannot protect the code nor any credentials unless you publish the code as an add-on.

GoranK
  • 1,628
  • 2
  • 12
  • 22
  • Ok thanks. Is there any other documentation out there, tutorials on how to go about this, besides the Google documentation? An example would be really helpful. – Shalin Aug 27 '21 at 12:00
  • You can get all information you need from Google documentation. If you are looking for a code samples, check https://developers.google.com/workspace/add-ons/samples – GoranK Aug 27 '21 at 12:16
  • The documentation has a lot of content and I, for example, am now stuck trying to figure out how to configure the OAuth consent screen. I want to publish this privately as it will only be used within our organization. – Shalin Aug 27 '21 at 12:57