I use Excel's XML mapping to download RSS feeds and select elements to display in column. I'm currently using Google Spreadsheet's IMPORTXML functions and trying to recreate my below Excel results.

Excel XML mapping output with element headings and column order rearranged

The site being queried uses XML namespaces for its RSS feed and so [local-name() = "ELEMENTNAME"] is applied in the query. I'm using the following formula, where "URL" and "ELEMENTNAME" are defined ranges, for each of the eight columns:

=IMPORTXML(URL,CONCATENATE("//*[local-name() = '",*ELEMENTNAME*,"']"))

Without the CONCATENATE function the formulas are:

//*/[local-name() = 'filing-date']
//*/[local-name() = 'size']

The output is fine except (1) it requires multiple queries for a single URI that (I believe) may slow the workbook as more queries are added and (2) for columns that have null entries in certain rows, such as the last two on the right, the table displays them consecutively from the top-down, i.e. the column results are not in their correct rows.

I also tested using the below query with specific elements selected though the output displays in rows rather than columns and are not organized as listed in the query:

//*[local-name() = 'filing-date'] | //*[local-name() = 'filing-type'] | //*[local-name() = 'filing-href'] | //*[local-name() = 'form-name'] | //*[local-name() = 'size'] | //*[local-name() = 'amend']

Is there a way to consolidate these queries into one and, if the only solution is to keep the queries separate, have the null entries for each element appear as a blank cell?

  • 1
    @Rubén I've revised the question and simplified the objective. Thanks.
    – Catalyx
    Commented May 25, 2016 at 4:11

1 Answer 1


It's not possible to achieve the desired result by using IMPORTXML alone, I think. Instead use Google Apps Script and the XML Service Service.


Adaptation of the code taken from the answer by Vidar S. Ramdal

function parseFeedXml(url) {
   var result = [];
   var xml = UrlFetchApp.fetch(url).getContentText();
   var document = XmlService.parse(xml);
   var atom = XmlService.getNamespace('http://www.w3.org/2005/Atom');
   var feed = document.getRootElement();
   var entry = feed.getChildren('entry',atom);
   for (var i = 0; i < entry.length; i++) { 
     var content = entry[i].getChildren('content',atom);
     var contentElement = '';
     var resultRow = [];
     for (var j = 0; j < content.length; j++) { 
       contentElement = content[j];
       evaluateText(contentElement.getChild('filing-date',atom), resultRow);
       evaluateText(contentElement.getChild('filing-type',atom), resultRow);
       evaluateText(contentElement.getChild('filing-href',atom), resultRow);
       evaluateText(contentElement.getChild('form-name',atom), resultRow);
       evaluateText(contentElement.getChild('size',atom), resultRow);
       evaluateText(contentElement.getChild('amend',atom), resultRow);
   return result;

function evaluateText(element, resultRow) {
  if (element) {
  } else {
  • This and the reference are very helpful. I hadn't used Google Apps Script yet and am getting up to speed (hence delayed as I have very little coding experience). This works brilliantly. (Pardon if off topic: I'd greatly appreciate references for getting up to speed on XML, especially Xpath syntax, and Google Apps Script. The W3C link earlier helped though it required some familiarity with coding. Many thanks.)
    – Catalyx
    Commented May 28, 2016 at 20:06
  • @Catalyx : For Google Apps Script see google.com/script/start and for XML and XPath try w3schools.com Commented May 28, 2016 at 23:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.