Scraping data to google sheets from a website that uses JavaScript

by

Tl;Dr

Adapted from my answer to How to know if Google Sheets IMPORTDATA, IMPORTFEED, IMPORTHTML or IMPORTXML functions are able to get data from a resource hosted on a website? (also posted by me)

Please spend some time learning how to use the browers developers tools so you will be able to identify

  • if the data is already included in source code of the webpage as JSON / literal JavaScript object or in another form
  • if the webpage is doing a GET or POST requests to retrieve the data and when those requests are done (i.e. as some point of the page parsing, or on event)
  • if the requests require data from cookies

Brief guide about how to use the web browser to find useful details about the webpage / data to import

  1. Open the source code and look if the required data is included. Sometimes the data is included as JSON and added to the DOM using JavaScript. In this case it might be possible to retrieve the data by using the Google Sheets functions or Url Fetch Service from Google Apps Script.
  2. Let say that you use Chrome. Open the Dev Tools, then look at the Elements tab. There you will see the DOM. It might be helpful to identify if the data that you want to import besides being on visible elements is included in hidden / not visible elements like <script> tags.
  3. Look at Source, there you might be able to see the JavaScript code. It might include the data that you want to import as JavaScript object (commonly referred as JSON).

There are a lot of questions about google-sheets +web-scraping that mentions problems using importhtml and/or importxml that already have answers and even many include code (JS snipets, Google Apps Script functions, etc) that might save you to have to use an specialized web-scraping tool that has a more stepped learning curve. At the bottom of this answer there is a list of questions about using Google Sheets builti-in funcitons including annotations of the workaround proposed.

On Is there a way to get a single response from a text/event-stream without using event listeners? ask about using EventSource. While this can’t be used on server side code, the answer show how to use the HtmlService to use it on client-side code and retrive the resoult to Google Sheets.

As you already realized, the Google Sheets built-in funcitions importhtml(), importxml(), importdata() and importfeed() only work with static pages that do not require signing in or other forms of authentication.

When the content of a public page is created dynamically by using JavaScript, it cannot be accessed with those functions, by the other hand the website’s webmaster may also purposefully have prevented web scraping.

How to identify if content is 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.

Below there are more details about how Google Sheets buil-in “web-scraping” functions works

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

Related

Software Recommentions

Web Applications

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

Similar questions

Some of this questions might be closed as duplicate of this one

More Related Contents:

  1. To exceed the ImportXML limit on Google Spreadsheet
  2. Long processing time likely due to getValue and cell inserts
  3. Why do we use SpreadsheetApp.flush();?
  4. Convert column index into corresponding column letter
  5. Merging or Combining two onEdit trigger functions
  6. How to poll a Google Doc from an add-on
  7. Google Spreadsheet Script – How to Transpose / Rotate Multi-dimensional Array?
  8. How to copy a row from one google spreadsheet to another google spreadsheet using google apps script?
  9. Emailing Google Sheet range (with or without formatting) as a HTML table in a Gmail message
  10. Google Sheets add-on: Set anonymous/dynamic functions to Menu using Google App Script
  11. Google Apps Script toast messages don’t appear for anonymous editors
  12. Synchronize independent spreadsheet rows, filled by IMPORTRANGE()
  13. How to speed ​up the search data in sheet
  14. How to Autofill last row with formula, when data is received from IFTTT on the last row?
  15. How to Transpose / Rotate Multi-dimensional Array?
  16. How to allow access for importrange function via apps script?
  17. How to fix ‘TypeError: Cannot call method “getRange” of null’ in Google Apps Script
  18. How to run certain scripts under the authority of a specific user?
  19. Set anonymous/dynamic functions to Menu
  20. Generate PDF of only one sheet of my spreadsheet
  21. Using Google Apps Script to scrape Dynamic Web Pages
  22. How do I replace text in a spreadsheet with Google Apps Script?
  23. How to make onEdit() trigger function apply to multiple sheets
  24. Google apps script: how to persist data in spreadsheet between different function calls?
  25. Google Script: Play Sound when a specific cell change the Value
  26. Failure of calling Google App Script onEdit function many times in a second
  27. Add timestamp to named column in Google Spreadsheet onEdit script
  28. What are some use cases for SpreadsheetApp.flush()?
  29. google script detect empty cell after edit or delete
  30. Converting Google spreadsheet date into a JS Date object?

Leave a Comment