Is it possible to write a script to open the Files app to a specific folder in iCloud Drive or any other location (E.g. Dropbox, G Drive, etc.) connected within the Files app?
No, but that comes down to how automatable the Files app is rather than how capable apps like Scriptable are. But, if you are looking to do something in the Files app that is repeatable (e.g. moving the same / latest file somewhere), then you may well be able to automate that activity by accessing the file storage directly with Scriptable, and never opening the Files app at all.