Google Drive files list in Google Sheet

I have a google sheet, with some data (rows and columns).

I want one column to have dropdowns in all cells. These drop downs will show list of files from a Google Drive folder and sub-folders. User should be able to select a file from this dropdown. This will become a hyperlink to the file. i.e. clicking this link will open the file picked from the dropdown.

I need the script, so that I can configure the name of Google Drive folder.

Automatic updates: The list should be updated automatically, so any changes in the Google Drive folder will be reflected in the Google Sheet dropdown.


Example:

My Google Drive has following two files and two folders containing another 3 files. So the dropdown should show the following:

file1.pdf
file2.pdf
Folder1/file3.pdf
Folder1/file4.pdf
Folder2/file5.pdf

If you want, i can give you access to my google drive folder.






Ideal skills and experience for this job:

- Proficiency in working with Google Drive and Google Sheets.
- Experience in automation and data synchronization between Google Drive and Google Sheets.
- Attention to detail to ensure accurate and up-to-date file listing.

Please provide examples of similar projects you have completed in the past.