XML stands for Extensible Markup Language, a format for storing and sharing data that is used by several applications.
It is comparable but different from HTML.
- XML is used to transport data, while HTML is used to display it.
- HTML makes use of preset tags such as <p> or <h1>, while XML makes use of any tags created by the author.
If you attempt to import an XML file in Google Sheets, you will get an error warning. It only supports Excel, CSV, and text files, not XML. However, with Google Sheets, you can import your XML data and interact with it. In this blog post, we’ll show how conveniently this can be done.
How can you import XML data to Google Sheets?
Copying and pasting the XML file’s contents into Google Sheets is inefficient since you need to get rid of lots of tags. So, the following two options are available to load XML to Google Sheets:
IMPORTXML function: Google Sheets IMPORTXML function is used to import structured data, such as XML, TSV, and HTML, from multiple sources.
Google Apps script: Writing little bits of custom code to load XML data into Google Sheets is an effective method. Although scripts take more technical expertise to write and set up, a vibrant community is available to assist you.
IMPORTXML function to connect XML to Google Sheets
Google Sheets has a built-in function called IMPORTXML to import XML to Google Sheets.
IMPORTXML syntax
=IMPORTXML("url", "xpath_query")
url: The website’s URL from which the data is being imported.xpath_query: The string that instructs the function on the kind of data we are attempting to import. XPath query is used to work with structured data.
Common XPath queries include:
//h2 – Scrapes all H2 headings
//title – Scrapes all titles
//@href – Scrapes all links
You can learn more about XPath queries and get familiar with its syntax here.
Example of how to open an XML file in Google Sheets using the IMPORTXML formula
To get started with IMPORTXML, complete the following steps:
- Open your XML file on your browser and copy its URL. For this example, we used this file.
- On your Google Sheet, select a cell and type
=IMPORTXML( - Inside the bracket paste your URL in quotation marks:
=IMPORTXML ("https://www.w3schools.com/xml/note.xml",
For your xpath_query, type "/*". This enables you to get all the file data into your Google Sheets document. Your formula should look like this:
=IMPORTXML ("https://www.w3schools.com/xml/note.xml", "/*")
IMPORTXML error in Google Sheets
If you get this error message, do not worry.
Google Sheets only asks your permission to access data from external URLs. Click Allow access and your XML data will be loaded to the spreadsheet.
Load XML data to Google Sheets vertically or horizontally
In our case, the XML data was imported horizontally into a single row. However, the data can also be loaded vertically into a single column like this:
You can change the horizontal to vertical XML import and vice versa with the help of two functions: TOROW and TOCOL. Here is what it looks like in our case:
Use cases of how you can connect XML to Google Sheets
How to import XML table to Google Sheets
You can import XML tables from web pages to Google Sheets with IMPORTXML. For this example, we will import a table from this Wikipedia page. Right-click on the XML element and select Inspect.
This will open the source code. You can then see how your data is tagged. If it’s displayed in a table, for instance, you’ll see it tagged as <tr></tr>.
- To grab the entire table, use the
"//tr"string. Your final IMPORTXML formula will look like this:
=IMPORTXML("https://en.wikipedia.org/wiki/Fish_farming","//tr")
How to import XML class to Google Sheets
In a similar way, you can import an XML class from a website to Google Sheets using the IMPORTXML function very easily. Let’s load the XML class, which contains all the H2 headers on the same web page. To do this:
- Go to the website and copy the URL of the website to your IMPORTXML formula.
- Right-click on the XML element and select Inspect.
- Check the class name for the data you want to import. For example, the class in the screenshot above is
mw-heading2and it’s on the<div>tag. So, the xpath_query is:
//div[contains(@class,'mw-heading2')]
- To get the contents for the class mw-heading2 on this Wikipedia page, the formula will be:
=IMPORTXML("https://en.wikipedia.org/wiki/Fish_farming","//div[contains(@class,'mw-heading2')]")
Formula example on how to parse XML response to Google Sheets
Let’s check out another example of how you can import XML to Google Sheets. We’ll use IMPORTXML to import a list of all sources supported by Coupler.io from this page.
The first two steps are the same:
- Go to the website and copy the URL of the website to your IMPORTXML formula.
- Right-click on the XML element and select Inspect. To get the xpath_query, check the class name for the data you want to import. In the screenshot below, the class name is
integrations-item-titleand it’s on the<div>tag so the xPath is://div[@class='integrations-item-title']
- To get all the contents for the class
integrations-item-titleon this webpage, the IMPORTXML formula will be:
=IMPORTXML("https://www.coupler.io/sources","//div[@class='integrations-item-title']")
How to import an XML file from Google Drive to Google Sheets
To import an XML file stored on Google Drive to a spreadsheet using IMPORTXML, you’ll need to:
- Share the Google Drive folder containing the file. Choose Anyone with the link and set the permission to Viewer.
- Get the shareable link to your XML file. Here is how it will look:
https://drive.google.com/file/d/1QagKPv3r_CWNWCS1uamAVa9h-N-cDb1t/view?usp=sharing
Read our blog post if you need guidance on how to share your Google file.
- Copy the file ID which is the selected bold area in the example below from the link URL.
https://drive.google.com/file/d/1QagKPv3r_CWNWCS1uamAVa9h-N-cDb1t/view?usp=sharingYou don’t need the entire Google address, just your file ID.
- Insert the file ID to the following URL boilerplate:
https://drive.google.com/uc?id=[file-id]&export=downloadAt the end of the day, your final link should look like this:
https://drive.google.com/uc?id=1QagKPv3r_CWNWCS1uamAVa9h-N-cDb1t&export=download
- We’ll place this URL in the A1 cell. Meanwhile, in A2, type
//*. This XPath gets all the values of your XML data. In A3, insert the formula:
=IMPORTXML(A1,A2)
Error when trying to import XML from Google Drive to GSheets
Things don’t always go smoothly when it comes to using this function with the XML stored on Google Drive. It has its limitations.
If you try to import an XML file from Google Drive, you’ll get #N/A! "Imported content is empty" or the HTML content of the Google Drive landing page at some point.
How to fix the IMPORTXML error to import XML from Google Drive
A workaround for this is to parse your XML file (which is stored on Google Drive) with Google Apps Script. If you have zero experience with it, check out our Google Apps Script tutorial.
Here are some scripts that help us dodge the #N/A error.
Apps Script #1:
Create the URL for your XML file as above and use the following script to obtain the file content from your shared file.
Function parseXml() {
var data = UrlFetchApp.fetch("https://drive.google.com/uc?id=1QagKPv3r_CWNWCS1uamAVa9h-N-cDb1t&export=download").getContentText();
var xmlDocument=XmlService.parse(data);
var root=xmlDocument.getRootElement();
Var notes=root.getChild("note",root.getNamespace()).getText();
Logger.log(note)
}
Note: Do not forget to insert your URL in UrlFetchApp.fetch().
Apps Script #2:
The file may not be shared in this scenario. However, the file must be on your Google Drive. Here is the script that will do the job.
function parseXml() {
var fileId = "1QagKPv3r_CWNWCS1uamAVa9h-N-cDb1t";
var data = DriveApp.getFileById(fileId).getBlob().getDataAsString();
var xmlDocument=XmlService.parse(data);
var root=xmlDocument.getRootElement();
Var notes=root.getChild("note",root.getNamespace()).getText();
Logger.log(note);
}
Note: Insert your file ID in var fileId =. In our case, this is 1QagKPv3r_CWNWCS1uamAVa9h-N-cDb1t.
This workaround was a solution from this Stackoverflow thread. To learn more about this function and how to use it, see this reference guide.
Bonus: Import data from cloud sources to Google Sheets
Now you know how to import XML to spreadsheets. At the same time, your essential data may be stored in data warehouses, cloud apps, JSON files, and other sources. If you want to have one solution to import it to Google Sheets, consider Coupler.io. It’s a reporting automation platform that lets you connect 60+ data sources to Google Sheets.
You can start for free without any bank details required. Just choose your source in the form below and click Proceed.
Then complete three simple steps:
- Connect your source and specify the data to export
- Preview your data and transform it on the go using filters, column manager, and formulas.
- Connect your Google account and specify the spreadsheet and a sheet where to import the data.
The best part is that you can automate data refresh without any coding. Toggle it on and configure the desired schedule.
Import data to Google Sheets with Coupler.io
Get started for free
