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.