XML in Google Sheets

What is XML in Google Sheets?

XML is a file format that can be read easily by both humans and machines that is used to store data. XML is very similar to HTML. HTML is used to display data, while XML is used to store data. The full form of XML is Extensible Markup Language. It is used to store and transport data and can be imported into Google Sheets. The best way to import an XML file is by using the IMPORTXML function. By importing XML into Google Sheets, you can use spreadsheets for visualization and analysis.

In this case, to import XML to Google Sheets, open your Google Sheets, select a cell, and paste the snippet below. We have provided a link to an online XML file.

=IMPORTXML (“https://www.w3schools.com/xml/note.xml”, “//note”)

You can see how the data is displayed in Google Sheets. You can see how the data is displayed in Google Sheets.

How to import XML to Google Sheets?

Google Sheets has a feature called “Import,” where you can directly import Excel, CSV, or other text files. Trying to import an XML file via File > Import will result in an error. However, this can be worked around quickly, and there are other ways to import XML from Google Sheets.

There are two ways to import XML in Google Sheets.

• Using the IMPORTXML function
• Using Google Apps Script

IMPORTXML function

You can use the IMPORTXML function in Google Sheets to import an XML file online in a website into your sheet. First, let us look at the syntax of the IMPORTXML function.

=IMPORTXML(“url”, “xpath_query”)

• url: The URL of the XML file from which data is taken
• xpath_query: XPath query is used to work with structured data. It instructs the function of the data we are importing.

Let us now look at the steps.

Step 1: First, find the data you want to import and inspect its HTML page. Here, we are looking at the Wikipedia page listing the US postal code prefixes.

Step 2: Find the data you want to import. Identify the tags that contain the data.

Step 3: In a Google Sheet, go to cell A1 and enter the formula

=IMPORTXML(“https://en.wikipedia.org/wiki/List_of_ZIP_Code_prefixes”, “//td”)

Step 4: Press Enter. Your data appears in the tabular form in Google Sheets, as shown below.

Google Apps Script

The IMPORTXML function fails when you need to import an XML into your Google Drive, and Google Sheets can’t directly access files on your PC.

In such cases, we use the Google Apps script. It helps automate the process. However, you will have to upload the XML file to Google Drive first.

Let us look at the required steps below.

Step 1: We have an XML file called book.xml containing a list of authors and books. We have uploaded it to our local Google Drive.

Step 2: Now, click on the file in your drive and observe the URL. The name specified in the red box is the FILE ID.

Step 3: Open your Google Sheets. Go to Extensions > Apps Script.

Step 4: Delete the default code and replace it with the following script:

function importXMLData() {

  var fileId = ‘1fdq9E9lApjVUbtuNiLAsSPay_LB_itf2’;  // Replace with your XML file’s ID from Google Drive

  var file = DriveApp.getFileById(fileId);

  var xmlContent = file.getBlob().getDataAsString();

    // Parse the XML content

  var xml = XmlService.parse(xmlContent);

  var root = xml.getRootElement();

    // Example: Extract and print all the ‘item’ elements from the XML

  var items = root.getChildren(‘book’);  // Add book as it is the XML tag you need

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  sheet.clear();  // Clear the current sheet to avoid data overlap

  // Write the data to the sheet

  var row = 1;

  items.forEach(function(item) {

    var data = item.getChildren();  // Get all child elements of ‘book’

    var col = 1;

    data.forEach(function(element) {

      sheet.getRange(row, col).setValue(element.getText());

      col++;

    });

    row++;

  });

}

Step 4: Press Run, and your code is executed.

Now, check your Google Sheet. The data is imported into it.

Examples

Google Sheets does not allow you to import XML files directly through the Import feature. However, there are other ways to import them. Let us look at some interesting examples, as shown below.

You cannot directly add an XML file from your PC to Google Sheets. Hence, first, add the File to your Drive.

Example #1 – Import XML File Saved on your System

Step 1: To make the XML file accessible online, go to your Google Drive, click the + New button and choose File Upload.

Step 2: Select the XML file to be uploaded to your Google Drive. Here, we choose florist_menu. The file is uploaded to your drive. Do not forget to make the file publicly accessible.

Step 3: Get the shareable link to your XML file. For instance, it should be like:

Step 4: Copy the file ID. It is the part that is highlighted by the box.

Step 5: Insert the file ID in the link provided below.

Step 6: Now, use it in the IMPORTXML function as follows.

=IMPORTXML(“https://drive.google.com/uc?id=1i2IGGDoAXfD5aahDW4sHMMlqa4auw_P2&export=download”,” //florist_menu”).

Press Enter and look up your Google Sheet.

Example #2 – Import XML File using the Web URL

Step 1: Let’s take an example of an XML file found in a website. Here, we have the Wikipedia page for postal codes in Canada.

https://en.wikipedia.org/wiki/List_of_T_postal_codes_of_Canada

Step 2: Apply the following function to the IMPORTXML function and enter it in cell A1 of your Google sheets. Identify the tags that contain the data. Here it is /td.

=IMPORTXML(“https://en.wikipedia.org/wiki/List_of_T_postal_codes_of_Canada”, “//td”)

Step 3: Press Enter. You get the list imported to Google Sheets.

Example #3

Let us look at another simple example where we import an XML file in Google Sheets.

Step 1: Look at this publicly available XML file in Google Sheets.

https://www.cs.utexas.edu/~mitra/csFall2010/cs329/lectures/xml/planes.xml.txt

Step 2: To import it into Google Sheets, use the following IMPORTXML function in cell A1.

=IMPORTXML(“https://www.cs.utexas.edu/~mitra/csFall2010/cs329/lectures/xml/planes.xml.txt”, “//planes_for_sale”)

Step 3: Press Enter. You can observe the data imported into the sheet.

Importance in data transfer

1. The ability to import XML into Google Sheets is important as this enables data transfer. The importance of data transfer includes:
2. Data transfer allows integration with other data sources. Data transfer through XML allows seamless communication between various external systems, such as APIs and Google Sheets.
3. Data transfer allows efficient handling and structuring of complex, hierarchical data through XML.
4. Another advantage of data transfer using XML is the ability to automate such transfers and update data in real time using IMPORTXML or Google Apps Script.
5. XML data is platform-independent, so data transfer across different systems and organizations for transformation, processing, and validation is easy.

Important Things to Note

1. The IMPORTXML function works with public URLs alone and won’t work with files in your system.
2. Hence, before accessing the file, you must upload it to a cloud storage service such as Google Drive to ensure its publicly accessible.
3. Only correctly formatted XML files can be imported into Google Sheets; otherwise, you may get an error.

The IMPORTXML function uses XPath as its second argument. It extracts specific elements from the XML.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand XML In Google Sheets, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to What Is XML In Google Sheets. Here we explain how to import XML files in various ways to store data in Google Sheets with examples. You can learn more from the following articles –

Comparison Operators in Google Sheets

Custom Number Format in Google Sheets

PI in Google Sheets