0

The screenshot below is the data that I want to export to another google sheet:

enter image description here

The screenshot below is my destination google sheet with data imported from the google sheet above:

enter image description here

May I know why ALL is missing in Cell CD14 when I import the data using IMPORTRANGE & QUERY? How should I fix it so that everything will be imported? Thank you.

weizer
  • 1,009
  • 3
  • 16
  • 39

2 Answers2

2

Query has issues with mixed data types. See also here

To work around that you can either format column CD as text (in the source table) or convert everything to text after it is imported.

=Arrayformula(iferror(query(importrange(...., ...)&"", "Where Col5 = 'Transmit'")))
JPV
  • 26,499
  • 4
  • 33
  • 48
  • Hi JPV, thanks for your solution! May I know what does `&""` behind `IMPORTRANGE`? – weizer Mar 04 '21 at 12:58
  • @weizer: Glad to hear that worked. &"" converts to text. – JPV Mar 04 '21 at 13:01
  • I realized that after I imported the data by using your formula, I'm not able to change the format in my destination sheet? Because the data in column `CJ` is actually a time. – weizer Mar 04 '21 at 13:23
1

You should be able to keep all original data types by using FILTER rather than QUERY, e.g.:

=IFERROR(FILTER(IMPORTRANGE(NameList!C3,"Sheet9!C14:J18"),IMPORTRANGE(NameList!C3,"Sheet9!G14:G18")="Transmit"))

I will add that, if you plan to be importing more than this one formula's worth of data using IMPORTRANGE, it will be more efficient to use IMPORTRANGE to import into its own sheet in your destination spreadsheet the entire range you'll ever want to reference from it, and then write your processing formulas referencing that new sheet rather than by making individual IMPORTRANGE calls (which will slow down your sheet). If you do use the one IMPORTRANGE call to get all the data you'll need into the destination sheet, you can just hide it and leave it in the background if you like.

Erik Tyler
  • 9,079
  • 2
  • 7
  • 11
  • I'm actually trying to import data from 10 source data sheets where all the data are in the same range in different sheets : `C14:J18`. That's why I'm using `QUERY` function as it allows me to do something like `Query({IMPORTRANGE(NameList!C3, "Sheet9!C14:J18"), IMPORTRANGE(NameList!C4, "Sheet9!C14:J18"),...}, "WHERE Col5 = 'Transmit'")` – weizer Mar 05 '21 at 02:32
  • All the more reason to make that collective and singular IMPORTRANGE call to a separate sheet in the destination, and then write other formulas referencing that newly added sheet. – Erik Tyler Mar 05 '21 at 03:47