How to know if Google Sheets IMPORTDATA, IMPORTFEED, IMPORTHTML or IMPORTXML functions are able to get data from a resource hosted on a website?

Question

Google Sheets has several import functions

• IMPORTDATA
• IMPORTFEED
• IMPORTHTML
• IMPORTXML

Sometimes the above functions returns errors like #N/A Imported content is empty and would like to be sure that there isn't a problem with the resource content to be imported.

How do I know if these functions are able to get the content that I looking to import?

I know that there also exist IMPORTRANGE but that function only is able to import content from Google Sheets spreadsheets

Answer 1

Tl;Dr

The importhtml(), importxml(), importdata() and importfeed() functions only work with public pages that do not require signing in or other forms of authentication.

Further, if the content of a public page is created dynamically by using JavaScript, it cannot be accessed with those functions. The website's owner may also purposefully have prevented web scraping.

---

Content added dynamically

To check if the content is added dynamically, using Chrome,

1. Open the URL of the source data.
2. Press F12 to open Chrome Developer Tools
3. Press Control+Shift+P to open the Command Menu.
4. Start typing javascript, select Disable JavaScript, and then press Enter to run the command. JavaScript is now disabled.

JavaScript will remain disabled in this tab so long as you have DevTools open.

Reload the page to see if the content that you want to import is shown, if it's shown it could be imported by using Google Sheets built-in functions, otherwise it's not possible but might be possible by using other means for doing web scraping.

According to Wikipedia, Web scraping, web harvesting, or web data extraction is data scraping used for extracting data from websites.

Use of robots.txt to block webcrawlers

The webmasters could use robots.txt file to block access to website. In such case the result will be #N/A Could not fetch url.

Use of User agent

The webpage could be designed to return a special a custom message instead of the data.

---

IMPORTDATA, IMPORTFEED, IMPORTHTML and IMPORTXML are able to get content from resources hosted on websites that are:

• Publicly available. This means that the resource doesn't require authorization / to be logged in into any service to access it.
• The content is "static". This mean that if you open the resource using the view source code option of modern web browsers it will be displayed as plain text.
  • NOTE: The Chrome's Inspect tool shows the parsed DOM; in other works the actual structure/content of the web page which could be dynamically modified by JavaScript code or browser extensions/plugins.
• The content has the appropriated structure.
  • IMPORTDATA works with structured content as csv or tsv doesn't matter of the file extension of the resource.
  • IMPORTFEED works with marked up content as ATOM/RSS
  • IMPORTHTML works with marked up content as HTML that includes properly markedup list or tables.
  • IMPORTXML works with marked up content as XML or any of its variants like XHTML.
• The content doesn't exceeds the maximum size. Google haven't disclosed this limit but the below error will be shown when the content exceeds the maximum size:

  Resource at url contents exceeded maximum size.

• Google servers are not blocked by means of robots.txt or the user agent.

On W3C Markup Validator there are several tools to checkout is the resources had been properly marked up.

Regarding CSV check out Are there known services to validate CSV files

It's worth to note that the spreadsheet

• should have enough room for the imported content; Google Sheets has a 10 million cell limit by spreadsheet, according to this post a columns limit of 18278, and a 50 thousand characters as cell content even as a value or formula.
• it doesn't handle well large in-cell content; the "limit" depends on the user screen size and resolution as now it's possible to zoom in/out.

---

References

• https://developers.google.com/web/tools/chrome-devtools/javascript/disable
• https://en.wikipedia.org/wiki/Web_scraping

Related

• Using Google Apps Script to scrape Dynamic Web Pages
• Scraping data from website using vba
• Web scraping tool/software availabe for free?
• Recommendations for web scraping tools that require minimal installation

The following question is about a different result, #N/A Could not fetch url

• Inability to use `IMPORTHTML` in Google sheets

Answer 2

(maybe not the answer, but here is a quick humble scraping guide)

• all starts with disabling JavaScript for the website to see what's left and what's possible to be scrapped
• the first choice is always IMPORTHTML and slowly (1-by-1 row) dragged down

  =IMPORTHTML("URL"; "TABLE"; ROW(A1)-1)

• if that fails then repeat it with:

  =IMPORTHTML("URL"; "LIST"; ROW(A1)-1)

• if that also fails then switch to IMPORTXML

  =IMPORTXML("URL"; "//*")

• if this returns something other than #N/A it can be either narrowed down with XPath or regexed/queried out. if still no luck, there is possibility of using API calls or JSON data instead of public URL (if exists)

  =IMPORTXML("URL"; "//div[@class='primary']")

• if IMPORTXML can't get JSON then there are custom IMPORTJSON functions on GitHub
• as a last resort there is IMPORTDATA and scraping stuff from source code like:

  =ARRAY_CONSTRAIN(IMPORTDATA("URL"); 8000; 20)

  =REGEXEXTRACT(QUERY(ARRAY_CONSTRAIN(IMPORTDATA("URL"); 3000; 2); 
   "where Col1 contains 'meta table id something'"); "\>(.+)\<")

• and if after all atempts the result is still #N/A, there is always time to give up and find some similar site which can be scraped

(a few more RL examples of mentioned techniques here)