0
votes

I have a script that adds specific values to the last row of the sheet by clicking a checkbox (has yes/no values) and deletes the row by unchecking the same checkbox. It works fine but as it is onEdit script - it keeps on going adding values. I am trying to loop all this job to check if the value already exists and if yes - do nothing. I developed the following but it does not work - keeps adding values with every edit. Here is the code

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = ss.getActiveSheet()
  var paramrange = sheet.getRange('A55:C60')
  var destn = sheet.getRange(sheet.getLastRow()+1, 1)
  var destnorma = sheet.getRange(sheet.getLastRow()+1, 2)
  var orpac = sheet.getRange('E23')
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();

for (var i = 0; i <= numRows - 1; i++) {
         var row = values[i];
         if (row[0] == 'ORP Ac') {
         } else{

  if(orpac.getValue() == 'yes') {
      var orpacn = 'ORP Ac';
      var orpacnorm = '-150';
      destn.setValue(orpacn)
      destnorma.setValue(orpacnorm)
      } else
      { 
         var rows = sheet.getDataRange();
         var numRows = rows.getNumRows();
         var values = rows.getValues();

         var rowsDeleted = 0;
         for (var i = 0; i <= numRows - 1; i++) {
         var row = values[i];
         if (row[0] == 'ORP Ac') {
         sheet.deleteRow((parseInt(i)+1) - rowsDeleted);
         rowsDeleted++;
 }
 }}

}
}}
1
Trying to clarify your question. Are you looking for a script to check if the first column is equal to 'ORP Ac', if it is then do nothing, if not then run your for loop?ross
yes, exactly...Anton Marchuk

1 Answers

0
votes

Your second loop is inside the first loop and it uses the same index. That's a problem.

This might work better. But I can't tell since I don't have your data.

function onEdit() {
  var ss=SpreadsheetApp.getActive()
  var sheet=ss.getActiveSheet()
  var paramrange=sheet.getRange('A55:C60')
  var destn=sheet.getRange(sheet.getLastRow()+1, 1)
  var destnorma=sheet.getRange(sheet.getLastRow()+1, 2)
  var orpac=sheet.getRange('E23')
  var orpacvalue=orpac.getValue();
  var rows=sheet.getDataRange();
  var numRows=rows.getNumRows();
  var values=rows.getValues();

  for (var i=0;i<numRows;i++) {
    var row=values[i];
    if (row[0]!='ORP Ac') {      
      if(orpacvalue=='yes') {
        var orpacn = 'ORP Ac';
        var orpacnorm = '-150';
        destn.setValue(orpacn)
        destnorma.setValue(orpacnorm)
      } else
      { 
        var rows = sheet.getDataRange();
        var numRows = rows.getNumRows();
        var values = rows.getValues();

        var rowsDeleted = 0;
        for (var j = 0; j<numRows; j++) {
          var row = values[j];
          if (row[0] == 'ORP Ac') {
            sheet.deleteRow((parseInt(j)+1) - rowsDeleted);
            rowsDeleted++;
          }
        }
      }
    }
  }
}

This appears to be a lot of script for a simple trigger which needs to complete in less than 30 seconds.