Google Apps Script - Editors Execute Script on Spreadsheet

Question

I created a Google spreadsheet to help my team keep track of their calendar for the upcoming week. The spreadsheet has 2 tabs: an "Overview" tab and a "Details" tab. On the "Details" tab, the top row lists all of the weekdays through the end of the year and the left column lists the team members names. For each day, the team member writes what school they will be at and the description of support.

The "Overview" tab has the same structure as the "Details" tab (i.e., dates across the top and names listed on the left), but provides a visual summary of where each person will be. I wrote a script that takes the school name from the "Details" tab and enters it in the cell that corresponds to the correct person/date on the "Overview" tab. Then it takes the description of support and creates a Note on the "Overview" tab with the description. It also shades the cell on the Overview tab so I can see that support is planned. So from the "Overview" tab, all I have to do is mouse over the cell and it will show me the planned support.

It works great in my own account. However, I have shared the spreadsheet with some team members and it is not working. I don't want my team to be able to edit the "Overview" tab because I only want them to input their information on the "Details" tab. If I give them editing rights to the "Overview" tab, then they can change the Notes, cell colors, sheet structure, etc (that is why I want the Overview tab as view-only). So, I protected the "Overview" tab so they cannot make edits. However, because they cannot edit this sheet, it seems that the script will not run and update the Overview tab when they edit the Details tab. I assume this is because they don't have permission (because the sheet is protected). When I remove the sheet protection, the script runs just fine for them.

Any thoughts on how I can get around this? I really need to keep the Overview tab View-only. Thanks.

onEdit...but I created my own (onEdit1) instead of using the default onEdit. — Chris

Serge insas Serge insas · Accepted Answer · 2013-11-05T22:09:20

If, as you stated in comments, you use an installable on Edit trigger the problem you describe should not occur since "When a script runs because of a trigger, the script runs using the identity of the person who installed the trigger, not the identity of the user whose action triggered the event. This is for security reasons. For example, if you install a trigger in your script, but your coworker performs the action that triggers the event, the script runs under your identity, not your coworker's identity. For complete information on this, see Understanding Permissions and Script Execution."

see doc here

EDIT : sorry, I didn't see the issue about this special case : issue 1562 posted on july 2012, status "triaged"

EDIT 2 : I tried @tracon6 suggestion to remove the protection temporarily but it doesn't work either... the script generates an error when trying to apply the protection.

but

as an EDIT 3

I found a workaround that works ! We can add an editor just for the time we write to the targetSheet and remove it right after... using a flush in between it works.

here is the code I used to test :

function onEditInstallable(event) {
  var sheet = event.source.getActiveSheet();
  if(sheet.getName()=='Sheet1'){return};
  var r = event.source.getActiveRange();
  var column = r.getColumn();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var targetSheet = ss.getSheetByName("TFA");
  var targetSheet = ss.getSheetByName("Sheet1");
  var user = SpreadsheetApp.getActive().getEditors()[1];
  var permission = targetSheet.getSheetProtection();
  permission.addUser(user);
  targetSheet.setSheetProtection(permission);
  SpreadsheetApp.flush();
  targetSheet.getRange(1,column).setValue('value change on sheet 2, column  : '+column);
  SpreadsheetApp.flush();
  permission.removeUser(user)
  targetSheet.setSheetProtection(permission)
}

EDIT 4 :

Since it seems that many editors could have access to these sheets (see last comments), there has to be a way to know who is active on the spreadsheet. In a non domain environment this is not possible with a triggered function so I would suggest a small uiApp with a list from which the user has to choose, trigger that with an installable onOpen and store the value to use it in the main function.

Heres is a piece of code to handle that aspect.

function SpecialonOpen(){
  var s = SpreadsheetApp.getActive();
  var app = UiApp.createApplication().setTitle('Please select your email in this list').setWidth(300).setHeight(300);
  var list = app.createListBox().setName('list')
  var editors = s.getEditors();
  for(var n in editors){
    list.addItem(editors[n].getEmail());
  }
  var handler = app.createServerHandler('getMail').addCallbackElement(list);
  var btn = app.createButton('select',handler);
  s.show(app.add(list).add(btn))
}

function getMail(e){
  var email = e.parameter.list;
  var editors = ScriptProperties.getProperty('currentEditors')||' ';
  if(editors.indexOf(email)==-1){
    editors+=(','+email);
  ScriptProperties.setProperty('currentEditors',editors);
  }
  var app = UiApp.getActiveApplication().close();
  var editors = ScriptProperties.getProperty('currentEditors').split(',');
  if(editors[0].length<2){editors.splice(0,1)};
  Logger.log(editors)
  return app
}

enter image description here

You should also add a function to reset this list at some moment... don't know what would be best ? on a daily base maybe ? I'll let this to you ;-)

Google Apps Script - Editors Execute Script on Spreadsheet

4 Answers

but

EDIT 4 :