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?

Improve this question
5
  • 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
    Commented Jan 19, 2015 at 10:47
  • The link return error.
    – Rubén - Volunteer Moderator - ♦
    Commented May 23, 2016 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
    Commented May 23, 2016 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 - Volunteer Moderator - ♦
    Commented May 25, 2016 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
    Commented May 25, 2016 at 18:31

2 Answers 2

Reset to default
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.

Improve this answer
6
  • 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
    Commented Jan 19, 2015 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
    Commented Jan 19, 2015 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
    Commented Jan 19, 2015 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
    Commented Jan 19, 2015 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 - Volunteer Moderator - ♦
    Commented May 25, 2016 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

Improve this answer

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.