XML to Google Sheets - All Options Available | Coupler.io Blog (2024)

XML stands for Extensible Markup Language, a format for storing and sharing data that is utilized 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.

The next logical question, therefore, is how to access an XML document. While some text editors, such as Notepad, can open XML files, the output file may be unreadable. 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 I import XML data to Google Sheets?

Manual import: This method requires you to copy and paste the XML file’s contents into Google Sheets. This method is quite inefficient and should only be used in scenarios where you are working with a small set of data.

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.

Why use IMPORTXML to import XML to Google Sheets?

If you attempt to access an XML file in Google Sheets, you will get an error warning since it currently only supports Excel, CSV, and text files, among others.

However, Google Sheets has a built-in function called IMPORTXML that lets you import XML to Google Sheets automatically, thereby letting you interact with your data.

IMPORTXML function to connect 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, 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", "/*")

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 the XML table as shown below from this Wikipedia page.

To do this, you will need to:

• Go to the website and copy the URL of the website to your IMPORTXML formula.
• 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 take another Wikipedia page and import the XML class, which contains all the headers in this web page to Google Sheets. 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.

• This will open the source code. You can then see how the data is tagged.

• Check the class name for the data you want to import. For example, the class in the screenshot above ismw-headline and it’s on the <span> tag so the XPath is:

//span[@class='mw-headline']

• To get the contents for the class mw-headline on this Wikipedia page, the formula will be:

=IMPORTXML("https://en.wikipedia.org/wiki/Meat","//span[@class='mw-headline']")

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 extract names of employees and their descriptions from the Railsware website and parse the XML responses to a spreadsheet.

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. For this example, we will be importing the names of the employees first.
• To get the XPath, check the class name for the data you want to import. In the screenshot below, the class name isname and it’s on the <div> tag, so the xPath is:

//div[@class='name']

• To get all the contents for the class name on this website, the IMPORTXML formula will be:

=IMPORTXML("https://railsware.com/railswarians/","//div[@class='name']")

Now, let’s pull the descriptions of the employees whose names we’ve already imported.

• In the screenshot below, the class name is description, and it’s on the <div> tag so the XPath is:

//div[@class='description']

• The IMPORTXML formula to get all the employees’ descriptions is:

=IMPORTXML("https://railsware.com/railswarians/","//div[@class='description']")

There you have it! If these formula examples seem quite shallow to you, check out how you can get dividend data and options from Yahoo Finance using IMPORTXML.

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=sharing

Note: You 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=download

At 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 stage: XML to Excel or BigQuery via Google Sheets

Are you thinking about importing XML data into your Excel workbook or even Google BigQuery? There is a way you can do this without using Python or any other code-based solution and even automate your imports on a schedule. It consists of two steps:

• Importing XML data into Google Sheets using the IMPORTXML function.
• Connecting your Google Sheets to either Excel or BigQuery using Coupler.io.

Coupler.io is a solution for scheduling data exports from different apps (Airtable, Shopify, etc.) and sources to Google Sheets/Excel/BigQuery with no coding required. Let’s discover how it works with the example of Google Sheets as a data source and BigQuery as a data destination.

• Sign in to Coupler.io. Configure your source (where you want to export data from), which is Google Sheets, and connect to your source account.

• Select the spreadsheet and sheet(s) that contains the XML data that you’ve already imported using the IMPORTXML function.

Once you’re set up the source, proceed to the destination setup.

• Choose BigQuery.

• Click Connect to configure your destination account and insert the credentials file to connect to BigQuery. Learn how to get the credentials.
• Specify the names of the Data set and the Table to import data to.
• That’s it. Choose the schedule you want and get your XML data to BigQuery according to it.

XML to Google Sheets import is incredibly versatile. You’ve read about the several use cases, but if you’re particularly interested in a certain case study, leave a comment below and we’ll add it to our article or perhaps create a new one just for your issue. Best wishes with your data!

Zakhar Yung

A content manager at Coupler.io whose key responsibility is to ensure that the readers love our content on the blog. With 5 years of experience as a wordsmith in SaaS, I know how to make texts resonate with readers' queries✍🏼