Google Sheets Query Image to show up from Query result

2
votes

I can't figure out how to get an image to display in cells in my gsheet when they are coming out of a query. I've tried various forms of arrayformula and query combos but nothing. Would love any help.

Tried this, with A4:A21 being the image URLs =ARRAYFORMULA(QUERY({B4:B21, image(A4:A21)}, "select Col1, Col2 LABEL Col1 'ID', Col2 'Image Showing'", 1))

Also tried this, with A4:A21 being the image URLs**

=ARRAYFORMULA(QUERY({B4:B21, "=image(""" & A4:A21 & """)"},

"select Col1, Col2 LABEL Col1 'ID', Col2 'Image Showing'", 1))

Any help is greatly appreciated.

Sample sheet

2
google-sheetsgoogle-sheets-formula

2 Answers

2
votes

Query doesn't recognize Image type. See this, which points to the QUERY() manual.

Each column of data can only hold boolean, numeric (including date/time types) or string values.

Instead, you can do what you're trying with HLOOKUP()s:

=ArrayFormula({HLOOKUP("ID",B4:B21,ROW(B4:B21)-3),IMAGE(HLOOKUP("Image URL",A4:A21,ROW(A4:A21)-3))})

or

=ArrayFormula(
{
    HLOOKUP("ID",B4:B21,ROW(B4:B21)-3),
    IMAGE(HLOOKUP("Image URL",A4:A21,ROW(A4:A21)-3))
})

Looks up each url and applies IMAGE to it after.

Edit: Accounting for the Display Status Column:

=ArrayFormula(
{
    HLOOKUP("ID",B4:B21,ROW(B4:B21)-3),
    IF(C4:C21="Yes",IMAGE(HLOOKUP("Image URL",A4:A21,ROW(A4:A21)-3)),)
})
0
votes

Here's a solution using ARRAYFORMULA and IMAGE(QUERY()) and it accounts for multiple columns!

Google Sheets Example

=ARRAYFORMULA({

  ({
    QUERY(
      {data!A1:C}, "SELECT Col1 WHERE Col1 is not null ORDER BY Col1 asc"
    )
  }),

  ({
    "IMG";
    IMAGE(
      QUERY(
        {data!A2:C}, "SELECT Col3 WHERE Col3 is not null ORDER BY Col1 asc"
      ),2
    )
  }),

  ({
    QUERY(
      {data!A1:C}, "SELECT Col2 WHERE Col1 is not null ORDER BY Col1 asc"
    )
  })

})

enter image description here