Google Script - onEdit(e) to trigger specific cell change

Question

I'm currently using the below script to trigger an automatic cell edit. However, as a script basically runs every time any cell on the worksheet is edited, I wonder if there is a way to modify it so that it only works when let's say a cell B24 is edited or a range of cells from B24 - B30 are then trigger the script. It seems like an onEdit (e) function could be an answer but I'm struggling to modify the code accordingly. Would you guys be so kind and help?

function myfunction() {

var app = SpreadsheetApp;
var targetSheet = app.getActiveSpreadsheet().getSheetByName("ABC");
targetSheet.getRange(154,2).setValue("Bingo");
}

When using the below onEdit(e) I keep receiving an error message " target sheet not defined"

function onEdit(e) {

var range = e.range;
var spreadSheet = e.source;
var sheetName = spreadSheet.getActiveSheet().getName();
var column = range.getColumn();
var row = range.getRow();

if(sheetName == 'Harmonogram wpłat' && column == 2 && row == 24)
{

SpreadsheetApp.getActiveSpreadsheet().gatSheetByName('ABC').getRange('B154').setValue('Bingo');

}

}

Answer 1

Most likely, the error is due to the typo in the last line of your code.

gatSheetByName('ABC')

should be

getSheetByName('ABC')

See if that helps?

Answer 2

Modification points:

1. You have a typo as the other answer has already mentioned. It should be getSheetByName(name) and not gatSheetByName(name).

2. You want to execute the script when a cell in the range B24 - B30 is edited. To achieve that you need to modify the if condition to include that range:

   if(sheetName == 'Harmonogram wpłat' && column == 2 && row > 23 && row<31)
   

3. SpreadsheetApp.getActiveSpreadsheet() can be replaced by spreadSheet since e.source is exactly the same thing.

Solution:

function onEdit(e) {    
  var range = e.range;
  var spreadSheet = e.source;
  var sheetName = spreadSheet.getActiveSheet().getName();
  var column = range.getColumn();
  var row = range.getRow();    
  if(sheetName == 'Harmonogram wpłat' && column == 2 && row > 23 && row<31){
       spreadSheet.getSheetByName('ABC').getRange('B154').setValue('Bingo');
  }   
}