Connect Google Sheets to Excel

by

You can pull a Google Sheet into Excel with Power Query and refresh it whenever the sheet changes: no download, no copy-paste. The trick is one small change to the sheet’s share link.

Works inExcel 2016 and later, Microsoft 365 (Windows); Excel for Mac with Power Query
NeedsA Google Sheet shared as “Anyone with the link”
RefreshOne click, on open, or every few minutes

Video

Step 1: Get the share link

  1. Open the Google Sheet and click Share.
  2. Under General access, choose Anyone with the link (Viewer).
  3. Click Copy link, then Done.
Google Sheet with sample sales data and the Share button
Google Sheets share dialog with Copy link

Step 2: Change the end of the link

The copied link opens the sheet in a browser. Excel needs a download link instead, so replace the end of it:

Copied link ends with Change it to
/edit?usp=sharing /export?format=xlsx

For example, https://docs.google.com/spreadsheets/d/ABC123/edit?usp=sharing becomes https://docs.google.com/spreadsheets/d/ABC123/export?format=xlsx. Everything before /edit stays the same.

Step 3: Import it with Power Query

  1. In Excel, go to Data → Get Data → From Other Sources → From Web.
  2. Paste the changed link and click OK.
  3. If asked, choose Anonymous in the Access Web Content dialog and click Connect.
Excel Data tab, Get Data, From Other Sources, From Web
From Web dialog with the Google Sheets link ending in export?format=xlsx
Access Web Content dialog with Connect button

The Navigator lists every sheet in the Google workbook. Select the one you want.

Power Query Navigator previewing the Google Sheet data

Step 4: Fix the headers and load

If the preview shows Column1, Column2 as headers, click Transform Data, then Home → Use First Row as Headers. Check each column’s data type, then click Home → Close & Load.

Power Query Editor showing the imported Google Sheets data
Use First Row as Headers command in Power Query
Google Sheets data loaded into an Excel table

Step 5: Refresh when the Google Sheet changes

Edit the Google Sheet, then in Excel click Data → Refresh All (Ctrl + Alt + F5). New rows and columns come through.

Excel refreshing to show new columns added in Google Sheets

To refresh automatically, go to Data → Refresh All arrow → Connection Properties and tick Refresh every _ minutes and Refresh data when opening the file.

Connection Properties dialog with refresh every n minutes and refresh on open

Only one sheet, as CSV

To import a single tab, use its gid (the number after #gid= in the browser address bar) and request CSV:

https://docs.google.com/spreadsheets/d/ABC123/export?format=csv&gid=0

Use this link in Data → From Web the same way. CSV is quicker for large sheets.

Things to know

  • Anyone who has the link can view the sheet. Don’t use this for private or sensitive data.
  • Changes only go one way: from Google Sheets into Excel. Edits in Excel are overwritten on the next refresh.
  • If refresh fails with an access error, check the sheet is still shared as Anyone with the link.
  • Add your own formulas in columns next to the loaded table, not inside it, so a refresh doesn’t remove them.

Frequently asked questions

Can I connect a private Google Sheet?

Not with this method. Power Query’s web connector can’t sign in to Google. Keep the sheet shared by link, or export it from Google Drive.

Does it work in Excel for the web?

Excel for the web can refresh some queries but can’t create a web query. Set it up in desktop Excel first.

Related