For question #2, scheduling is currently not supported, but it is planned for a future release -- place your vote here:
Once implemented, it sounds like your primary issues will be resolved. I'm not sure when it will be implemented but its definitely on the roadmap.
Hi Matt. Thanks for your answer. I placed my vote.
Do you also have an answer to my first question? I.e. do you know whether it is possible to let Tableau Prep automatically recognize new sheets?
Thanks in advance for your answer.
For the people who have the same issue as I had: an answer to the first question is to use the wildcard union in Tableau Prep. Please refer to https://onlinehelp.tableau.com/current/prep/en-us/help.html#prep_connect.html#Union for more information