Coupler.io Blog

Extract Data from XML to Google Sheets: How to Use the IMPORTXML function

  • Zakhar Yung
    • Right-click on the XML element and select Inspect. 

    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.

    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")

    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:

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

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

    //div[contains(@class,'mw-heading2')]
    =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:

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

    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.

    https://drive.google.com/file/d/1QagKPv3r_CWNWCS1uamAVa9h-N-cDb1t/view?usp=sharing

    You don’t need the entire Google address, just your file ID.

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

    1. Connect your source and specify the data to export
    2. Preview your data and transform it on the go using filters, column manager, and formulas.
    3. 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
    Exit mobile version