Import XML from URL into Google Sheets using Custom Function

Let’s as assume, I have data (a list of books, categories and prices) in XML format in an URL. See this URL.

The XML is a library, which has a list of books. I want to extract (or import) the name of the books along with the price and show it in my Google sheets. I do not want to copy data one by one. Instead, I’ll just copy the URL and paste it in a cell and it should automatically import the relevant data and show it in the sheet.

All I need is an app script function, that will be invoked (or called) when I enter or paste the URL and press the enter key.

Follow these steps.

You must have access to your Google account to create and use Sheets. So, if you have a Google account, sign-in first.

1) Open Sheets.

2) Create a new spreadsheet and name it. The sheet is empty. It will be filled will XML data.

3) From the top menu, click Extensions and choose Apps Script.

4) By default, it creates a file named Code.gs, where we’ll write the apps script function.

Write the Script

Here’s our function.

function readXML(url) {
  let results = [];
  if (!url) return;

  // Fetch XML data.
  const xmlData = UrlFetchApp.fetch(url, {
    muteHttpExtensions: true,
    method: 'GET',
    followRedirects: true
  });

  // Create a document.
  const document = xmlData.getContentText().split('<List>');
  
  // Extract data and store it in an array.
  for (let i = 1; i <= document.length - 1; i++) {
    let books = document[i]
      .split('<BookName>')[1]
      .split('</BookName>')[0];
    let price = document[i]
      .split('<Price>')[1]
      .split('</Price>')[0];
        
    results.push([books + ' ' + price]);
        // OR, use a comma to show data in different columns. 
            // like this... results.push([books, price]);
  };

  results = results.filter(function (el) {
    return el != null;
  });

  return results;   // Return the final result.
}

I have explained the function below. First, let’s execute the function to see the result. Therefore, go back to your spreadsheet.

Click on a cell (any cell), type this in the cell, and press Enter key.

It will show loading… for a moment, while it is extracting the data from the URL. If everything is executed property, it will display the data.

Note: I am not doing any formatting. The objective here is simple. Extract XML data from URL and show the data in Google spreadsheet.

What does this function do?

The function readXML() takes an argument in the form of url or the address of the xml page.

First, we’ll fetch xml data using GET method. If the call is a success, the const xmlData will have the xml data.

Next, Next, I have created a document by splitting the "<List>" tag. Doing this, the function knows how many List tag it has to browse to get the relevant data.

Inside the "for loop", I have two variables to store name of the book and its price. Now see this code here.

let books = document[i]
    .split('<BookName>')[1]
    .split('</BookName>')[0];

It starts from <BookName> and ends at </BookName> (closing tag) and extracts the value inside the tag and assigns it to variable.

The data is then pushed inside an array.

results.push([books + ' ' + price]);

Before returning the results, it checks if the array is not empty.

results = results.filter(function (el) {
    return el != null;
});

This is also an example on how to create a custom function in Google sheets. A custom function is a user defined function (UDF). If you have worked with JavaScript, then you must have heard about this term.

Its just a sample. You can perform many interesting and useful tasks using a custom function in Google sheets.