3

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']
etc.

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 May 25 '16 at 4:11
2

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.

Example:


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);
       result.push(resultRow);                
     }
   }
   return result;
}

function evaluateText(element, resultRow) {
  if (element) {
    resultRow.push(element.getText());
  } else {
    resultRow.push('');
  }
}
  • 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 May 28 '16 at 20:06
  • @Catalyx : For Google Apps Script see google.com/script/start and for XML and XPath try w3schools.com – Rubén May 28 '16 at 23:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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