google sheet importhtml with click "load more" from tradingview

Question

i'm trying to import data from tradingview, formula is;

=importhtml("https://www.tradingview.com/markets/stocks-usa/market-movers-all-stocks/","table",0)

however it only loads 100 rows, although there are more than 4900 rows in the dataset.

the webpage loads 100 rows everytime after "load more" button is clicked. but i need to import all 4902 rows into google sheets at once.

any help is appreciated. kind regards

i tried to dig into the page source, but it's too complicated for me.

Answer 1

Issue and workaround:

When I saw the HTML of your URL, it seemed that the values were retrieved from an API. The post method is used for requesting. Unfortunately, the post method cannot be used with IMPORTXML. So, in this answer, as a workaround, I would like to propose using Google Apps Script. When Google Apps Script is used, it seems that 4092 values can be retrieved. The sample script is as follows.

Sample script:

Please copy and paste the following script to the script editor of Google Spreadsheet, and save the script.

When you use this script, please put a custom function of =SAMPLE() into a cell. By this, 4092 values are retrieved.

function SAMPLE() {
  const url = "https://scanner.tradingview.com/america/scan";
  const header = ["name", "description", "close", "currency", "change", "volume", "market_cap_basic", "fundamental_currency_code", "price_earnings_ttm", "earnings_per_share_diluted_ttm", "earnings_per_share_diluted_yoy_growth_ttm", "dividends_yield_current", "sector", "recommendation_mark"];
  const payload = { "columns": header, "options": { "lang": "en" }, "range": [0, 4902], "sort": { "sortBy": "name", "sortOrder": "asc" }, "preset": "all_stocks" };
  const res = UrlFetchApp.fetch(url, { contentTyle: "application/json", payload: JSON.stringify(payload) });
  const obj = JSON.parse(res.getContentText());
  return [header, ...obj.data.map(({ d }) => d)];
}

Testing:

When this script is run, the following result is obtained.

Note:

• When you want to directly put the values to cells without using a custom function, please use the following sample script.

  function myFunction() {
    const url = "https://scanner.tradingview.com/america/scan";
    const header = ["name", "description", "close", "currency", "change", "volume", "market_cap_basic", "fundamental_currency_code", "price_earnings_ttm", "earnings_per_share_diluted_ttm", "earnings_per_share_diluted_yoy_growth_ttm", "dividends_yield_current", "sector", "recommendation_mark"];
    const payload = { "columns": header, "options": { "lang": "en" }, "range": [0, 4902], "sort": { "sortBy": "name", "sortOrder": "asc" }, "preset": "all_stocks" };
    const res = UrlFetchApp.fetch(url, { contentTyle: "application/json", payload: JSON.stringify(payload) });
    const obj = JSON.parse(res.getContentText());
    const values = [header, ...obj.data.map(({ d }) => d)];
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); // Please set your sheet name.
    sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
  }
  

Reference:

• fetch(url, params)