Google-sheets – How to prevent editors from accessing the script editor in Google Sheets

google sheetsgoogle-apps-script

I have an auto sort script on a Sheets document that I will be giving multiple people editing permission to, to add to the file. I have created a trigger so the script also runs when other users edit the file as well.

The only thing I would like to do now is prevent other users with editing permission from accessing the script editor and changing it. Is it possible to prevent access to script editor for other collaborators with editing permission?

I have read other answers saying to create a standalone script and programmatically create an installable trigger. I have created a standalone script but I don't know how to use it with the Sheets document and when I tried to put in an installable trigger it only seemed to run the script properly once, and when accessing it from another users account I could still access script editor as well.

Best Answer

I see this question is quite old but thought I'd answer for of other searchers in the future.

You can't restrict editors from viewing the script editor.

You can, as you have started to explore, have a standalone script file which connects to the standalone spreadsheet.

In Drive click NEW > MORE > Google Apps Script

You connect the spreadsheet & the script together by using:

var ss = SpreadsheetApp.OpenFileById("[spreadsheetIdHere]")

Once the variable ss is assigned to the spreadsheet you can reference it in the standalone script the same as you would have done if it had been a bound script in the script editor.

The spreadsheet ID is the long number (marked as xxxx here) between 2 slashes for instance:

https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxx/edit#gid=0

I hope that gives you something to go on.

Related Solutions

Google-sheets – How to use triggers with functions that require parameters from the spreadsheet

How to use triggers with functions that require parameters from the spreadsheet?

AFAIK, you cannot. The solution is to create one single function, for example called RefreshAll, inside which you call all the wrapper functions (GetValue) containing the actual parameters which need to be passed to the function that actually does the job (RealFunctionThatDoesTheJob).

Note: Sorry if this sounds complicated. If you only have one set of parameters (as opposed to 3, in the example below), you can do away with GetValue and call RealFunctionThatDoesTheJob directly from within the function that is being triggered (RefreshAll).

Lastly, to have your triggers send to your spreadsheet those values that are dependent on parameters, you need to have the script push the values to the spreadsheet using (currentSheet.getRange(Row,Column).setValue(Value);)

Here is the example:

function RefreshAll() {

  var currentSheet = SpreadsheetApp.openById("ID").getSheetByName("MySheet");

  var Value1 = GetValue1();
  currentSheet.getRange(Row,Column).setValue(Value1);

  var Value2 = GetValue2();
  currentSheet.getRange(Row,Column).setValue(Value2);   

  var Value3 = GetValue3();
  currentSheet.getRange(Row,Column).setValue(Value3);

}

function GetValue1() {
  return RealFunctionThatDoesTheJob("Parameter1","Parameter2","Parameter3");
}

function GetValue2() {
  return RealFunctionThatDoesTheJob("Parameter4","Parameter5","Parameter6");
}

function GetValue3() {
  return RealFunctionThatDoesTheJob("Parameter7","Parameter8","Parameter9");
}

Google-sheets – How to make Google Sheets editable only at certain times

With the following script you can restrict access (view only) to your Google Spreadsheet.

Code

function setUsers() {  
  var date = new Date(), day = date.getDay(), hour = date.getHours();
  var monday = 1, friday = 5;
  if((day == monday && hour == 8) || (day == friday && hour == 14)) {
    var sProp = PropertiesService.getScriptProperties();
    if(!sProp.getProperty('day')) { 
      sProp.deleteAllProperties();
      sProp.setProperty('day', day);
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      switch(day) {
        case monday:
          ss.addEditors(ss.getViewers());
          break;
        case friday:
          var editors = ss.getEditors(); 
          for(var i = 0, iLen=editors.length; i < iLen; i++) {
            var user = editors[i];
            ss.removeEditor(user);
            ss.addViewer(user);
          }
          break;
      }      
    }    
  }
  return;
}

Trigger

enter image description here

Explained

The trigger is only fired when it's a Monday or a Friday and only on those specific times. After that it will check whether it hasn't been triggered once before, by looking up in the scripts memory. If nothing is found for that day, then delete all other entries and create a new one. This way, if the script is triggered again, it will find an entry and skip to return. The switch statement makes it possible to switch between the days and the different actions to make. On Mondays, the existing viewers need to be added as editors. This can be done via a batch operation (at once). On Fridays the editors need to be removed first in order to add them as viewers.

Notes

I've tested it against two of my own accounts and it worked. As far as I know, no messages are being send about the user being added as viewer or as an editor (which is the case when you do that manually). In the editor mode, the viewer mode is being enforced automatically. This isn't the case when the user is in the viewer mode. There the user needs to refresh the window (F5).

If you the set trigger on a 5 minutes interval, then in theory the time frame can be missed by 5 minutes. If you choose to pick the 1 minute interval, then this will be minimized to 1 minute.

Another option would be to create a trigger each and every time. This way the set time can be programmatically triggered (even thought the triggers are bound to be executed give or take 15 minutes, see reference²).

Add the code under Tools > Script editor and press the bug button to authenticate the script. Furthermore, create a trigger by selecting Resources > Current project's triggers.

Setback

There's one huge setback to this solution however. Everyone, added as an editor, will be able to see and change the code and there's no way to avoid that, see reference¹.