Google Apps Script onEdit(e) for specific column does not work

Question

i know this question has been answered but i have tried multiple things and cannot undestand why my script does not work. So i want it to only run the function when you edit an specific column (Number 8 that would be the H column) but it does not work and the function runs when i edit any cell.

Please revise my code and let me know if a miss anything as i have tried multiple approaches and they do not work.

Thanks in advance

My code

  function onEdit(e) {

var server = "" , dbName = "" , username = "" , password = "" , port = 3306;
var url = "jdbc:mysql://"+server+":"+port+"/"+dbName;
var conn = Jdbc.getConnection(url, username, password);
  
var range = e.range;
var col = range.getColumn();

  if (col === 8) {
     
      var stmt_del = conn.prepareStatement('Delete from sheets_db');
      var exe_del = stmt_del.execute();

      var sheet = SpreadsheetApp.getActiveSheet();
      var data = sheet.getDataRange().getValues();
      var stmt_inst = conn.prepareStatement('INSERT INTO sheets_db (Full_Name, LOS, Grade, Sub_LoS, E_mail, DS_Floor, DS_Desk) values (?, ?, ?, ?, ?, ?, ?)');

      for (var i = 1; i < data.length; i++) {
        stmt_inst.setString(1, data[i][0]);
        stmt_inst.setString(2, data[i][1]);
        stmt_inst.setString(3, data[i][2]);
        stmt_inst.setString(4, data[i][3]);
        stmt_inst.setString(5, data[i][4]);
        stmt_inst.setString(6, data[i][5]);
        stmt_inst.setString(7, data[i][6]);
        stmt_inst.addBatch();
      };
      var exe_inst = stmt_inst.executeBatch();
    };
  conn.close(); 
  };

PD: For context the function drops all records in a database and insert the rows from the sheet, if you could additionally let me know a way to do this without deleting the records but that do no insert duplicate entries each time it runs i would be very grateful.

Answer 1

the function runs when i edit any cell and i want to run when i edit only one column

onEdit will be activated when the user changes a value of any cell in the document. You can't get away from it.

• But you can put the full code inside the if condition so that code is only executed if you edit column 8.

Keep in mind that this will work for every sheet, let me know if you need to add in the if condition a specific sheet name.

Solution:

  function onEdit(e) {
  
  var range = e.range;
  var col = range.getColumn();

  if (col === 8) {

      
      var server = "" , dbName = "" , username = "" , password = "" , port = 3306;
      var url = "jdbc:mysql://"+server+":"+port+"/"+dbName;
      var conn = Jdbc.getConnection(url, username, password);
     
      var stmt_del = conn.prepareStatement('Delete from sheets_db');
      var exe_del = stmt_del.execute();

      var sheet = SpreadsheetApp.getActiveSheet();
      var data = sheet.getDataRange().getValues();
      var stmt_inst = conn.prepareStatement('INSERT INTO sheets_db (Full_Name, LOS, Grade, Sub_LoS, E_mail, DS_Floor, DS_Desk) values (?, ?, ?, ?, ?, ?, ?)');

      for (var i = 1; i < data.length; i++) {
        stmt_inst.setString(1, data[i][0]);
        stmt_inst.setString(2, data[i][1]);
        stmt_inst.setString(3, data[i][2]);
        stmt_inst.setString(4, data[i][3]);
        stmt_inst.setString(5, data[i][4]);
        stmt_inst.setString(6, data[i][5]);
        stmt_inst.setString(7, data[i][6]);
        stmt_inst.addBatch();
      };
      var exe_inst = stmt_inst.executeBatch();
      conn.close(); 
    };
  
  };