Google-sheets – ImportXML error: ‘imported content is empty’ when selecting for text within a list item

google sheetsimportxmlxpath

I want to scrape a text within this website in google sheets with the IMPORTXML function. Through dev tools by inspecting the text in question, I get the corresponding HTML:

<li _ngcontent-udl-c4="">The outbound route is available for sale until 28/03/2020 and these are the last available dates.</li>

If I right-click > copy full XPath, I get /html/body/main/nas-fare-calendar/nas-info/div/div/ul/li[1]. If I input that in my sheet, as in (A1 has the URL linked to above):

=IMPORTXML(A1,"/html/body/main/nas-fare-calendar/nas-info/div/div/ul/li[1]")

I get an error 'Imported content is empty.'. What gives?

I've also tried Web Scrapper extension and the way that selects the text in question is with '.list–spaceless li:nth-of-type(1)'. Any way I could use that as inspiration for an XPath? Just putting that as input for IMPORTXML doesn't work.

What is the easiest way to select for the innerHTML of that HTML snippet above? Just trying with '//li' just won't include the li above. I don't know how to include _ngcontent-udl-c4 or li that has _ngcontent-udl-c4 as a style.

Best Answer

As mentioned in the comment above, it's not possible to retrieve the text via importxml. I managed, however to scrape it successfully. Here's how:

Open developer tools (Ctrl+Shift+I or File → Developer → Developer Tools)
Go the site in question.
Click on the network tab -> Filter icon -> select XHR to filter out some of the requests
Find the relevant request (see screenshot)
Right click on the request to copy its API URL
Now in google script query that URL

function getXMLAndExtractDate() { var url = "some.url/request/"; var res = UrlFetchApp.fetch(url); if(res){ string = res.getContentText(); var regex =/(?<=The outbound route is available for sale until )(.*)(?= and these)/; var out = string.match(regex)[0]; return out; //Logger.log(out); //28/03/2020 } } After that you can do with the date what you want (in my case I write it into a spreadsheet and track them daily. In case of any changes, it fires me an email.

Inspiration: https://beshaimakes.com/js-scrape-data

First, I should warn you, that down this path lies madness.

It may seem like something obvious to do, and it doesn't look that hard, but generating HTML using a spreadsheet (be it Google Sheets, Excel or any other spreadsheet package) may seem simple, but things can and will get over-complicated, or you will very soon reach a point in which you will need to write a macro to achieve your desired output.

Moreover, once you take the plunge and start writing your first couple of macros, if your sheet is not already overly complicated, you will hit the limits of the macro language pretty soon too.

But why? I'm not doing anything too intricate!

The reason you will quickly exhaust the features of a spreadsheet when using it to generate HTML is that generating structured markup is not well suited to the overall design of grid calculation paradigm. Which is the reason we have Word and Excel and that

"In the beginning there was Visicalc, and Wordstar, 
 and each did what they did 
 and they were not a single program" 

(From 'The book of Mozilla', I forget which verses).

Word processing is in itself, basically the process of generating a structured markup document. So there are software packages more suited to the task.

So what would be a wise thing to do?

My advice would be to keep the database in the spreadsheet, (a task for which it is well suited) and spare yourself deep frustration and unmet deadlines, and invest some time to learn a proper language; one that is easy to learn and efficient at templating HTML.

Python comes to mind. The alternative being taking advantage of the features of other Google products (since you're already doing Sheets) like Google Sites that makes it dead easy to create proper websites, and allows to embed and drive some of its pages from the data in a spreadsheet.

But I want to do it in Sheets! In fact, that's why I am asking here!

Agreed. However, I've been down that road, and would have appreciated that someone warned me that madness awaited, so I felt it would have been irresponsible and unfair to the Asker not to warn him of the perils ahead. (I'm looking at those who know, and didn't raise a voice.)

That being said, I've seen people abuse spreadsheets in manners far more troubling. So:

Here is my shot at good advice on the design of a structured-markup generating spreadsheet

You can see all this advice in action in this Google Spreadsheet, so you can examine the actual formulas at your leisure and see how these simple points will help you avoid creating an unmaintainable monstrosity and keep your sanity for a while more.

Create a master tag table that calculates the attributes for basic tags from the values of two or more independent cells
Take advantage of advanced features in the spreadsheet like named ranges to keep all your formulas simple by referencing the values on the master tag generating table
For the named ranges, choose names that look like the markup they reference so your formulas are readable and maintainable, like say
```
 =CONCATENATE(p_s1, A4, p_close) or
 =CONCATENATE(span_s2, B7, span_close)
```
Complexity will creep up quickly, keep your formulas simple and build the markup progressively from the inside out, taking advantage of using formulas in adjacent cells. for example, in column C surround a plain value with inner <p> tags, in column D, surround the value from C in outer <span> tags, in column E, surround whatever is in D with outer <div> tags and so on. one step at a time, you've got plenty of columns and then some
Avoid at all costs doing two steps or complex markup in a single cell. Always rely on your master tags table, because you know when you're done, someone will suggest that the class for all titles should be something different than what you coded. You'll be glad then that you didn't hardcode those attributes in a formula that wraps around twice on the f(x) bar, did you?

Please do visit the example sheet since that will be a lot clearer that a gazillion screenshots or the dense prose I tend to write when I get all worked up. Hope this really helps!

Google Sheets – How to Insert Error or Spacer When IMPORTXML Fails

to avoid array literal errors it should be:

=QUERY({
 IFERROR(IMPORTXML(A4,  "//div[@class='col-xs-12 visible-xs margin-top-1x']//span[@style='font-weight:bold;font-size:1.2em;'][@class='pull-right primaryPrice']"), "");
 IFERROR(IMPORTXML(A5,  "//div[@class='col-xs-12 visible-xs margin-top-1x']//span[@style='font-weight:bold;font-size:1.2em;'][@class='pull-right primaryPrice']"), "");
 IFERROR(IMPORTXML(A6,  "//div[@class='col-xs-12 visible-xs margin-top-1x']//span[@style='font-weight:bold;font-size:1.2em;'][@class='pull-right primaryPrice']"), "");
 IFERROR(IMPORTXML(A7,  "//div[@class='col-xs-12 visible-xs margin-top-1x']//span[@style='font-weight:bold;font-size:1.2em;'][@class='pull-right primaryPrice']"), "");
 IFERROR(IMPORTXML(A8,  "//div[@class='col-xs-12 visible-xs margin-top-1x']//span[@style='font-weight:bold;font-size:1.2em;'][@class='pull-right primaryPrice']"), "");
 IFERROR(IMPORTXML(A9,  "//div[@class='col-xs-12 visible-xs margin-top-1x']//span[@style='font-weight:bold;font-size:1.2em;'][@class='pull-right primaryPrice']"), "");
 IFERROR(IMPORTXML(A10, "//div[@class='col-xs-12 visible-xs margin-top-1x']//span[@style='font-weight:bold;font-size:1.2em;'][@class='pull-right primaryPrice']"), "");
 IFERROR(IMPORTXML(A11, "//div[@class='col-xs-12 visible-xs margin-top-1x']//span[@style='font-weight:bold;font-size:1.2em;'][@class='pull-right primaryPrice']"), "")}, 
 "where Col1 is not null", 0)