Import current price from website to spreadsheet

Question

As you can probably gather from the title, the goal is to plug the current price of this product into a spreadsheet.
Perhaps I'm over-complicating things, but there must be an easy way to do this.

The route I've taken is to utilize the IMPORT XML feature on GoogleSheets. I feel like I'm probably encountering a syntax error, I just can't seem to get it right.
My cell currently looks like:

=IMPORTXML("http://www.walmart.com/ip/Morton-Sea-Salt-Fine-Salt-17.6-oz/10849552", "id('WM_PRICE')/x:div/x:div/x:span/x:span[2]")

I used an extension for Firefox called Xpath checker to pull the Xpath of the pricing element from the website, but the application doesn't seem to approve. Unfortunately I couldn't find much documentation on using GoogleSheets in this manner (as a 'simple' scraping tool).

UPDATE

Tried IMPORTHTML; it appears that the site has the price nested between lines 18 and 19. So using the following does not work:

=IMPORTHTML("http://www.walmart.com/ip/Morton-Sea-Salt-Fine-Salt-17.6-oz/10849552", "list", 18)

I know the unique selector for the node containing price is

#WM_PRICE > div:nth-child(1)

Is there not some way to just use this to syndicate the information on Google Sheets?

RESOLUTION

Okay, I've found a semi-solution by utilizing the IMPORTHTML function as a table and then parsing the data and chopping it down to what I need. There's probably a better way.
My solution:

=MID(Query(IMPORTHTML("http://www.walmart.com/ip/Morton-Sea-Salt-Fine-Salt-17.6-oz/10849552", "table", 2), "SELECT Col1 LIMIT 1"),8,6)

By changing the HTTP bit I can now effectively get the price of anything under $99.99 at Walmart.com (EDIT: After some testing, it works on FOOD only, the page layout for other items is different), anything over that and I'll lose a decimal place. Fortunately, there aren't many items I need that will fall into that category.

Explanation of the formula:

First was to import the precious data, I achieved the raw import with the following:

IMPORTHTML("http://www.walmart.com/ip/Morton-Sea-Salt-Fine-Salt-17.6-oz/10849552", "table", 2)

However, it imported several columns and rows, no bueno. So I chopped them off by "selecting" column 1, and "limiting" the rows to 1.

Query((), "SELECT Col1, LIMIT 1")

But that left me with a single, mucked up cell: The formula returned the word Online before the $ value (and a bunch of junk after it), so I parsed 8 characters into the string and stopped parsing after 6 characters via the MID command (fortunately there was a single space buffer after the dollar amount).

=MID((),8,6)

If anyone else has a more elegant way to do this, please still comment. I just wanted to leave some stepping-stones for anyone looking to do something like this in the future.

Answer 1

The error in your importxml was in the xpath syntax. here is the correct one:

=CONCATENATE(IMPORTXML("http://www.walmart.com/ip/Morton-Sea-Salt-Fine-Salt-17.6-oz/10849552","//*[@itemprop='price'][1]"))

edited: added the [1] at the end of the xpath to prevent the duplicate price.

Answer 2

Ultimately, I can't do it with Google's functions. As a result, I decided to write a custom function.

Go to Tools -> Script Editor. Click "Spreadsheet" on the left hand side to create a new document. Scroll down and paste the following code in:

function getWalmartPrice(url) {
  var html = UrlFetchApp.fetch(url).getContentText(); //Can't create document element ^_^
  html = html.substring(html.indexOf("bigPriceText1"));
  html = html.substring(html.indexOf(">") + 1);
  var txt = html.substring(0, html.indexOf("<"));
  //same thing for the small price
  html = html.substring(html.indexOf("smallPriceText1"));
  html = html.substring(html.indexOf(">") + 1);
  return txt + html.substring(0, html.indexOf("<"));
};

It was a pain to write (no XHR, no document, etc.) but it's just a bunch of JavaScript string manipulations to find the DOM elements' contents.

Save the file - click File->Save, control/command-S, or the button presented above. You will probably be prompted with a bunch of permission-related messages, and you'll also have to give the project a name (doesn't matter what you call it, as long as the file is called Code.gs which it automatically is). Now you can go back to the spreadsheet and test it out. Remember, the function is =getWalmartPrice(<url>). A tool-tip does not pop-up on custom functions as you write the function's name, hence don't be alarmed when it doesn't show up.

Here's a sample:

=getWalmartPrice("http://www.walmart.com/ip/Morton-Sea-Salt-Fine-Salt-17.6-oz/10849552")
=getWalmartPrice("http://www.walmart.com/ip/Dell-Pre-Owned-Refurbished-Silver-745-Desktop-PC-with-Intel-Core-2-Duo-Processor-4GB-Memory-750-Hard-Drive-and-Windows-7-Professional-Monitor-Not/21907344")

I included the second example to show that the code is universal. It works for products from different departments (i.e. electronics) and for things that cost over $99.99. The code correctly returns $209.18.

In case you want a number and not a string, you can pretty easily change the code to extract only the numbers and no string. You can do txt.match(/\d/g).join(""); to get only numbers out of a string, and of course parse it to do some manipulations.

Answer 3

The following setup returns the price:

A1: http://www.walmart.com/ip/Morton-Sea-Salt-Fine-Salt-17.6-oz/10849552
A2: (//div[@itemprop='price'])[1]
A3 Formula: =CONCATENATE(IMPORTXML(A1,A2))
A3 Result: $2.35