The downside of this approach is that the data in BigQuery is not real time. Technically, you will upload your sheets to BigQuery (all locked under your accounts) and then you will connect to BigQuery from Power Query (Power BI) to get your data.
Install BigQuery Uploader for Google Sheets for your Google Account.
Make yourself BigQuery admin in the Google Cloud Project.If you absolutely need to keep your Google Sheet private, then this is the process:
Let’s look at both options in more detail. Insecure option – you’ll have to make your Google Sheet public so anyone with the link can access it but the process is relatively easy.Secure option – you’ll be able to keep the Google Sheet private but the process is a bit cumbersome.You want to be using the reference sheet stored in Google Sheets since it is always the latest and the only correct version.You would like to use this reference sheet as data source in Power Query (Power BI).You store reference sheets via Google Sheets files where multiple people can work at the same time.Google Sheets have replaced Excel in a lot of companies so how do Google Sheets get along with Power Query (Power BI)? Actually pretty well.