If you're not limited to R Studio, there is another way to access Google Sheet data from Power BI:
- Get shareable link of the Google Sheet (Permission at least Anyone with the link can view):
2. Create a blank query in Power BI and get the source as below:
let Source = Excel.Workbook(Web.Contents("the-link-in-step-2"), null, true) in Source
- If you drill into the Table cell you should be able to see the data and work from there.