onEdit timestamp based on other sheet

Question

As of now, it appears that Google Spreadsheets does not allow locking of cell ranges but instead locking has to be done on a sheet by sheet basis in its entirety. I would like to share a sheet with another user and have them enter data on Sheet 1. To prevent tampering, I would like the data duplicated on Sheet 2 (locked by me) with a timestamp of the last change made on Sheet 1. I've been playing with this onEdit() function but it does not give an updated time when I edit Sheet 1 but still only if I edit Sheet 2. I just haven't figured out what I'm doing wrong.

function onEdit(e) 
{
  var ss = e.source.getActiveSheet();
  var rr  = e.source.getActiveRange();

//comment 2 lines below if you want it working on all sheets, not just on 2nd one
  if(ss.getIndex()!= 2)
    return;
 ///
  var firstRow = rr.getSheetByName(Sheet2).getRow();
  var lastRow = rr.getSheetByName(Sheet2).getLastRow();

//the last modified date will appear in 12th column
  for(var r=firstRow; r<=lastRow; r++)
    ss.getRange(r, 12).setValue(new Date());
}

Is there another way I could do this?

Answer 1

You cannot use scripts to prevent the other user from tampering, because he can go to Tools>Script editor and edit your script so that it becomes idle, then edit Sheet1, then restore your script. See the issue tracker for this issue. You cannot protect your scripts from being edited by other users with whom you share at least one worksheet of your spreadsheet.

On the other hand, leaving scripts aside, you can always see the history of what was edited by whom and when: File>See revision history.

Answer 2

I see a couple of problems. First, you're trying to use .getSheetByName() on a range object, but that method is only supported by spreadsheet objects. You can run .getSheet() on a range and that will return the sheet the range is in. Secondly, formatting: GAS requires standard Javascript which means that your if and for functions need to have {}. This should work for what you want:

onEdit(e){
if(e.range.getSheet().getSheetName()!="Sheet2"){
 var row1=e.range.getRow();
 var col2=e.range.getColumn();
 var row2=e.range.getLastRow();
 var col2=e.range.getLastColumn();
 var data=e.range.getValues();//the data to be copied
 var copySheet=e.source.getSheetByName("Sheet2");//sheet you want to copy to
 var copyRange=copySheet.getRange(row1,col1,row2-row1,col2-col1);// the range data will be copied to
 var time=new Date();
 var timeRange=copySheet.getRange(row,col);//wherever you want the time signature to be. 
 timeRange.setValue(time);//inserts time stamp into sheet
 copyRange.setValues(data);//copies data to the range
}
}

Just replace row and col with the row and column you want to place the time into. It takes the edited range and copies it to the same location on sheet 2 so it only works if sheet 1 is the only sheet to be edited. Otherwise you'll end up with overlapping data. You could also set a time stamp to be adjusted to the range, meaning put its position as something like (row1,col2+5). That would put it 5 columns right of the edited range.