import all rows of an expandable table with IMPORTHTML or IMPORTXML in Google Sheets

Question

I am interested in importing a table from a website. The does not load all rows at first; it expands as the user scrolls, eventually reaching the end.

I'm using a GoodReads account as an example.

I want to import all rows; however, as the url doesn't change, I believe I will need to use the IMPORTXML function rather than IMPORTHTML. However, I have not been able to identify an XPath that works.

IMPORTHTML displays the rows that initially populate when the page runs (url, "table", 2)

IMPORTXML currently displays text from rows that initially populate when the page runs in single cell

the following link has both options in individual sheets https://docs.google.com/spreadsheets/d/14jHGRyHKf866jrZiIfX2-GX6hZ2SE6vi_DezckpXaRs/edit?usp=sharing

Jason E. Jason E. · Accepted Answer · 2021-05-04T22:33:21

Upon checking the website you are trying to get data from, it the auto-expand of data while scrolling is controlled by JavaScript. Thus, it cannot be fetched using import() functions from google. This is why you can only fetch the initially available data.

To check whether a website is being controlled by JavaScript, click on the lock button beside the url->go to Site Settings->set JavaScript to 'block'->refresh the website and see the difference.

In your case, after blocking JavaScript, the website turned into this:

import all rows of an expandable table with IMPORTHTML or IMPORTXML in Google Sheets

1 Answers