Solution 1: Change the google share link
Google share must be allowed everyone. At the end of the link, change from
depends on the file extentions.
Ruth from Curbal posted a youtube demo and an invoked function to make it even easier.
Solution 2: Use the CData ODBC Driver (NOT a free tool)
More detail can be found here: https://www.cdata.com/kb/tech/googledrive-odbc-power-bi.rst
The following steps can be followed to connect Google Drive to Power BI:
- Start by specifying your connection in an OCBC DSN. All Google APIs can be integrated on behalf of either an individual or an organization, as per your requirement.
- Once the DSN has been created, you can proceed with connecting the Google Drive DSN from the Power BI system. You can download the Power BI Desktop from here by clicking on “Download” and then “Power BI Desktop”.
- You need to open Power BI Desktop and click on “Get Data”.
- Select the ODBC option in the panel.
- From the menu, select the concerned DSN. You can also expand the “Advanced Options” and use SQL queries that you want to use to import data.
- In the navigator dialog box, select “tables”.
- You can edit the query of the imported table in the “Query Editor”. Here, you can connect your Google Drive data with other data sources for collaborative manipulation.
- Click on “Load” to pull data into Power BI and carry out any data executions as required.
After connecting your Google Drive to Power BI, you can visualise it according to your need in Power BI.
Also if possible then convert to CSV and try to connect because the size will reduce and then you can easily connect.
Even though a step-wise procedure can enable a manual connection of Google Drive to Power BI, there are significant challenges that can be encountered with future updates and alterations.
You might need to set a refresh on schedule or even execute manual refreshes as per demand for data to be kept consistent with the source updates. It can be a cumbersome process for large organisations where thousands of updates are being made by the second, and a manual monitoring process seems to be unfavourable.