How to Import and Automatically Refresh JSON Data in Excel
Question details
The user wants to fetch data from a JSON web source into Excel and set the spreadsheet to refresh the data automatically at a specific time interval.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Pulling live or regularly updated data from a web URL that returns JSON, and keeping the Excel workbook continuously synchronized without manual intervention.
- Observed behavior
- The JSON data is successfully pulled into Excel, but it fails to update automatically in the background even though the source web page refreshes regularly.
Verify that your data source URL directly returns a valid JSON payload (not an HTML page containing JSON) and ensure you are using an Excel version that supports Power Query and automated background refreshes.
Import and Transform JSON Data via Power Query
Use the built-in Power Query tool to connect to the web API, extract the JSON data, and transform it into a readable table format before loading it into your worksheet.
Power Query can natively parse JSON structures, provided the web source returns plain JSON formatting without extra characters or wrapper scripts.
Some websites require authentication or block Excel's default web scraping agent. Ensure the web source allows public programmatic access.
Open Excel and go to the 'Data' tab. Click on 'Get Data' > 'From Other Sources' > 'From Web' (or directly select 'From Web' if visible).
Paste the exact URL that points to the JSON feed into the dialog box and click 'OK'.
Once the Power Query Editor opens, click 'To Table' in the Transform tab to convert the JSON record into a table structure. Use the expand icon (two diverging arrows) on the column header to reveal specific fields.
After cleaning the data and removing any unnecessary wrapper layers, click 'Close & Load' on the Home tab to import the finalized data into your Excel sheet.
Configure Automatic Refresh Intervals
Modify the properties of your imported query connection to enable automatic updates at designated time intervals or upon opening the workbook.
Try WPS Office for Seamless Spreadsheet Management
Advanced data connectivity like Power Query often depends on specific Microsoft 365 enterprise subscription tiers (like ProPlus). If you are looking for a reliable, fast, and free alternative to manage your standard spreadsheets and complex formulas without version limitations, WPS Office provides an exceptional suite of tools.
- 1. Download the Installer: Visit the official WPS Office website and download the free installation file for your operating system.
- 2. Install WPS Office: Run the installer and follow the on-screen prompts to complete the setup process in minutes.
- 3. Open Your Spreadsheets: Launch WPS Spreadsheet and instantly open your existing Excel workbooks with perfect formatting retention.
Frequently Asked Questions
Why does my JSON web query return an HTML page instead of raw data?
This happens when the URL provided points to a web page that renders the JSON visually, rather than the raw API endpoint itself. Make sure your URL targets the direct JSON data feed. Additionally, some sites block Excel's automated requests, returning an HTML error page instead of the requested JSON.
Do I need a specific Excel version to use Power Query for JSON imports?
Yes, standard Power Query functionality is available in Excel 2016 and newer. However, automated background refreshing for web data and more advanced Power Pivot features are sometimes restricted to specific editions like Microsoft 365 ProPlus or Enterprise subscriptions.
Why is the 'Refresh every X minutes' option grayed out?
This option can be grayed out if background refresh is disabled for that connection. In the Connection Properties window, ensure 'Enable background refresh' is checked. If it is still unavailable, the specific data source or your current Excel license might not support timed automatic updates.
Will my JSON data refresh automatically when the Excel file is closed?
No, if the file is simply saved on your local hard drive, Excel cannot execute the query while the file is closed. To refresh data without having the desktop app open, the file must be hosted on a cloud service like SharePoint or OneDrive for Business with scheduled refresh configured via Power BI or Excel Online.
