Issues with scheduled refresh / gateway

Hi all, 

My company is making the move from excel to power bi. I have experience creating reports in power bi but the background side of things is new to me and our IT consultants seem to have limited experience with power bi. I'm hoping someone on here can help with a couple of questions I have. 

Reports that are using SQL as data source are refreshing perfectly fine but I have troubles with other data sources. 

When trying to schedule a refresh for a report using sharepoint I get this error message:"Scheduled refresh is disabled because at least one data source is missing credentials. To start the refresh again, go to this dataset's settings page and enter credentials for all data sources. Then reactivate scheduled refresh."

I go to the dataset settings to enter credentials and add the gateway but I then get this error: "We could not register this data source for any gateway instances within this cluster."

I am using sharepoint for many reports so this issue is blocking the refresh of all of them. 

Another report is using excel as the main data source. I believe excel can't be added to an on premises gateway. Is that right? 

I get the same credentials error message. When I go to the dataset settings:

And when I edit credentials for the excel files:

I know I could get around these issues by refreshing through desktop and republishing but I was hoping that we could automate as much as possible.

In a few weeks I will be off work for several months, not sure if this could have an impact on the scheduled refreshes? Or on refreshes through the personal gateway (if there's any)? What about when my password expires? 

Thanks a lot in advance for your help!