6

I want to get price data on this website (https://tarkov-market.com/item/Pack_of_sugar)

But it doesn't work

=IMPORTXML("https://tarkov-market.com/item/Pack_of_sugar","//*[@id='__layout']/div/div[1]/div/div[4]/div[1]/div[2]/div[1]/div[2]")
Tanaike
  • 181,128
  • 11
  • 97
  • 165
armusk
  • 73
  • 1
  • 1
  • 5

1 Answers1

5
  • You want to retrieve the price like 55,500₽ from the URL of https://tarkov-market.com/item/Pack_of_sugar and put to a cell on Google Spreadsheet.

I could understand like this. If my understanding is correct, how about this answer?

Issue and workaround:

Unfortunately, IMPORTXML cannot be used for this situation. Because IMPORTXML is used like =IMPORTXML("https://tarkov-market.com/item/Pack_of_sugar","//*"), an error like the value cannot be retrieved from the URL occurs. So in this case, as a workaround, I would like to propose to use Google Apps Script as a custom function. When Google Apps Script is used, the value can be retrieved.

Sample script:

Please copy and paste the following script to the container-bound script of the Spreadsheet. And please put =sampleFormula() to a cell. By this, the value can be put to the cell.

function sampleFormula() {
  const url = "https://tarkov-market.com/item/Pack_of_sugar";
  const html = UrlFetchApp.fetch(url).getContentText();
  return html.match(/price:(.+?)<\/title>/)[1].trim();
}
Result:

enter image description here

Note:

  • This script is for your question. So when this script is used for other URL and scenes, an error might occur. Please be careful this.

References:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • According to this [question](https://stackoverflow.com/q/74216081/1595451), the custom function from this answer is not working anymore. It gets Error 403. – Rubén Oct 31 '22 at 00:14
  • 2
    @Rubén Thank you for your comment. About `According to this question, the custom function from this answer is not working anymore. It gets Error 403.`, I think that in the case of `403`, it is considered that the server cannot be accessed from the Google side. So, in this case, it is considered that although it could access the server when this answer has been posted, the specification of the server has been changed. I deeply apologize that I cannot continue to check the condition of the server. – Tanaike Oct 31 '22 at 00:19
  • 1
    @Rubén As additional information, for example, it seems that the request by the custom function is different from the request by the script editor. [Ref](https://stackoverflow.com/a/63024816) So, I tested both situations. And, unfortunately, in the current stage, the value of "user-agent" cannot be changed with UrlFetchApp. From these results, I thought that in the current stage, the server cannot be accessed using UrlFetchApp from the Google side due to the change in the specification of the server side. It's considered that it's required to use another language instead of Google Apps Script. – Tanaike Oct 31 '22 at 00:55
  • @Rubén Thank you, too. In the case of questions that the values are retrieved from the URL, when the specification of the server side is changed, the sample scripts and the sample formulas posted in the answer might not be able to be used. There is the case that the updated server can be used by modifying the script and formula. But, unfortunately, there is the case that the updated server cannot be accessed from the Google side. Unfortunately, I cannot keep checking all questions and answers. Such a situation is making me feel sorry. – Tanaike Oct 31 '22 at 02:15
  • 1
    I don't think that you should review every old answer, actually it will be nice to have a canonical question to be used as target to mark as duplicate all the questions about the common errors for questions tagged [tag:google-sheets]+[tag:google-apps-script]+[tag:web-scraping]+[tag:urlfetch] (including those that should have these tags), i.e. instead of providing very specific workarounds for each OP like using an an specific API.. provide general guidance and let the OP to find the API by themselves, ... – Rubén Oct 31 '22 at 02:26
  • 1
    or instead of givin specific instructions about how to get the data from a JSON included in the webpage, provide general guidanc and let the OP to find the JSON and make the specific code by themselves. – Rubén Oct 31 '22 at 02:29
  • 1
    @Rubén Thank you for replying. I understand your reply. I think that introducing the methods for retrieving the expected values is useful for OP and other users, even when the server is changed. So, I thought that your previous answer will be useful for a lot of users. https://stackoverflow.com/a/74245749 – Tanaike Oct 31 '22 at 02:32