1

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

  • 1
    Related: Do we need to extend the [importxml] wiki? – Rubén Mar 22 '18 at 18:06
  • no judging tho, but kinda don't understand the logic of bounty-ing question of yours which already has an answer of yours accepted year ago. – user0 Mar 9 at 12:58
  • @user0 Thanks for asking. I would like to receive more feedback about this Q/A. The question only has on +1 and one -1, it has very few comments and only one answer while other self answered questions have being answer by others where several of them are better than the OP answer. – Rubén Mar 9 at 16:08
  • 2
    I don't understand what this question is actually asking for. At present, the single answer covers the case of invalid sources (but that is, naturally, not a Sheets matter then). As for doing in-place testing, "How do I know if these functions are able to get the content that I looking to import?" Wrapping your "IMPORT…" functions in an IFERROR will let you functionally/programmatically know it failed. That's obviously oversimplified but, then… what else is the question asking for? (All written in good faith; hoping to nudge things forward. Cheers.) – Joel Reid Mar 12 at 17:05
  • @JoelReid Thanks for the feedback. This Q/A was an attempt to get feedback about how to help people looking for help about using the Google Sheets import spreadsheet functions. – Rubén Mar 16 at 3:48
2

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

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

  • 1
    I think that the most important part of this answer regarding the question is "all starts with disabling JavaScript for the website to see what's left and what's possible to be scrapped" – Rubén Mar 13 at 21:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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