Import site specific data into Google Sheets

0
votes

For some time now I've been trying ways to successfully import specific data from the Site https://www.infogol.net/matches/result/english-premier-league/everton-vs-wolves-2019-09-01/30701

In the Stats tab, in Maps, there is the data called Infogol xG, that's exactly what I want to be able to play for my spreadsheet.

I tried with various formats of ImporXML and ImportDATA but never succeeded.

I would like your help in trying to find a form via script or even formulas to be able to capture this data, is of paramount importance to the study I am doing on the qualitative system of kicks in football games.

Image

Image Link specifying the data I need

1
google-apps-scriptgoogle-sheets

1 Answers

0
votes

How about this answer? In this answer, IMPORTXML is used. Unfortunately, the values of 1.54, 2.14 cannot be directly retrieved from the HTML data of the URL. So the values are retrieved from a sentence. Please think of this as just one of several answers.

Sample formula:

=SPLIT(REGEXREPLACE(REGEXREPLACE(IMPORTXML(A1,"//div[2]/p[1]/text()[last()]"),"\(\w.+\)|[^\d. ]","")," |. |.$","@"),"@",TRUE,TRUE)

In this case, https://www.infogol.net/matches/result/english-premier-league/everton-vs-wolves-2019-09-01/30701 is put in the cell "A1". The flow of this formula is as follows.

  1. Retrieve the HTML data from URL using the xpath of //div[2]/p[1]/text()[last()] with IMPORTXML.
  2. Retrieve the value from the HTML data using REGEXREPLACE.
  3. The values you want are retrieved using REGEXREPLACE and SPLIT.

Result:

enter image description here

Note:

  • This formula can be used for the current URL of https://www.infogol.net/matches/result/english-premier-league/everton-vs-wolves-2019-09-01/30701. When you want to use this for other URL and/or the page design of URL is changed, an error might occur. Please be careful this.

References:

If this was not the direction you want, I apologize.