Search code examples
web-scrapinggoogle-sheetsarray-formulasgoogle-sheets-querygoogle-sheets-formula

Format Query Properly Into Google Sheets / Weather Formula

I have a Google Sheet of people who I will be outreaching to and some of the columns it includes are the City and State. Using the location, I want to pull the weather and temperature at a certain date and be inputted into its own cell in its proper row.

This is the formula I'm currently using:

=Query(IMPORTHTML("https://www.timeanddate.com/weather/usa/"&Substitute(lower(TRIM(A2))," ","-")&"/ext","table",1)," select Col3,Col4,Col5,Col6,Col8 where Col1='"&text(B2,"DDD")&Char(10)&text(B2,"MMM")&" "&index(split(B2,"/"),1,2)&"' ",2)

Which results in this:

enter image description here

I'd like to do this: First row example.

enter image description here

Solution

  • try:

    =ARRAYFORMULA(QUERY(TO_TEXT(IMPORTHTML("https://www.timeanddate.com/weather/usa/"&
 SUBSTITUTE(LOWER(TRIM(A2)), " ", "-")&"/ext", "table", 1)), 
 "select Col3,Col4 
  where Col1 = '"&TEXT(B2, "DDD")&CHAR(10)&TEXT(B2, "MMM")&" "&DAY(B2)&"'", 0))

    0