🧭 Step-by-Step Guide
🧩 Step 1: Get a “Direct Download” Link from Google Drive
Let’s say your file is in Drive like this:
You need to convert it to a direct export link:
✅ For a .csv or .xlsx file:
Replace the part:
with:
👉 Example:
That’s the URL you’ll use in Power Query.
🧩 Step 2: Open Power Query in Excel
-
Open Excel.
-
Go to the Data tab.
-
Click Get Data → From Other Sources → From Web.
🧩 Step 3: Paste the Google Drive Link
-
Paste your converted link, e.g.
-
Click OK.
🧩 Step 4: Load or Transform Data
-
Excel will connect and show a preview.
-
If it’s a CSV: you’ll see table data right away.
Click Load (or Transform Data to clean it first). -
If it’s an XLSX: Power Query might ask for the sheet/table name first — select it.
🧩 Step 5: Refresh Anytime
Once loaded:
-
Go to Data → Refresh All
to pull the latest version from Google Drive (if the Drive file was replaced or updated).
🧠 Optional: Automate the Refresh
You can set Excel to auto-refresh on file open:
-
Go to Data → Queries & Connections.
-
Right-click your query → Properties.
-
Check ✅ “Refresh data when opening the file”.
⚠️ Important Notes
| Type | Works with | Notes |
|---|---|---|
.csv | ✅ Perfect | Loads cleanly and quickly |
.xlsx | ⚠️ Sometimes | Google Drive doesn’t stream XLSX perfectly; better to export via Google Sheets |
| Google Sheet | ✅ Using export link | Use special link format (below) |
🔗 Bonus: If the file is a Google Sheet
If your data is stored in a Google Sheet, use this format instead:
You can get gid from the URL of the sheet tab:
Then use the link:
Paste that into Power Query “From Web”.