CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
мајдејсаремех
мајдејсаремех

Reputation: 536

onEdit(e) trigger won't call function automatically in google sheets

I am trying to calculate some values using my own function (showing dummy function in this question), and I'd like the function to trigger when the user edits a cell in a given range, however even after writing the trigger the only time when the value is calculated again is when I go to the script editor and click Save.

My function:

function calculate(column) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var column = sheet.getActiveCell().getColumn();

  var values2d = sheet.getRange(1, 1, 15).getValues();
  var values = [].concat.apply([], values2d);

  var testRange2d = sheet.getRange(1, column, 15).getValues();
  var testRange = [].concat.apply([], testRange2d);

  var sum = 0;
  for (var i = 0; i < 15; i++) {
    if (testRange[i] == true) sum += values[i]
  }

  return sum;
}

function onEdit(e) {
  var activeCell = SpreadsheetApp.getActiveSheet().getActiveCell();
  if (activeCell.getBackground() === "#e3f5a2") activeCell.setBackground("#ffffff");
  else activeCell.setBackground("#e3f5a2");
  calculate();
}

And the sheet:

enter image description here

The cell besides total is where I call my function. And the value will only be updated either manually or as described earlier, but even when I trigger an event, I have also tried doing other actions in the trigger like changing a random cells value and that worked however.

Upvotes: 0

Views: 385

Answers (1)

TheWizEd
TheWizEd

Reputation: 8616

Might i suggest the following. This will only occur if a checkbox in column 2 is changed.

function onEdit(event) {
  try {
    var sheet = event.source.getSheetByName("Sheet1")
    if( event.range.getSheet().getName() === "Sheet1" ) {
      if( event.range.getColumn() === 2 ) {
        var values = sheet.getRange(1,1,15,2).getValues();
        var sum = 0;
        for( var i=0; i<values.length; i++ ) {
          if( values[i][1] ) sum += values[i][0];
        }
        sheet.getRange(16,2,1,1).setValue(sum);
        if( event.range.getBackground() === "#e3f5a2" ) 
          event.range.setBackground("#ffffff");
        else
          event.range.setBackground("#e3f5a2");
      }
    }
  }
  catch(err) {
    SpreadsheetApp.getUi().alert(err);
  }
}

Upvotes: 1

Related Questions