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

Question

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.

Answer 1

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

• https://developers.google.com/apps-script/guides/triggers/
• https://developers.google.com/apps-script/guides/triggers/events

Answer 2

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);
}

Writing the same thing to three cells and changing the display with formatting

function elfunko() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const f = ["MM/dd/yyyy","dd/MM/yyyy","yyyy/MM/dd"]
  sh.getRangeList(["A1","A2","A3"]).getRanges().forEach((r,i) => r.setValue(new Date()).setNumberFormat(f[i]))
}

Sheet0!A1:A3

A
03/20/2022
20/03/2022
2022/03/20

If you can't do this then perhaps you don't have a Date() object in there in the first place