0

Using the IMPORTXML function in Google Sheets, I'm trying to get the 'properties' element values from this link to appear with their element names as column headers.

Using the below XPath query outside Google Sheets, I'm able to get the data to display the element names and their values in rows separated by a space. In Google Sheets, I get an error (Imported Xml content can not be parsed.).

//*[local-name() = 'properties']/*[not(*)]/concat(local-name(), ' ', .)

I have a sample spreadsheet here with the desired output. Cell B12 contains the IMPORTXML formula that refers to the XPath in cell B4 (labeled as XPATH Query 2). Cell B3 ("XPATH Query 1") contains the query that seems to work outside of Google Sheets.

The below image displays the current output of cell B12 outlined in red and the desired output beginning in cell D11.

enter image description here

8
  • No repro. I just made a copy of your spreadsheet; the formula in B12 using IMPORTXML returns the referred error; in my copy, it returned values instead of an error. The formula uses a different xPath. Anyway, while a link to the spreadsheet is helpful, questions should be self-contained, this means that the relevant parts of the spreadsheet should be described in the question as well as the formulas and function parameters should be included in the question. Sep 17, 2023 at 16:49
  • @Rubén Thanks for the feedback. The formula in cell B12, referring to the query in cell B4, results in just the element values without the element names as displayed in the table starting in cell D11. Changing the formula in B12 to refer to the query in cell B3 results in a parsing error in Google Sheets but the query seems to work elsewhere. Help is appreciated.
    – Catalyx
    Sep 17, 2023 at 19:32
  • I just opened my copy; this time, the formula in B12 returns an error, but the formula in B12 of the spreadsheet linked in the question works. This inconsistency makes the question non-suitable for this site as troubleshooting-like questions are off-topic. Sep 17, 2023 at 19:46
  • One option is to eliminate the inconsistencies, in this case, the formula in B4. If this is just added to display the expected result, let's remove the formulas by doing a copy-paste as value only, and focus on xPath of B3. Sep 17, 2023 at 19:53
  • By the way, when text xPath in external tools, be sure that this tool uses xPath 1.0 Sep 17, 2023 at 20:07

2 Answers 2

2

Alternatives

Here are alternative approaches to get the data in your preferred layout:

  1. IMPORTDATA
    =IMPORTDATA(url, delimiter)
    Imports from a URL in CSV or TSV format.

    =LET(url, "https://home.treasury.gov/resource-center/data-chart-center/interest-rates/daily-treasury-rates.csv/2022/all?type=daily_treasury_yield_curve&field_tdr_date_value=2022&page&_format=csv",
         IMPORTDATA(url, ","))
    
  2. IMPORTHTML
    =IMPORTHTML(url, query, index)
    Imports from a table or list within an HTML page.

    =LET(url, "https://home.treasury.gov/resource-center/data-chart-center/interest-rates/TextView?type=daily_treasury_yield_curve&field_tdr_date_value=2022",
         table, IMPORTHTML(url, "table"),
         CHOOSECOLS(table, 1, SEQUENCE(1,13,11)))
    
    • CHOOSECOLS is used to remove 9 columns from the array that were hidden columns in the source table and are not required.
    • Column 1 (NEW_DATE) is manually entered;
    • The remain columns are specified by generating a 13 number SEQUENCE from 11 to 23

Intermittent Connection Issues

I also encountered intermittent periods during testing where the URL would stop resolving.

This affected IMPORTXML and IMPORTHTML but I didn't witness it with IMPORTDATA. This may highlight a difference or I may simply have been lucky with IMPORTDATA and it would have behaved similarly eventually.

Modifying the URL to point to a different year then changing it back seemed to resolve the issue when it showed up.

2

Things to have in mind about using xPath on IMPORTXML

  1. It supports xPath 1.0. An old version of the IMPORTXML help article mentioned the supported version; the current version points to a third-party tutorial about xPath hosted on https://www.w3schools.com/ that used to be unreliable. I don't know if they have improved or how Google has decided to point to that site. All of the xPath testers I found searching the Web support xPath 2.0.
  2. Some time ago, I read that IMPORTXML can't return xPath function results because the above doesn't support xPath 2.0. I don't have a source to support this, but I haven't found examples that prove the contrary either.

Considering the above, and due to the difficulty of controlling the recalculation of spreadsheet formulas in Google Sheets, I lean to use Google Apps Script, the Url Fetch Service and the XML service.

One option might be to create a custom function. This might work similarly to IMPORTXML but might include a custom retry algorithm while the execution time does not exceed 30 seconds. If a longer execution time is required, the function could be triggered by an installable trigger, a custom menu, and other options.

2
  • Yes, the documentation for Google Sheets doesn't make clear which XPath version is used though it seems v1.0. I understand the concat expression I used is v2.0. From asking around, v1.0 doesn't seem to support returning the element names either. so a custom function may be the only alternative at the moment when the data are only in XML.
    – Catalyx
    Sep 18, 2023 at 23:00
  • @Catalyx I think that the problem is that IMPORTXML can't be included in the output of functions like concat, but it could be used for filtering. Considering this, it might be not precise to say that IMPORTXML doesn't support concat, it might be more precise to say that it doesn't fully support xPath 2.0. Anyway, the point is that IMPORTXML can't use all the XPath expressions. Sep 19, 2023 at 0:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.