How to Run onEdit/onChange function in Google Apps Script on Data pulled Dynamically using Query

Question

So, I am pulling some data from a Google Sheet let's say A into sheet B.

In the Sheet B, I am fetching the data from Sheet A using Query() formula.

What I need is when a new row is added to Sheet B (pulled when a new row is added to sheet A) then run a script.

What the script will do is choose a random value from the H column which is dropdown type.

The script which works by the way on non dynamic data is:

function onEdit(e){

    var range = e.range;
    var row = range.getRow();
    const agentColumnName = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(row,8);
    const dropdownValues = agentColumnName.getDataValidation().getCriteriaValues()[0];
    agentColumnName.setValue(dropdownValues[Math.floor(Math.random() * dropdownValues.length)]);
        
}

But I want to run this on the Dynamic Data that is when a new row is added by the Query function. I read that onEdit and onChange only runs on user entered data ... but is there any workaround for that?

Please help me fix the script to work on dynamic data.... thanks

Screenshot to understand:

Query+Importrange formula in A2 of sheet B:

=Query(IMPORTRANGE("https://docs.google.com/spreadsheets/d/XXXXXXX/edit","Sheet1!A2:G"),"select *",0)

Answer 1

Try:

Try this script with an onChange() trigger.

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var rg = ss.getRange(2, 8, ss.getLastRow() - 1);
  var vl = rg.getValues();
  var dv = rg.getDataValidation().getCriteriaValues()[0];
  vl.forEach((r, i) => {
    var c = rg.getCell(i + 1, 1);
    c.isBlank() ? c.setValue(dv[Math.floor(Math.random() * dv.length)]) : null;
  });
}

Result: