OnEdit script to change timestamp when a row is edited (error)

0
votes

I found this script and am trying to use it in a google sheet. The trouble I am having is that I get an error message "You do not have permission to call setValue (line 8).", and when I change a value it will only create the timestamp for the first entry in a cell. If I edit the cell the timestamp does not change. Could someone take a look at this scrip to see what is wrong. Sorry I am a newbie to writing code and I don't understand all of the lines. Here is the sheet This is the script I am using:

function onEdit() {
 var s = SpreadsheetApp.getActiveSheet();
  var r = s.getActiveCell();
  if( r.getColumn() != 2 ) { //checks the column
    var row = r.getRow();
    var time = new Date();
    time = Utilities.formatDate(time, "GMT-08:00", "MM/DD/yy, hh:mm:ss");
    SpreadsheetApp.getActiveSheet().getRange('M' + row.toString()).setValue(time);
  }
}

In order to run the script I typed =onedit(a3:l3) it seemed like the only way to get it to work at all. Thanks for any help

thanks for any suggestions.

2
excelgoogle-sheets

2 Answers

1
votes

Try making the following commented changes to your code 

function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  var r = s.getActiveCell();
if( r.getColumn() != 2 ) { //checks the column
  var row = r.getRow();
  var time = new Date();
  time = Utilities.formatDate(time, "GMT-08:00", "MM/DD/yy, hh:mm:ss");
// remove this line below
SpreadsheetApp.getActiveSheet().getRange('M' + row.toString()).setValue(time);

//replace with this line below
return time;
  }
}

Reference

You cannot set values to a cell outside of where the custom formula is being set on the spreadsheet, its illogical.

0
votes

If you want to date/timestamp col M when edits are done in col B, try something like this:

function onEdit(e) {
var s = SpreadsheetApp.getActiveSheet();
if(s.getName() !== 'Sheet1' || e.range.columnStart != 2 || e.range.rowStart < 2) return;
e.range.offset(0, 11).setValue(e.value ? new Date() : "");
}

As this is an onEdit there is no need to enter a formula (=onedit()) in the spreadsheet. Note that: - erasing a value in col B will cause the stamp to disappear (if you don't want this, just let me know). - format col M as date/time via the 123-button.

See if this helps ?