1

I am using IMPORTXML to create what is essentially a vehicle inventory feed file.

My issue arises when a price is not included in our main website. What seemingly is happening is when the price is not included the IMPORTXML is skipping that one, not including any error or anything, and including the next price that it finds in its place on the sheet.

This is an issue since we are trying to line up these trucks with their vin, msrp, price, etc.

https://docs.google.com/spreadsheets/d/1KceS3krlbpU2FjQly-tK8N5qiwshIq3rQFQ36WRNV5U/edit?usp=sharing

To give a specific example in my sheet this vin number: 1FTMF1E5XKKE62311 currently doesn't have a price listed on our site. Instead of inserting a blank cell or error, the IMPORTXML is inserting the price for the unit listed after this. This is creating a cluster of blank prices at the end of the sheet even though the prices for those vehicles are on our website. Basically this throws off the entire sheet after the first skipped price.

Here is how we are pulling in internet price currently:

=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']");
 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']");
 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']");
 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']");
 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']");
 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']");
 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']");
 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']")},"")

We were hoping that the error handler would work but unfortunately, it didn't. Does anyone have any thoughts on how we would not break the import order when there is a price that isn't listed?

Any advice would be much appreciated!!

update changed above sheet to be editable by anyone if you'd like to make a column and give it a shot

Improve this question

2 Answers 2

Reset to default
0
  • 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)

Improve this answer
14
  • This might be a better formula than what I was initially using but still doesn't seem to skip or insert a placeholder when that path is invalid ie price doesn't exist (or at least I can't seem to get it to). So essentially all the unknown prices are still bunching up at the bottom. If it should and I am using it wrong, please let me know :) Thanks for your help User0!
    – Brian L
    Aug 29, 2019 at 15:53
  • depends... if you know the exact amount of imported stuff you can change ending of each row from formula from , ""); to: , {"";"";""}); (eg if you know that it should scrape 3 cells under each other). this way it will be pre-fixed. also you will need to drop the query
    – user0
    Aug 29, 2019 at 17:11
  • I am always trying to import the exact same amount of data per row. Like in that example file, there are 4 items I would like to import (vehicle title, MSRP, VIN, and Price). Your example and my price column only attempt to import the price so not exactly sure what you mean when you say if I know that it should scrape 3 cells under each other. Also not clear what you mean when you say that I will need to drop the query. Thanks User0!
    – Brian L
    Aug 29, 2019 at 18:08
  • try: i.stack.imgur.com/JhEb8.png
    – user0
    Aug 29, 2019 at 18:16
  • 1
    Yup! you got it exactly! So when the data is acquired, if there is a missing variable (in this case price like in your image) all of the data isn't thrown off by one cell. That is a really smart solution simply skipping a row when you detect another variable like Call For Price. I am trying to understand this formula a bit and am making progress, but still haven't been able to incorporate the 2nd url (in A5) so it gets data from both urls in A4 and A5. You have been super helpful User0.
    – Brian L
    Aug 30, 2019 at 19:41
0

Edit : found a way to spot missing datas

Original answer :
There are no errors because there's no data : IMPORTXML retrieve every data corresponding to a specific Xpath query and will not display anything if there's nothing.
Conclusion : IFERROR is useless here. However what you can do is concatenate your IMPORTXML queries using | like this : 

=IMPORTXML(A4,"//h2[@class='vehicleTitle margin-x']/a/span[@class='notranslate']")
=IMPORTXML(A4,"//div[@class='col-xs-12 visible-xs margin-top-1x']/ul/li/span[@style='text-decoration:line-through;'][@class='pull-right']")
etc....

Becomes 

     =IMPORTXML(A4,"//h2[@class='vehicleTitle margin-x']/a/span[@class='notranslate'] |
 //div[@class='col-xs-12 visible-xs margin-top-1x']/ul/li/span[@style='text-decoration:line-through;'][@class='pull-right'] |
 //div[@class='col-xs-12 visible-xs margin-top-1x']//li[@class='vinDisplay']/span | 
 //div[@class='col-xs-12 visible-xs margin-top-1x']//span[@style='font-weight:bold;font-size:1.2em;'][@class='pull-right primaryPrice']
")

Result is an array like this one : 

2019 Ford F-150 XL
$30,045
$20,427
1FTMF1CB6KKC12430
2019 Ford F-150 XL
$30,045
$20,427
1FTMF1CB8KKC12381
2019 Ford F-150 XL
$31,450
$21,846
1FTMF1CBXKKD26088
...
  and then where there's no data for the price : 
...
2019 Ford F-150 XLT
$54,200
$39,758
1FTEW1E53KKC32677
2019 Ford F-150 XL    here's the vehicle 
1FTMF1E5XKKE62311     VIM but no price
2019 Ford F-150 XLT
$53,620
$39,853
1FTEW1E43KKD96874

Just need to find a solution to rearrange the data.

Edit : Solution to rearrange the data.

Next to the result column B2:B, I put this formula in C2 then expand it =countif(arrayformula(regexmatch(INDIRECT("B2:B"&ROW(B2)),"Ford")),true) It increments a counter for each new vehicule name with the keyword Ford.

Counter for each new vehicule

Then I used the same idea to create an array for each entry : 

=transpose(FILTER($B$2:$B$401,$C$2:$C$401=row(A1)))

The final result when there's missing data :

Array with the missing data

Improve this answer
6
  • Thanks so much for this suggestion Paul! It's definitely a different strategy I didn't consider. Experimenting now to see if I can figure out how to insert some type of placeholder text in the cell when I rearrange in place of where the price would normally import if present. If I find a solution I will post.
    – Brian L
    Aug 28, 2019 at 18:46
  • I think it's definitely a better strategy for one reason : you make less calls with importxml (max is 50/day AFAIK). I'll try to find a solution but right now I have no idea to how ths problem should be approached : maybe some REGEX-PLACE-EXTRACT-?? formula ? or with apps-script ?
    – pjmg
    Aug 28, 2019 at 19:03
  • Hmm yea almost some kind of logic like after a cell containing "Ford" there has to be two cells with a dollar sign.
    – Brian L
    Aug 29, 2019 at 16:43
  • @BrianL I think I found a way if it's still relevant for you.
    – pjmg
    Oct 15, 2019 at 23:35
  • @paul sure I would love to see what you came up with! I found a hackey workaround that works in some instances but it is not at all an elegant solution.
    – Brian L
    Oct 17, 2019 at 20:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.