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


enter image description here

Pattern 2:

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

Sample script:

Please put =SAMPLE("") 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.


  • These sample formula and script are for the URL of When other URL is used, the result you expected might not be retrieved. Please be careful this.


CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top