Google sheets scrips: create XML sitemap dropdown based on value selection

See more:

Javascript

google-app

Problem:
I would like to have the cell (column D) next to column F update to a dropdown list of the sitemap URLs based off the link next to the cell labeled "Staging Link" when column E is updated to "Open". This sheet will have multiple links so the logic will need to loop up to find the nearest staging link above the row being edited. My script linked from this is still pretty far off but was my starting point to work from. Thanks for any help!

Actions/Outcome:

1.) User updates column E to "1-Open"
2.) Script: Loops up to find nearest value of "Staging Link" above cell being edited
3.) Script looks up XML sitemap of URL next to cell "Staging Link"
4.) Script adds list Data Validation to Column D adjecent to cell that was originally edited. Data Validation is drop down list of URLS in sitemap from staging link.

What I have tried:

Links:
Google Sheets link

Google Apps Script link