Scripts not running for shared users in Google Sheets

Question

I created a spreadsheet for me and my co-workers to be able to edit live which I have already shared with them. I added the functions for a timestamp to show in 2 columns once specific cells are edited and a write once script that uses a helper sheet. The problem I'm having is that the scripts won't run for anyone but me when I protect the main sheet or the helper sheet. Can someone help me figure out how to protect both sheets but leave specific cells on the main sheet open for editing?

I did some research and found that it's probably due to triggers that need permission. The solution would be to install these triggers so they run as the person who installed them but I'm not quite sure how to do that. — An Te
the problem I've been seeing with this is when I click to view all my triggers, it doesn't list any. I'm not exactly sure why I can't run my functions while the sheet/s are protected. — An Te

Sam Tyurenkov Sam Tyurenkov · Accepted Answer · 2018-12-14T21:46:09

This function adds date in column B when column C is manually edited in a Sheet named "Sheet1". Note, that onEdit trigger won't work if a cell is programmatically edited:

function onEdit(e) {
  if(e.source.getActiveSheet().getName() == "Sheet1") { 
    if(e.range.columnStart == 3 && e.range.rowStart > 1) {
      var d = Utilities.formatDate(new Date(), "GMT+1", "dd.MM.yyyy");
      e.range.offset(0, -(e.range.columnStart - 2)).setValue(d);
    }
  }
}

As for the permissions - they do exclude each other in your case, you can't forbid access to entire sheet and allow to some columns/rows at the same time.

Instead, you should have the sheet editable and restrict only rows/columns you want to protect + provide editing rights to the rows/columns you want to be editable - and they shouldn't interfere.

Scripts not running for shared users in Google Sheets

1 Answers