This is an introduction to the concept of using onChange to trigger specific events as opposed to onEdit.
The challenge: onEdit() doesn’t fire when the change is the result of a formula, including IMPORTRANGE(), GOOGLEFINANCE(), etc. and onChange() doesn’t include the source, range, or value properties.
Important notes:
* onChange only runs when the Sheet is open; the Sheet will not update “in the background”
* You can call the function whatever you want; onChange is an installed trigger and must be installed from the Triggers section, which means it can be applied to any function
* You must use the PropertiesService before the onChange to set each key-value pair so the onChange script has a starting value
* Using the cell A1 notation as the key name makes it easier to process since the SpreadsheetApp can access the range via the key name

IMPORTRANGE Source Sheet (to copy from File : Make a copy)
https://docs.google.com/spreadsheets/d/1TG6CPeoN2eHtnrQSq_V0AvjNRSVGRsCjQq9Km-vVMHE/edit#gid=0

Testing sheet with script (to copy from File : Make a copy)
https://docs.google.com/spreadsheets/d/1JRaBvmdpHKOexUfp-UqQ5HwyPHvdDIkvkZavUHkHR0c/edit#gid=0
* Remember to update the IMPORTRANGE sheet ID

Script:
function onChange(e){
Logger.log(e.changeType);
if(e.changeType != “OTHER”) return;
let props = PropertiesService.getScriptProperties().getProperties();
const sh = SpreadsheetApp.getActive();
const ss = sh.getActiveSheet();

let r, val;

for (let p in props){
Logger.log(p);
Logger.log(props[p]);
r = ss.getRange(p);
val = r.getValue();

if(val != props[p]){
r.offset(1,0).setValue(props[p]);
PropertiesService.getScriptProperties().setProperty(“A2”,SpreadsheetApp.getActive().getActiveSheet().getRange(“A2”).getValue()); // run for each relevant cell
}
}
}

function setupProperties(){ // run for each relevant cell
PropertiesService.getScriptProperties().setProperty(“A2”,SpreadsheetApp.getActive().getActiveSheet().getRange(“A2”).getValue());
}

function retrieveProps(){
Logger.log(PropertiesService.getScriptProperties().getProperty(“A2”));
}

Connect with me:
• spencer.farris@gmail.com
• spencerfarris.me
• www.linkedin.com/in/spencer-farris/
• Twitter @FarrisSpencer
• Google Product Expert support.google.com/docs/profile/12305