1

I'm attempting to retrieve information from this XML page into a Google Sheets workbook using the IMPORTXML function.

The following formula, which had worked before, now returns a "Could not fetch url" error:

=IMPORTXML(<referenced URL>,"//*[local-name() = 'assigned-sic-desc']")

Changing https:// to http://, as suggested here, did not work.

A similar question to mine above using the IMPORTHTML function has a response that a site's robots.txt file may be a reason for such error.

Though this question's site's robots.txt file lists the path ending in /cgi-bin, Google Sheets has no problem fetching the same URL with the IMPORTFEED function in the formula below (this function cannot retrieve the information needed):

=IMPORTFEED(<referenced URL>,"feed author")

Note also that other URLs that include the /cgi-bin path returned the correct information with IMPORTXML or IMPORTHTML functions and that the HTML output for the referenced URL (i.e. without the &output=atom in the URL query) has a hyperlink labeled "RSS Feed" pointing to the XML webapge, possibly suggesting that the page is allowed to be indexed or crawled by search engines.

QUESTIONS

  1. [RESOLVED in comment] Is this possibly an inconsistency by the website or Google Sheet or is something missing?
  2. Is there another way to retrieve the data into Google Sheets from the XML webpage or the HTML webpage?
1
  • To answer Question 1, this problem failing IMPORT- function calls appears to be a bug reported to and known by Google per its IssueTracker page. Question 2 remains open.
    – Catalyx
    Feb 14, 2022 at 21:06

1 Answer 1

1

UNSUPPORTED GOOGLE SHEETS FEATURE

As of 2022, most API or other calls for data to sec.gov requires a declared customized header, per this other question on StackOverflow.

Google Sheets has a static header and does not permit it to be customized, per this answer to another StackOverflow question.

It is therefore not possible to query the website using Google Sheets.

ALTERNATE APPROACHES

As an alternative for spreadsheet users, Microsoft Excel's Power Query or an open-source spreadsheet application's equivalent can perform the query. If Google Sheets is necessary, the resulting file from the alternative spreadsheet app can be referenced by the Google Sheets file via Google Drive.

1
  • Programming languages, such as Python, that support saving spreadsheet files in various formats may also support this feature. For users wanting to stay within Google's ecosystem of apps, Google's Colab may also support this approach. These alternatve approaches are not included in the above answer as the query and functions used in the OP's question were spreadsheet-based.
    – Catalyx
    May 31, 2022 at 13:10

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.