3

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 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)

And that's it folks. 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.

  • Tried everything but can't get it to work. It is known to be buggy. – Shahar Aug 4 '14 at 1:21
  • Yeah, it's a pain. I can't come up with a universal solution either. I just had to "rig" it. But it won't work with anything over $99.99 in value, because it will start cutting off the 6th value (penny decimal place). – Bill-Skates Aug 4 '14 at 1:46
  • Evidently the solution I came up with only works for food items. Other departments have a different layout, and therefore won't parse correctly. – Bill-Skates Aug 4 '14 at 2:13
  • That really sucks. You might as well just use a programming language (an actual one) to extract the data you need and then paste it in Google spreadsheet. Is that a possibility or no? – Shahar Aug 4 '14 at 2:38
  • 1
    Edit out your resolution into a new answer below so that it is not mixed in with the question itself – Eight Days of Malaise Jan 4 '16 at 16:39
2

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.

  • 1
    It seems to be duplicating the numbers. Can you fix / update this? PS: Also, would like to be able to do the same with Amazon & NewEgg for some shopping calculations and comparisons. – Alex S Mar 1 '16 at 11:20
  • @AlexS I fixed it, all it needed was an [1] at the end of the xpath to show the price once - also yeah i can do it for the other sites also but I need sample urls – Aurielle Perlmann May 31 '16 at 1:43
1

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

enter image description here

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:

enter image description here

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.

enter image description here

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:

enter image description here

=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.

  • Not working anymore. Not sure why. Can you fix / update this? PS: Also, would like to be able to do the same with Amazon & NewEgg for some shopping calculations and comparisons. – Alex S Mar 1 '16 at 11:20
0

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

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.