How to Import JSON to Excel Without Coding

JSON (JavaScript Object Notation) is a popular data format that is widely used for storing and exchanging data between web applications. On the other hand, Excel is a powerful spreadsheet tool that is used for data manipulation and analysis. Importing JSON to Excel provides a convenient way to leverage the analysis and visualization capabilities of Excel. With the right approach and tools at your disposal, this process can be streamlined and automated, allowing you to save time and focus on deriving valuable insights from your data.

In this article, we will explore different methods to import JSON data to Excel so that you can make an informed decision based on your specific needs. For one of the methods, Coupler.io, we’ve created a video tutorial, which you can watch right away.

Options to import JSON to Excel

Basically, there are three methods for importing JSON data into Excel, each offering its own advantages:

• Coupler.io: With this data automation platform, you can automate the process of importing JSON to Excel, apply transformations and filtering to your data, and load it directly into Excel on a customized schedule.

• Power Query: You can import JSON data stored in your local machine or query the JSON URL using Excel’s built-in tool.

• VBA (Visual Basic for Applications): ​​This coding option is well-suited for Excel users with a strong technical background. With VBA, you can write custom scripts to import JSON to Excel. 

Let’s explore each option in detail. Imagine you’re working at a marketing agency, and your team has been gathering customer feedback through a survey. All the survey responses are saved in this online JSON file. Your task is to analyze and present the data, in Excel to gain insights, for your client. Now you need to decide on the way to import this JSON file to Excel. We suggest using a method that automates the process of importing JSON into Excel and allows you to schedule imports of JSON data. Let’s explore how this works.

How to automatically import JSON to Excel

Coupler.io is a data analytics and automation platform that enables you to automate the import of JSON data to Excel. The platform offers a data integration solution that supports exporting data from over 60 different data sources and transferring it to various destinations such as Google Sheets, Excel, Looker Studio, or BigQuery. You even have the flexibility to customize the import frequency, ensuring that you always have access to up-to-date data whenever you need it.

Let’s now see how the Coupler JSON integration with Excel works.

• Sign up for Coupler.io with your Microsoft account. Click Add new importer.
• Select JSON as the source application and Microsoft Excel as the destination application. 

Extract Data

• Insert the URL to your JSON file in the JSON URL field.
• Click Continue.

• For the HTTP method, select GET.
• If your file is password-protected, you will need to authorize it by providing your API key in the Request headers.
• Additionally, you can also make use of the other optional settings – for instance, select the specific fields to be imported, specify the number of rows to skip, etc.

If those settings are not required, click Finish and proceed to go to the next steps.

Transform Data

In the Transform data section, you can preview your data and rearrange it if needed – hide or rename columns, add columns, filter and sort the data, calculate custom metrics directly, and so on.

• Once everything looks good, click Proceed in the upper right corner to continue.

Manage Data 

• In the Manage Data section, connect to your Microsoft account, grant the requested permissions, then click Continue.
• Select a workbook and a worksheet where the data will be loaded. You can select an existing Excel worksheet or create a new one.

• You have the flexibility to choose the import mode from the following options:
  • Replace: the old data will be replaced by fresh data every time you import.
  • Append: the old data will remain unchanged, and the fresh data will be added to the sheet during each import.
• Additionally, you can toggle the last updated column to configure the importer to include a column for the date and time of each refresh. This allows you to track when the data was last updated. You can also choose to skip the data refresh if there have been no changes since the previous import.
• Click on Finish and Proceed once you are satisfied with your settings.
• At this point, you have completed the setup process, to get your JSON data, click Save and Run.

You can optionally toggle on Automatic data refresh and customize the schedule for your data imports. That’s it! Below is the survey result that we imported from JSON to Excel.

How to import JSON to Excel with Power Query

Power Query is a data transformation and connectivity tool available in Excel. It allows you to shape, clean, and transform your data from various sources, including JSON files, databases, web pages, and more. However, the main drawback of PowerQuery is that it’s only available for the Excel desktop app. Within Power Query, there are typically two methods to import JSON into Excel.

How to import a JSON file to Excel using Power Query

Using the From JSON option, you can import data from JSON files stored in your local machine to Excel. To do this:

• Navigate to the Data tab.
• Click on Get Data. 
• From the drop-down menu, choose From File and then select From JSON.

• This will open the import data dialog box. In the dialog box, locate the JSON file you want to import and click on Import.

• Excel will open a Power Query Editor window. Here, you will preview your data and decide how JSON’s data will be loaded into your spreadsheet

• In the Query Editor, you can click on the Transform option and then select To Table to convert the JSON data into a table format. This conversion allows you to perform various transformations on the data before loading it into your Excel workbook. 

• You might notice that the values are currently hidden in the Query Editor. This is because you need to select which columns you want to display. To do this, click on the Expand button, which will allow you to choose the specific columns you want to include and make their values visible in the editor. 

• At this point, you have the flexibility to modify your data. For this example, we deselected the Use column name as prefix option to remove the default column name prefix and add your custom column names. This gives you control over how your data’s named. Additionally, you can select columns that you want to see in the table. After making these selections, click OK.

• Now you can observe that your data has been properly loaded into rows and columns in the Query Editor. To proceed with loading this transformed data into Excel sheets, Click on Close & Load.

Here is your JSON data after it has been imported.

How to import data from JSON URL using Power Query

To import data from a JSON URL using Power Query, you will need to provide the URL of the online JSON file you wish to import and follow these steps:

1. Navigate to the Data tab. 
2. Click on Get Data and select From Other Sources. 
3. Choose the option labeled From Web. 

• Enter the JSON URL.

5. Click on OK to import the JSON data. 
6. Choose the web content access. For this example, we will go for Anonymous. Click Connect.

• The Power Query editor will open, allowing you to preview the data loaded from the JSON URL.

From here, follow steps 6-9 in the above section as they are the same.

Importing JSON data to Excel using VBA

VBA (Visual Basic for Applications) is a programming language integrated into Excel that allows you to automate tasks, create custom functions, and enhance Excel’s functionality through code. With VBA you have the ability to create macros that can automate tasks or handle operations. When it comes to importing data from JSON files to Excel using VBA you usually need to write custom code that interacts with the JSON file and processes the data. The advantage of using code is that it can be tailored to suit your requirements. If you are familiar with VBA you can customize the code according to your needs. However, if you are new to VBA it may require some time and effort to grasp the basics. Don’t worry though we have prepared a guide that simplifies and explains the fundamentals of using Excel VBA.

Import JSON Data to Excel – Choosing the Best Method

The best choice to import data from JSON to Excel depends on your requirements and skillset. If you prefer a user interface and don’t have programming experience Power Query is an excellent option. On the other hand, if you need automated and scheduled imports without coding knowledge, Coupler.io is a go-to solution. It offers a no-code approach where you can easily import JSON data and other types of data into Excel.

For those who’re proficient, in Excel and have programming skills then VBA is a good option because it allows you to write your own code and automate various tasks within Excel. Out of all the methods we’ve discussed the no-code approach is generally favored by many, including ourselves. It offers advantages, such as a selection of pre-built integrations and the ability to import data into other applications like Google Sheets, BigQuery, etc. You can give Coupler.io a try at no cost, with a 14-day trial period.

Streamline your data analytics & reporting with Coupler.io!

Coupler.io is an all-in-one data analytics and automation platform designed to close the gap between getting data and using its full potential. Gather, transform, understand, and act on data to make better decisions and drive your business forward!

• Save hours of your time on data analytics by integrating business applications with data warehouses, data visualization tools, or spreadsheets. Enjoy 200+ available integrations!
• Preview, transform, and filter your data before sending it to the destination. Get excited about how easy data analytics can be.
• Access data that is always up to date by enabling refreshing data on a schedule as often as every 15 minutes.
• Visualize your data by loading it to BI tools or exporting it directly to Looker Studio. Making data-driven decisions has never been easier.
• Easily track and improve your business metrics by creating live dashboards on your own or with the help of our experts.

Try Coupler.io today at no cost with a 14-day free trial (no credit card required), and join 700,000+ happy users to accelerate growth with data-driven decisions.

Start 14-day free trial