Even though this Stack Overflow trend shows a declining usage of XML and the fast-rising adoption of JSON, this statistic also tells us that XML is still in use today.
Unarguably, XML uses a syntax that bears a striking resemblance to that of HTML. Regardless, they differ in their purposes.
Unlike HTML that’s presentation-focused, XML is optimized for data transfer. XML is verbose and by extension hard to read. As a result, sometimes it's smarter to import XML to Google Sheets (for example). Beyond just reading, being able to load such data into a tool like Google Sheets quickly, would streamline analyzing and drawing useful insights from this data.
But how can we import XML to Google Sheets?
This article will uncover three different ways of extracting data from XML to Google Sheets. Now, back to your question…
How to import XML to Google Sheets?
We can import XML to Google Sheets in at least three different ways:
- With the Google Sheets IMPORTXML function
- With Google Apps Script
- Automation with n8n
IMPORTXML function to connect XML to Google Sheets
Even though Google Sheets has native support for importing excel, CSV, and text files amongst others, if you try to directly import an XML file, you’d get an import error. In the image below, I got an import error when I tried to import an XML file, books.xml
However, all hope is not lost. Google Sheets comes with an IMPORTXML function out of the box. Generally, the function allows importing structured data like XML, HTML, etc. into Google Sheets using a permitted syntax.
To import XML to Google Sheets with the Google Sheets IMPORTXML function, write your IMPORTXML query in this manner: IMPORTXML("url", "xpath_query")
A couple of things to unpack:
- “url” — This is the URL of the XML file whose data we want to extract to Google Sheets.
- “xpath_query” — The string that tells the IMPORTXML function what data to import into Google Sheets. For example, if we specify “h2” as the xpath_query, the function will scrape all H2 headings and load them into Google Sheets.
Additionally, we could also use the wild card (”/*”) xpath_query to scrape and load all the content of an XML file into Google Sheets.
To concretize this, let’s consider an example.
Importing a food menu from XML to Google Sheets
Given this XML file, that’s a breakfast menu with a list of different varieties of food, let’s load all the food into Google Sheets. If you look at the XML file, you’d see that each food is wrapped within the <food> tag.
To import XML to Google Sheets in this case, open your Google Sheets, select a cell and paste the snippet below.
=IMPORTXML ("<https://www.w3schools.com/xml/simple.xml>", "//food")
In the snippet above, the first argument is the URL of our sample XML file, and /food, the second argument, is the xpath_query— it scrapes and loads all the items wrapped around the <food> tag in our XML file.
The snippet above should load all the food on the menu into Google Sheets as shown in the image below.
Now, if instead of //food you pass /* as the xpath_query, all the data in that XML file would be loaded into the Google Sheets.
Cool, right? Well, the IMPORTXML function is flawed in one major way. If you try to pass the URL of an XML file hosted in your Google Drive to the function, it will throw an #NA error. For example, replace the URL of our sample XML file with the link to this XML file that’s hosted in Google Drive and publicly available. You should get an error.
This is where the other IMPORTXML alternatives come in. Let’s consider these alternatives.
Google Apps Script to connect XML to Google Sheets
Because the IMPORTXML function fails in scenarios where you need to import an XML hosted in your Google Drive to Google Sheets, use the Google Apps Script in such cases. Hold on— Google Apps Script?
The Google Apps Script is a cloud-based service that allows you to write custom JavaScript, called scripts that interact with Google products like your Gmail, Google sheets, Google docs, etc. It adds an extra layer of custom functionalities to these products.
Importing a books list from XML to Google Sheets
To import XML to Google Sheets with the Google Apps Script, the first thing to do is grab the ID of the XML file hosted in your drive. Since in our case we are going with this XML file hosted in my drive, the ID is 1VDgIFU5c3pKyHU58kt3kt4cEQvU8KBr8. The file contains a list of books in a catalog
Next, go to the Google Sheets you want to import the XML into and click Extensions → Apps Script.
On the Apps Script interface copy and paste the snippet below into the code.gs file.
function xmlParser() {
const fileId = "1VDgIFU5c3pKyHU58kt3kt4cEQvU8KBr8";
const data = DriveApp.getFileById(fileId).getBlob().getDataAsString();
let document = XmlService.parse(data); //have the XML service parse the document
let root = document.getRootElement(); //get the root element of the document
let foods = root.getChildren("book"); //gets the list of books in the catalog
let list = [] //we create an array that will hold the data
foods.forEach(function (food) {
fields = food.getChildren()
subList = []
fields.forEach(function (field) {
subList.push(field.getText())
})
list.push(subList)
})
writeToSheet(list)
}
function writeToSheet(list) {
let range = SpreadsheetApp.getActiveSheet().getRange(1, 1, list.length, list[0].length)
range.setValues(list)
}
Running the snippet above would load the list of books in that XML file into your Google Sheets. See the image below.
Also keep in mind that in addition to parsing XML files hosted in a Google drive, with Apps Script, you can pass the link to pretty much any XML file. If the Apps Script is this robust why then do we need a second Google Sheets IMPORTXML alternative?
Well, we do because using Apps Script requires writing some JavaScript code that could import XML to Google Sheets. Evidently, this approach is not ideal for non-technical persons as it requires some coding experience.
Since the IMPORTXML function fails in reading XML content hosted in Google Drive, Apps Script, on the other hand, is a little bit technical. Thus, we need something that solves these problems. A third option that’s flexible enough to parse pretty much any URL to an XML file, while requiring very little or no coding experience.
Enter n8n.
Automatically import XML to Google Sheets with n8n
We will demonstrate importing XML into Google Sheets with n8n in this section by using this workflow that reads an XML file and stores its content in Google Sheets. The workflow creates Salesforce accounts and contacts based on data coming from Google Sheets.
Pre-requisites
- n8n set up. The easiest way to get started is to sign up for n8n cloud, but you can also self-host n8n.
- Generally, make sure you are running n8n 0.197 or newer
Take the following steps to set up and run the workflow.
Step 1: Import the workflow
- Click on the Use workflow button on the import XML to Google Sheets workflow page. That action would copy the workflow to your clipboard.
- Go to your n8n editor UI and paste the copied workflow. Your editor UI should be updated with the workflow as shown in the image below.
Notice how all the Google Sheets nodes are highlighted in red. Configure them before proceeding to the next step.
Configuration here just entails adding your Oauth2 credentials for Google. Learn how to authenticate the Google Sheets nodes here.
Step 2: Add your spreadsheet ID
The Write header row and Write data to sheet nodes require adding your Google Sheet ID in addition to Oauth2 credentials. Take the following steps to do that.
- Copy the ID of the Google Sheets you want to import your XML data into. Refer to step 1 of this tutorial to learn how to grab the ID of a Google Sheet.
- Add the ID to the Write header row and Write data to sheet nodes.
Step 3: Run workflow
Hit the Execute Workflow button and the list of foods in our sample XML file should be loaded into your Google Sheets.
Wrap up
In this article, we’ve explored three different ways to import XML to Google Sheets, namely:
- With the Google Sheets IMPORTXML function
- With Google Apps Script
- Automation with n8n
Even though the IMPORTXML function is great, it’s not compatible with XML files hosted on Google drives. Google Apps Script on the other hand is robust and flexible but requires some JavaScript experience to work with.
In the end, we’ve seen how n8n sets itself apart from all these other tools because it works easily with just any XML URL (even XML files hosted in a Google drive). And most importantly, it requires very little or no technical experience.
What’s next?
Even though in this article we focused mostly on how to import XML to Google Sheets, n8n also has a bunch of other XML-related workflows. For example, there is a workflow for transforming XML data and uploading it to Dropbox.
Ready to turn your routine tasks into automated workflows?
Sign up for the n8n cloud account to easily get started and start automating processes with n8n! The platform also offers hundreds of pre-built integrations and hundreds of automation templates, allowing your team to design the custom workflows you need.