Search code examples
arraysweb-scrapinggoogle-sheetsgoogle-sheets-querygoogle-sheets-formula

importhtml different number of columns in each URL throws error & further need to move/shuffle columns

I'm trying to import tables with different number of columns. This resulted in error "In ARRAY_LITERAL, an Array Literal was missing values for one or more row" it seems the number of columns should be the same.

Further I need help regarding second URL table where in the output:

a) B column should be in the G "H" column

b) Shuffle the columns like in the second URL table output, C,D columns should be in the B,C columns. E,F columns should be F,E. )

the sheet is attached here

Solution

  • your 2nd imported table outputs #N/A so you are actually putting 6 columns in array with one column/one cell

    enter image description here

    if you want to proceed anyway use:

    =QUERY({
 IFERROR(QUERY(IMPORTHTML(
 "https://www.financialexpress.com/market/stock-market/nse-top-gainers/", "table", 2),
 "where Col1 is not null", 1), {"","","","","",""});
 IFERROR(QUERY(IMPORTHTML(
 "https://www.financialexpress.com/market/stock-market/nse-buyers/",      "table", 2), 
 "where Col1 is not null offset 1", 0), {"","","","","",""})},
 "where Col1 is not null", 0)

    enter image description here

    UPDATE:

    =QUERY({
 IFERROR(QUERY(IMPORTHTML(
 "https://www.financialexpress.com/market/stock-market/nse-top-gainers/", "table", 2),
 "select Col1,' ',Col2,Col3,Col4,Col5,Col6 
  where Col1 is not null 
  label ' ''Bid Qty'", 1), {"","","","","",""});
 IFERROR(QUERY(IMPORTHTML(
 "https://www.financialexpress.com/market/stock-market/nse-buyers/",      "table", 2), 
 "where Col1 is not null offset 1", 0), {"","","","","","",""})},
 "where Col1 is not null", 1)

    enter image description here