Google-sheets – IMPORTRANGE + QUERY won’t import IMAGE

google sheetsgoogle-sheets-queryimportrange

I use Sheets in my business to create product catalogs for wholesale prices. I have different sheets for different companies but the data in many of them is the same. I'd like these sheets to sync from the main document. I've found a solution on here, however when I use the following function, the image imported in the main sheet using the IMAGE function doesn't get imported:

=QUERY(IMPORTRANGE("key","All!A2:M130"),"SELECT * WHERE Col1 contains 'Home&Beauty&Fashion'")

Is it possible at all?

Best Answer

This has nothing to do with ImportRange; the issue can be reproduced using Query within the same sheet. The documentation explains that Query only supports

boolean, numeric (including date/time types) or string values.

As far as Query is concerned, an image contained in a spreadsheet is simply a null value.

A workaround is to store URL strings themselves in the master sheet. These can be retrieved by Query, and then Image can be applied to the result.

Related Solutions

Google Sheets – Importing Columns of Data

Ok, I think I've figured it out. Not straightforward, but now that it's done, not terribly difficult.

**=VLOOKUP(D1,'TARGET_SHEET'|A:B,2)**

Some explanations about why I couldn't get the answer.

Criterion (the D1 here) does not need to be a string. It can be a cell reference.
A1 notation for cells can be used to refer to complete columns (did not know that). So A:B is perfect syntax in A1 notation. http://msdn.microsoft.com/en-us/library/office/bb211395%28v=office.12%29.aspx
The array by definition needs to be 2 or more columns. (duh!) The value for the index parameter will likely be "2" (for a two column array). If the array had 3 columns, more than likely the value would be 2 or 3.
You can auto-increment the values for this vlookup formula by grabbing the handlebars and pushing them down on the result column. So: in the cell immediately below the one with the formula, you will see =VLOOKUP(D2,'TARGET_SHEET'|A:B,2)

Google-sheets – How to nest IMPORTRANGE into the Query function in New Google Sheets

Please try something like:

=query(importrange("https://docs.google.com/spreadsheets/$$$$","Applicant Form Response!A:G"),"Select * where Col7 = 'Pass' ")

where $$$$ is whatever is appropriate to complete the URL for your source (access to which you will need to grant).

Best Answer

Related Solutions

Google Sheets – Importing Columns of Data

Google-sheets – How to nest IMPORTRANGE into the Query function in New Google Sheets

Related Topic