ImportRange Function to Ignore Empty Cells

google-sheets google-sheets-formula google-sheets-query google-query-language importrange
Thomas Kauer picture Thomas Kauer · Jan 10, 2019 · Viewed 9.8k times · Source

I want to import a range from another Google sheet, but to ignore empty cells so that the data prints back to back (versus having big gaps created from importing empty cells). Here is my formula (Google link changed for confidentiality):

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/dalsfjasfdjjasfd/edit#gid=76574001",
 "Course_Schedule!F2:F77")

Put another way, import range of about result would be: blank cell, blank cell, 25, 43, blank cell

When I want it to be: 25, 43

Answer

player0 picture player0 · Jan 14, 2019 

=QUERY(IMPORTRANGE("1GVsBuYlJk2wxKgYjA2k_6ZlBoZ17frwyktfP6a0lUao",
 "Course_Schedule!F2:F77"), "select Col1 where Col1 is not Null", 0)

4

Related questions

Google Sheets: How do I filter view two columns OR instead of AND

In Google Sheets, I want to filter view my results so only rows with an "x" in column D AND/OR column E are shown. If I filter to show the columns with an "x" in them, it will only …

Read More
Google spreadsheet Query Error - column doesn't exist

Another problem with Google Spreadsheet API. I'm trying to use QUERY() function in order to get all customers from our database who are from Warsaw. Google API seems however to have a big problem with parsing my query. I've checked …

Read More
VLOOKUP within IMPORTRANGE

I'm trying this function on Google spreadsheet without success in order to gather in a sheet a value after a VLOOKUP: =importrange("otherurl";cell("address";vlookup(value("201608"),"All_nodevice!$A$16:$C$1000",2,false))) I get a general error. Does IMPORTRANGE support …

Read More