Most demonstrations of using as an excel sheet as a datasource for Power BI will assume the excel sheet is locally stored. This is what happens when you click Get Data and Excel in Power BI desktop.

Eventually you will want to have this auto refreshing and in order to do this you could install a data gateway on a machine/server. It is much simpler however, to simply change the datasource from an Excel one to a Web one, and let the schedule refresh take place entirely in the cloud.

If you go to Power Query and click on the first step, source, it will look like this:

= Excel.Workbook(File.Contents("C:\Users\DamienDevaney\Documents\YOUR_SPREADSHEET.xlsx"), null, true)

All we need to do is change it like this:

= Excel.Workbook(Web.Contents("https://YOUR_DOMAIN.sharepoint.com/sites/YOUR_SITE/Shared%20Documents/YOUR_SPREADSHEET.xlsx"), null, true)

Now when you click schedule refresh in the the PBI service it will work without any data gate. So you can share and collaborate on a spreadsheet and have it update Power BI visuals on a schedule.