Use importHTML and get multiple table with all next page in google sheet-CodePudding

Asked this same question before and got it answered and works well too.

Please read this thread :

2. If I write sequence 170 and above, the result comes like this. and

Error show : #VALUE - In ARRAY_LITERAL, an Array Literal was missing values for one or more rows.

This has been tried in Google Sheets and you can do it too.

Google Sheet: https://docs.google.com/spreadsheets/d/1yawdkBHkheaXeziWHFBFd3gngO3EQJr0d19lUH9cVfI/edit?usp=sharing

I have all the data from this link ie more than 4000 data. And there are about 170 pages. I hope to get more and more data easily.

URL : https://www.screener.in/screens/881782/rk-all-stocks/

Thank You.

CodePudding user response：

Probably, the results you have so far is due the limit of characters the cell can store and also the way the data is available on the website (i.e. the data source).

Also, IMO, for these kind of tasks, consider use Apps Script and/or use proper APIs.

I've added a new sheet called "SAMPLE_EACH_PAGE" - which contains a sample where I use the following formula:

=IMPORTHTML("https://www.screener.in/screens/881782/rk-all-stocks", "table",1)

and, for every page, add the following formula:

=IMPORTHTML("https://www.screener.in/screens/881782/rk-all-stocks?page=X", "table",1)

where X on page=X means the page number to get the data.

The catch here is that you need a way to set the formula each certain cells - every 26 or 27 cells if I count correctly.

CodePudding user response：

The issue is most likely because you are trying to fetch 170 pages while the source page tells Showing page 1 of 169. You should use an end page number that is no larger than 169.

You may also want to retrieve the data in smaller chunks. You can specify the start and end pages with this remix of player0's formula:

=arrayformula( 
  lambda( 
    baseUrl, pageStart, pageEnd, 
    query( 
      reduce( 
        importhtml(baseUrl & pageStart, "table"), 
        sequence(pageEnd - pageStart, 1, pageStart   1), 
        lambda( 
          result, pageNumber, 
          { 
            result; 
            iferror( 
              importhtml(baseUrl & pageNumber, "table"), 
              iferror(sequence(1, 11) / 0) 
            ) 
          } 
        ) 
      ), 
      "where Col1 is not null", 1 
    ) 
  )( 
    "https://www.screener.in/screens/881782/rk-all-stocks/?limit=25&page=", 1, 150 
  ) 
)

Replace the 1 with the start page and the 150 with the end page you want.