Search code examples
google-sheetsxpathgoogle-sheets-formula

What is the Xpath to IMPORTXML to get the full <link> tag which have the @hreflang attribute

I want to import all tag which has the attribute [hreflang]. So far, I can only able to import the attribute value using the following formula.

=IMPORTXML("https://allbirds.eu","//link[@hreflang]/@href")

Now I am getting the target attribute value only (for example @href in this case: https://fr-FR.allbirds.eu/). I can not import the tag itself. I want to import the tag itself. My expected result is <link rel="alternate" hreflang="de-DE" href="https://de-DE.allbirds.eu/">

Please give me your suggestion which xpath can give me the result?

Thank you

Solution

  • In your situation, how about using Google Apps Script as follows?

    Sample script:

    Please copy and paste the following script to the script editor of Spreadsheet and save the script. When you use this script, please put a custom function of =SAMPLE("https://allbirds.eu") to a cell.

    const SAMPLE = url => [...UrlFetchApp.fetch(url).getContentText().matchAll(/<link[\w\s\S].+?>/g)].flatMap(([e]) => e.includes("hreflang") ? [e] : []);

    Result:

    enter image description here

    Note:

    • This sample script is for your URL of https://allbirds.eu. When you changed the URL, this script might not be able to be used. Please be careful this.

    Reference:

    Added:

    I thought that IMPORTXML cannot directly retrieve the value including the tag like <link rel="alternate" hreflang="de-DE" href="https://de-DE.allbirds.eu/">. So I proposed to use Google Apps Script. But, from your following reply,

    But I'm looking for a solution with XPATH. Apps Script is my last option.

    If you are required to use IMPORTXML and the xpath, how about the following sample formula?

    =ARRAYFORMULA("<link rel=""alternate"" hreflang="""&IMPORTXML("https://allbirds.eu","//link[@hreflang]/@hreflang")&""" href="""&IMPORTXML("https://allbirds.eu","//link[@hreflang]/@href")&""" />")

    In this case, the same result with the above sample Google Apps Script can be obtained.