Google Sheets – Active Cell Validation with Google Script

google sheetsgoogle-apps-script

A Google Script I am writing that seems to be failing. The first part of this script is to validate that only a specific sheet is being changed. In this case column 3 of my sheet called Labor.by.month. If any other sheet gets updated then nothing should happen. Only column 3 should be impacted. My code works but it seems that any column 3 update in any sheet causes the change to occur.

How can I limit it to just one sheet? This is the code I prepared:

function onEdit(e){
  // Set a comment on the edited cell to indicate when it was changed.
  var sourcesheet = "Labor.by.Month";
  var ss = SpreadsheetApp.getActiveSpreadsheet(); //obtain active sheet
  var mysheet = ss.getSheetByName(sourcesheet); //declare sheet value
  var editedCell = mysheet.getActiveRange().getColumn() //get column that active cell is edited from

  if(editedCell == 3){
    SpreadsheetApp.getActiveSheet().getRange('A7').setValue('Hello');
  }

  Logger.log(editedCell);
  //var range = e.range;
  //range.setNote('Last modified: ' + new Date());
}

Best Answer

The following script will do what you want.

Code

function onEdit(e) {
  if(e.source.getSheetName() == "Labor.by.Month") {
    if(e.range.columnStart == 3) {
      var r = e.range.offset(0, -2);
      r.setValue('Hello');
      r.setNote('Last modified: ' + new Date());      
    }
  }
}
Related Topic