'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Fix Excel Power Query Importing Google Sheets as HTML
Question details
The user wants to import data from a Google Sheet into Excel 2021 using Power Query but receives an HTML page instead of the actual data table.
- Product
- Microsoft Excel 2021
- Device & OS
- not provided
- Scenario
- Importing a shared Google Sheets web link into Excel via the From Web Power Query feature.
- Observed behavior
- Excel imports the Google Sheets web page interface as an HTML document rather than extracting the raw spreadsheet data.
Ensure you have the exact sharing link for your Google Sheet and that its permissions are set to 'Anyone with the link' so Excel can access the data without requiring a Google login.
Use the Google Sheets Export URL Format
Changing the standard Google Sheets sharing link into a direct .xlsx export URL forces Power Query to read the file as a spreadsheet rather than a web page.
By default, Google Sheets URLs point to a web interface. Power Query reads this as an HTML webpage. By modifying the link to point to an export format, you bypass the web interface and allow Power Query to download and transform the raw Excel-compatible data directly.
Open your Google Sheet, click 'Share' in the top right corner, and change the general access setting to 'Anyone with the link'.
Copy the sharing link. Look at the URL and find the SPREADSHEET_ID, which is the long string of letters and numbers located between '/d/' and '/edit'.
Construct a new URL using this exact format: https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/export?format=xlsx (replace SPREADSHEET_ID with the code you found in the previous step).
Open Excel 2021, navigate to the 'Data' tab on the ribbon, and select 'From Web' in the Get & Transform Data group.
Paste your newly created export URL into the dialog box and click 'OK'. Power Query will now connect directly to the workbook data instead of an HTML page.
Try WPS Office for Seamless Spreadsheet Management
If you frequently encounter complex data connection errors or find Excel's advanced features difficult to navigate, WPS Office offers a free, lightweight alternative. The WPS Spreadsheet tool provides an intuitive interface that makes managing, viewing, and organizing data simpler without the steep learning curve.
Frequently Asked Questions
Why does Excel 'From Web' show an HTML table instead of my Google Sheets data?
When you use a standard Google Sheets viewing link, Excel connects to the web page wrapper (the HTML interface) rather than the raw data file. Modifying the URL to an export format bypasses the HTML wrapper and delivers the actual spreadsheet file.
Do I need to make my Google Sheet public to import it into Excel?
Yes, the sharing settings must be set to 'Anyone with the link' can view. If the sheet is restricted, Power Query will not be able to authenticate automatically and will fail to fetch the data.
Can I use this export URL method to auto-update data in Excel?
Yes. Once the connection is established using the .xlsx export URL, you can simply click 'Refresh All' on the Data tab in Excel to pull the latest updates directly from your Google Sheet.
