Google Sheets, AppScript: How to run custom function with parameters on edit?-CodePudding

On google sheets, I have a custom function which is using cell values as parameters and doing a calculation in app script. I want this function to run on everytime an edit happens in the sheet to get the latest values.

function buildProfit(uniqueId, transactionType) {
  if (transactionType == 'Sale') {
    return 0;
  }
  var rows = SpreadsheetApp.getActiveSheet().getDataRange().getValues();
  var buyPrice = 0;
  var totalPrice = 0;
  for (var i = 0; i < rows.length; i  ) {
    if (rows[i][1] === uniqueId && rows[i][4] === "Sale") {
      totalPrice  = rows[i][7];
    }
    if (rows[i][1] === uniqueId && rows[i][4] === "Buy") {
      buyPrice = rows[i][7];
    }
  }
  var profit = totalPrice - buyPrice;
  return profit > 0 ? profit : 0;
}

I am calling this function in the cell like this =buildProfit($B3, $E3) I understand there's a function called onEdit() but I am confused, how do I pass it via the sheet so that the parameters are passed well. Thanks.

CodePudding user response：

Running a function with parameters from onEdit trigger

function onEdit(e) {
  const sh = e.source.getSheetByName("Sheet0");
  const [uniqueId,transactionType] = sh.getRange("A1:A2").getValues().flat();//getting parameters from spread sheet or perhaps you may wish to take them from your current spreadsheet
  buildProfit(uniqueId, transactionType);
}

CodePudding user response：

Description

You would have to replace the formula with the following script. When the onEdit(e) is triggered by a change in any cell the value will appear in the cell that previously contained the formula.

Script

function onEdit(e) {
  try {
    // in case you want to limit edits to only Sheet1
    if( e.range.getSheet().getName() === "Sheet1" ) {
      var sheet = e.range.getSheet();
      var uniqueId = sheet.getRange("B3").getValue();
      var transactionTyoe = sheet.getRange("E3").getValue();
      var profit = 0;
      if( transactionTyoe !== "Sale ") {
        var rows = sheet.getDataRange().getValues();
        var buyPrice = 0;
        var totalPrice = 0;
        for (var i = 0; i < rows.length; i  ) {
          if (rows[i][1] === uniqueId && rows[i][4] === "Sale") {
            totalPrice  = rows[i][7];
          }
          if (rows[i][1] === uniqueId && rows[i][4] === "Buy") {
            buyPrice = rows[i][7];
          }
        }
        profit = totalPrice - buyPrice;
      }
      // XX99 is the cell that contains the formula
      sheet.getRange("XX99").setValue( profit > 0 ? profit : 0 );           
    }
  }
  catch(err) {
    SpreadsheetApp.getUi().alert(err);
  }
}

Reference