POWER QUERY : POWER BI : Connect Google Drive Sheet (3 WAY)

power bi and power query connect to google drive file, google drive file download link, power bi and power query connect to google drive file

WHAT IS GOOGLE DRIVE TO POWER BI AND POWER QUERY?

POWER BI and POWER QUERY provides several types of data connection procedures to pull data into the POWER QUERY EDITOR, connection between Google Drive and Power Query is one process of them. Users can import/pull data from GOOGLE DRIVE files to Power Query Editor, process the data, and create a visualization in POWER BI or create a report on EXCEL.

POWER QUERY is a common platform for both POWER BI and EXCEL. 

Many times, this question comes to me "HOW TO IMPORT DATA FROM GOOGLE DRIVE TO POWER BI?"

In this tutorial, we will get details

1. Connection Between Google Drive and Power Query (for POWER BI)
2. Connection Between Google Drive and Power Query (for MS EXCEL)
3. Create Google Drive File Download Link
4. Synchronize Google Drive to Computer or Gateway

• [message]
• ##check## Find POWER QUERY Tutorial List
• POWER QUERY Step by Step Beginner Guide Link

PROCESS 1 - SHARE THE FILE

STEP 1 - Login Google Drive

STEP 2 - Right click the file you want to import data and click on SHARE. Share the file to anyone. Then click on Done.

[lock]

STEP 3 - You will copy the link like below

https://drive.google.com/file/d/1FM3HMx7mIzdAFLGuIOfpnLLIDsNoaLZa/view?usp=sharing

The Yellow highlighted area is your file id.

[/lock]

You need to replace the file ID in the below link

https://drive.google.com/uc?export=download&id=YOUR_FILE_ID

After replacing your final code, you will get a link like below

[https://drive.google.com/uc?export=download&id=1FM3HMx7mIzdAFLGuIOfpnLLIDsNoaLZa]

STEP 4 - Copy the link. Go to POWER QUERY window in POWER BI or in EXCEL

(How you find POWER QUERY Editor in POWER BI & EXCEL 2010 to OFFICE 365)

STEP 5 - Select Data Source from WEB

STEP 6 - Paste the Link (created in step 5) and click OK

STEP 7 - If ask for the credential, you can use ANONYMOUS or WINDOWS PASSWORD. Regarding other options, I will make another article on CREDENTIAL. 

• Message
• Please be sure that, you done EMAIL NEWSLETTER subscription. So you will get notification for every article.

STEP 8 - Click on Sheet and click the Ok. You will find that data from GOOGLE DRIVE FILE or GOOGLE SHEET will be populated in the POWER QUERY window. After this step, you can populate data in your POWER BI or EXCEL.

PROCESS 2 - PUBLISH THE FILE

STEP 1 - Open the file in Google Sheet

STEP 2 - Go to file and click on PUBLISH ON WEB option

STEP 3 - Select the option as below image and click on Publish

STEP 4 - Copy the link

FOLLOW SAME STEP AS DESCRIBE PROCESS 1

STEP 5 - SAME AS PROCESS 1

STEP 6 - SAME BUT USE NEW LINK (which is created in Process 2 - Step 4)

STEP 7 - SAME AS PROCESS 1

STEP 8 - SAME AS PROCESS 1

PROCESS 3 - SYNC GOOGLE DRIVE FILE

I suggest choose Process 3 and it is highly secured because users do not require to share your GOOGLE DRIVE FILE or GOOGLE SHEET with anyone and publish to the web. If you choose Process 1 or Process 2, it may create a problem for confidential data because users sharing the file publicly. 

Where Process 3 is completely secure and it is a quick process to import data into power query. You can uninstall and stop sync anytime you want. 

STEP 1 - Go to https://support.google.com/a/answer/7491144?hl=en

You can read the instruction written over the webpage

STEP 2 - Find the WINDOWS or MAC section and download the software (which is applicable). I am using WINDOWS, so let's download the software.

After download, install the software. The installation process is common as usual. 

STEP 3 - After Installation, Run the Google Drive Stream file and you will find the one Drive created in your computer.

STEP 4 - Now, You can use Google Drive as your Hard Disk Drive. 

Now you can select the Source file as EXCEL and fetch/import data directly from the GOOGLE DRIVE or GOOGLE SHEET.

Select DATA SOURCE as EXCEL, not WEB

STEP 5 - Select GOOGLE DRIVE  file from your local hard drive and click on IMPORT

STEP 6 - You will find data load or import to POWER QUERY

STEP 7 - Complete Data Loaded to the POWER QUERY. Now users can load the data into POWER BI or EXCEL.

STEP 8 - If you checking the source of the data, it will show data loaded from Hard Disk Drive where actual data are store in the GOOGLE DRIVE

• [message]
• ##bullhorn## Is This Article HELPFUL
• Please comment YES or NO in below comment box. Your feedback is important, please share your Feedback, Question, Idea in below comment box
• If this article helpful, please find the share option below and share with your friends. You can copy the below link and paste it in Whatsapp, Facebook, Twitter, Linkedin & other social network site.
• Please CLICK HERE to subscribe our newsletter and you will get notification of all update and new articles.