Googlesheets ... Whats the best way to

I want to populate one sheet with data from multiple sheets also removing any blank rows. What is the best formulas to use? Importrange or what?

1 Like

Hard to answer without example sheets as there are many ways depending on the data.

Wrap the import ranges with a query function and then use a Where Col1 is not null, to get rid of the blank rows. You can also add the same conditions with Col1, Col2, Col 3… (use a OR between each condition if several columns) if needed. It depends on the sheets data.

=QUERY({IMPORTRANGE(…);IMPORTRANGE(…)},“Select * Where Col1 is not null”,1)

I set the category of your post : spreadsheet magics. To avoid mixing Glide tips and Google Sheets tips.

ty ill give that a try Christophe_hk

here is what i did and getting an error:
=QUERY({IMPORTRANGE(“1ODR1M5ERbGUlk_AMxKwyXlC6dwcBcHWfz-KIYnQM5bQ”,“Kaneb!A3:J”);IMPORTRANGE(“1nlhoEQXmA9w4l9FrO2S58Qypef13XjRzLIjvNCnuMXo”,“NigsII!A3:J)},“Select * Where Col1 is not null”,1)”))

note: i also did the importrange for each sheet and granted access before i did the above.

Remove the last "))

arrrgh now getting formula parse error with

=QUERY({IMPORTRANGE(“1ODR1M5ERbGUlk_AMxKwyXlC6dwcBcHWfz-KIYnQM5bQ”,“Kaneb!A3:J”);IMPORTRANGE(“1nlhoEQXmA9w4l9FrO2S58Qypef13XjRzLIjvNCnuMXo”,“NigsII!A3:J)},“Select * Where Col1 is not null”,1)

Between the 2 IMPORTRANGE, replace the ; by a ,

and have a beer! :blush:

1 Like

im tryin and ty for helpin so far no go hmm

here is what i have and as you said change between the importrange change the ; to a, also you told me to remove the brackets at the end but it keeps adding them back

=QUERY({IMPORTRANGE(“1ODR1M5ERbGUlk_AMxKwyXlC6dwcBcHWfz-KIYnQM5bQ”,“Kaneb!A3:J”),IMPORTRANGE(“1nlhoEQXmA9w4l9FrO2S58Qypef13XjRzLIjvNCnuMXo”,“NigsII!A3:J")},“Select * Where Col1 is not null”,1"))

That is why I suggested you share a sample spreadsheet. Doing it without the actual data is just poking at it.

1 Like

its just 10 columns of text and the same 10 columns from each sheet

As George said, not easy without your spreadsheet.

BUT try the following :

=QUERY({IMPORTRANGE("1ODR1M5ERbGUlk_AMxKwyXlC6dwcBcHWfz-KIYnQM5bQ”,“Kaneb!A3:J”),IMPORTRANGE(“1nlhoEQXmA9w4l9FrO2S58Qypef13XjRzLIjvNCnuMXo”,“NigsII!A3:J")},“Select * Where Col1 is not null”,1)

i tried that to Christophe and it comes back with Formula parse error. and added "
)) to the end of formula

Good info, but if someone would want to assist you they would have to create a spreadsheet, create a few sheets, populate those sheets with data, then work on the formula. I’m just saying that if you shared a spreadsheet it would make it easier on the person trying to help you.

i do understand George and i have watched a few videos to try and get the formula right as well , there is nothing complex in the sheets but text.

Very strange @Woody, for me it works. Maybe special (invisible) characters hidden in your formula. Could you try to copy it and paste it in something like notepad. Then copy and paste in your gsheet.

k TX George and Christophe ill keep tryin til i get it!