Microsoft recently introduced Google Sheets as a data source for Power BI, enabling the visualization and manipulation of Google Sheets data on its platform.
1. Go to the Google Sheets spreadsheet that will be connected to Power BI and copy its URL.
2.Enter Power BI and click the “Get Data” button.
3.In the screen that opens, look for “Google Sheets,” select the corresponding option, and click “Connect.”
4.Paste the full URL copied in step 1 and click “Ok.”
5.Power BI will then ask you to connect to your Google account. Click on “Sign in.”
6.Select the Google account you wish to use for the connection. Attention: Ensure that the chosen account has access to the spreadsheet to be connected.
7.At this step, Google will ask you to confirm that Power BI can access your Google account. Click “Continue.”
8.You can now return to Power BI and click on “Connect.”
9.Select the tab(s) of the spreadsheet you want to connect and click on “Transform Data.”
10.We will make a small adjustment so that Power BI understands the first line of the Google sheet as a header. In the Power Query Editor that opens, click on the table icon (first square of the first line of the table), and then click on “Use First Row as Header.”
11.Note that now the first row is bringing the names of the columns from your Google sheet. Click on “Close and Apply” to exit Power Query.
12.Done! Now your Google Sheets data will be available in Power BI.
Updating Google Sheets Data in Power BI
- Power BI Desktop
To update the data from Google Sheets in Power BI Desktop, click the “Refresh” button on the Home tab.
- Power BI online
In premium versions of Power BI, you can set a scheduled update so that data is refreshed as frequently as you define. To do this, in the navigation pane, under “Dataset,” select the set, and then click on Update > Schedule Update.
Importing Data in Google Sheets to Connect it to Power BI
The best way to import data from Google Sheets to connect it to Power BI is by using a tool that performs these imports automatically so that you don’t have to worry about manually downloading data or creating complicated scripts.
Connect Google Sheets to Power BI
Use Power BI's native Google Sheets connector to load and refresh spreadsheet data, with Kondado feeding the spreadsheet from external sources.
Copy the Google Sheets URL
Open the spreadsheet you want to connect to Power BI and copy the full URL from the browser address bar.
Use Get Data > Google Sheets in Power BI
Open Power BI Desktop, click Get Data, search for Google Sheets, click Connect, paste the URL, and click OK.
Sign in with the right Google account
Click Sign in, choose the Google account that has access to the spreadsheet, and approve the consent screen so Power BI can read the file.
Pick tabs and promote headers
Select the tab(s) you need and click Transform Data. In Power Query Editor, choose Use First Row as Headers, then Close & Apply.
Schedule refresh and feed the sheet automatically
In Power BI Service, configure scheduled refresh on the dataset. To populate the sheet from external systems, use Kondado to replicate 80+ sources into Google Sheets at the frequency you choose.
