1

Google Spreadsheets has an IMPORTXML function, which I am trying to use to maintain a list of TV episodes, based on the API at http://services.tvrage.com/feeds/episode_list.php?sid=3183.

I am trying to get a table of Date | Season No | Episode No | Title from that XML.

The XML is hierarchical, nesting episodes under seasons.

I can get my date, episode and title columns, using xpath:

  • Date - /Show/Episodelist/Season/episode/airdate
  • Episode No - /Show/Episodelist/Season/episode/seasonnum
  • Title - /Show/Episodelist/Season/episode/title

However, I cannot get seem to find the right xpath to extract the Season number for each episode entry, which is the no attribute to the Season parent node.

I have tried:

  • /Show/Episodelist/Season/episode/../@no
  • /Show/Episodelist/Season/episode/parent::Season/@no

Both of these result in getting a distinct list of seasons, i.e.:

Date         Season  Episode  Title
01-01-2001   1       1        foo
02-01-2001   2       2        bar
03-01-2001   3       3        baz
04-01-2001           4        fee
05-01-2001           5        fob
01-03-2002           1        bix
02-03-2002           2        buz
03-03-2002           3        fez
04-03-2002           4        baj

...whereas I am looking for:

Date         Season  Episode  Title
01-01-2001   1       1        foo
02-01-2001   1       2        bar
03-01-2001   1       3        baz
04-01-2001   1       4        fee
05-01-2001   1       5        fob
01-03-2002   2       1        bix
02-03-2002   2       2        buz
03-03-2002   2       3        fez
04-03-2002   2       4        baj

What is the correct xpath so that the IMPORTXML routine does not do a distinct operation on the imported data?

  • This is tricky. Your problem seems to be that there is only 1 node that contains the season number, while the other values exists 1 per row. But if you could share an example spreadsheet with us, it would be easier to understand your problem. – Vidar S. Ramdal Jan 19 '15 at 10:47
  • The link return error. – Rubén May 23 '16 at 11:58
  • I think this particular API disappeared around September 2015, and didn't leave a forwarding address. If I find an equivalent XML API, I will update – jimbobmcgee May 23 '16 at 14:17
  • From the answer by @Aurielle, the following URL could work instead of the deprecated API: tvrage.com/the-layover/episode_list/all – Rubén May 25 '16 at 18:18
  • Yes, but that is scraping the webpage, not reading from a feed. Besides, the feed was an example to demonstrate the problem, not the problem itself - the problem was that IMPORTXML only returned unique values, even if the xpath was explicitly crafted to work on non-unique elements – jimbobmcgee May 25 '16 at 18:31
1

As stated in my comment, doing this with =importXML and XPath seems tricky.

But doing it programmatically, using the XmlService of Google Apps Script, looks easier.

I have written the following:

function parseTvRageXml(url) {
   var result = [];
   var xml = UrlFetchApp.fetch(url).getContentText();
   var document = XmlService.parse(xml);
   var root = document.getRootElement();

   var show = document.getRootElement();
   var episodeList = show.getChild("Episodelist");
   var seasons = episodeList.getChildren("Season");
   for (var i = 0; i < seasons.length; i++) {
     var season = seasons[i];
     var seasonNum = season.getAttribute("no").getValue();
     var episodes = season.getChildren("episode");
     for (var j = 0; j < episodes.length; j++) {
       var episode = episodes[j];
       var resultRow = [];
       resultRow.push(seasonNum);
       resultRow.push(episode.getChild("epnum").getText());
       resultRow.push(episode.getChild("seasonnum").getText());
       resultRow.push(episode.getChild("airdate").getText());
       resultRow.push(episode.getChild("link").getText());
       resultRow.push(episode.getChild("title").getText());
       result.push(resultRow);                
     }
   }
  return result;
 }

In effect, it fetches the content from the given URL, parses it as XML, and iterates through the elements, extracting values of the elements you wanted, which is stored in a 2-dimensional array (result), and returned.

To use this, you must install the script in your spreadsheet. Click the Tools menu → Script editor, and paste the code listed above. Save the script.

In the cell where you want the output, enter

=parseTvRageXml("http://services.tvrage.com/feeds/episode_list.php?sid=3183")

The result should be a list of episodes, with the following columns:

Season number | epnum | seasonnum | airdate | link | title

I have set up an example spreadsheet to demonstrate this, feel free to copy it.

  • This is what I have ended up doing, but it is a bit of a pain, because it requires additional permissions (and some too-broad Noscript exclusions). In any case, it appears to be an issue with XPath, rather than Google Sheets' implementation. – jimbobmcgee Jan 19 '15 at 12:40
  • Not sure I understand what "additional permissions" and "Noscript exclusions" you need. You're installing this in Google Spreadsheets script editor, right? Shouldn't need any specific permissions to run this script - and "Noscript exclusions" shouldn't be part of it, because the script is running server-side. – Vidar S. Ramdal Jan 19 '15 at 13:17
  • Re permissions: Google pops up the 'this app needs access to external data' warning; Re Noscript: all App Scripts are served from googleusercontent.com, so require an additional exclusion. But these are my constraints, not yours, so don't worry about it :-). – jimbobmcgee Jan 19 '15 at 13:23
  • Oh, OK, I didn't get the "external data" warning. As for Noscript, I still don't think it applies. The script is not served from googleusercontent.com, it is run from Google's servers. As such, the web browser is not part of the equation. – Vidar S. Ramdal Jan 19 '15 at 13:26
  • 1
    I just take the code from this answer as "inspiration" for this other answer to Using Google Spreasheet's IMPORTXML Function to Download Selected Multiple Columns – Rubén May 25 '16 at 20:52
1

You now can use importxml and/or importfeed.

This will automatically stack it for you:

=IMPORTXML("http://www.tvrage.com/the-layover/episode_list/all","//h1 |//*[@size='4']| //*[@class='b'][1]/tr | //*[@class='b'][2]/tr")

enter image description here

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.