This has changed my life! Thank you, @Jeff_Schneider!
Just as an additional bit of info, if anyone is looking to create a Power BI (desktop) report directly from a Google Sheet, here are the instructions (originally from: Solved: How to connect google sheet to Power BI - Page 2 - Microsoft Fabric Community)
1. Use Power BI desktop (this won’t work just on Power BI service you have to start on desktop).
2. Share Google Sheet and get link from sharing.
3. Paste Google Sheet shared link and it will end in something like “adfe/edit?usp=sharing”
4. Remove the /edit?usp=sharing off the url
5. then add export?format=xlsx&id= where the edit/? had previously started
6. then copy and paste the long id from the first part of your url
7. the long, final URL you should use for Power BI get from web will be something like:
“https://docs.google.com/spreadsheets/d/1nWV8adkjfadkfHWDIAa3ad/export?format=xlsx&id=1nWV8adkjfadkfHWDIAa3ad”
NOTE: id after equals sign matches id from Google for share sheet. (BTW this isn’t a real link just demonstration).
That’s it - now you can design in Power BI desktop and publish to Power BI service on web (if needed). Only downside is there’s no automatic refresh. Folks can edit / enter on Google Sheet but change won’t appear in Power BI Desktop until you click refresh and won’t appear in Power BI Service until you republish and overwrite.
I’ve been trying to set this up for ages, and then I finally found this answer and it works! So now I can set up an advanced search report for multiple projects, sync the report with Google Sheets, then build a flashy, management-pleasing Power BI report that refreshes at a click.
Perfect. 