repeat the country name for each of the leagues

I believe your goal as follows.

  • You want to achieve Final Expected Goal at the bottom of your question.
  • In your formula, it seems that the titles of “COUNTRIES” include the titles of “LEAGUES”. For example, “Bundesliga” is included in “COUNTRIES”. And “Belgium” is included in “LEAGUES”. I thought that this might be different from your expected result.

Pattern 1:

In this pattern, the built-in functions in Spreadsheet is used.

  • The values of “COUNTRIES” can be retrieved with the xpath of //country/title.
  • The values of “LEAGUES” can be retrieved with the xpath of //leagues/title.
    • In your case, there are several values of “LEAGUES” in a “COUNTRIES”.
  • In order to retrieve “COUNTRIES” for each “LEAGUES”, I used //leagues[position()>1]/id | //country/title and I referred the sample formula from this site.

From above xpath and formula, I would like to propose the following sample formula.

Sample formula:

={
ARRAYFORMULA(LOOKUP(ROW(A2:A64),ROW(A2:A64)/IF(IMPORTXML(A1,"//leagues[position()>1]/id | //country/title")<>0,TRUE,FALSE),
IMPORTXML(A1,"//leagues[position()>1]/id | //country/title"))),IMPORTXML(A1,"//leagues/title")
}
  • In this formula, the URL of http://lineuptoday.com/api/matches/bydate?date=01&month=11&year=2020 is put in a cell “A1”.
  • As an important point, in this case, it is required to set the last row number (in your case, it’s 64.) When the last row number is not set, the formula becomes a bit complecate and the process cost becomes very high. So in this answer, as another method, I would like to also propose a custom formula created with Google Apps Script. In this case, the process cost might be a bit reduction.

Result:

enter image description here

Pattern 2:

In this pattern, the custom function created with Google Apps Script is used.

Sample script:

Please put =SAMPLE("http://lineuptoday.com/api/matches/bydate?date=01&month=11&year=2020") to a cell.

function SAMPLE(url) {
  const res = UrlFetchApp.fetch(url);
  const root = XmlService.parse(res.getContentText()).getRootElement();
  const n = root.getNamespace();
  return root.getChildren().reduce((ar, c) => {
    const titleCountry = c.getChild("title", n).getText();
    c.getChildren("leagues", n).forEach(l => {
      ar.push([titleCountry, l.getChild("title", n).getText()])
    });
    return ar;
  }, []);
}
  • In this case, the output result is the same with above sample formula. But it is not required to set the last row number.

Note:

  • These sample formula and script are for the URL of http://lineuptoday.com/api/matches/bydate?date=01&month=11&year=2020. When other URL is used, the result you expected might not be retrieved. Please be careful this.

References:

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top