QUERY - Select a Range of Multiple Columns in Google Sheets
HTML-код
- Опубликовано: 13 сен 2024
- How to get QUERY function in Google Sheets to select a range of columns?
QUERY function playlist • Google Sheets Query fu...
#query #googlesheets #advanced
I'm following your lessons from France. Your pronunciation for a non native English listener and your teaching techniques are brilliant! I have so much improved my skills at work. I thank you so much!
Greetings from Paraguay. You are the best teacher ever. The content of your channel is liquid gold! I just asked my sister if she wants to marry you...
I binge watch ALL your videos. You are the goTo guy for advanced sheets stuff. This helps a lot!
I could never do query function. I just did filter function. Now I am starting with query function, Thanks again your videos so helpful, I learn so much.
so cool, as always! thanks for doing this!
Thank you for the new trick to use in the query formula. Am I the only one who gets excited when I see a new video posted by you? 🙂
The Choosecols function, which was not available at the time of this video, can make the process easier. You can use Sequence inside Choosecols to select a range of columns. For example, if you want columns 3-7 and 10-15, the formula would look like this: =choosecols(query(TEXT!A1:T,"select *",1),sequence(5,1,3),sequence(6,1,10)). Forgivingly, it also works if you swap the rows and columns in the Sequence; i.e., in a Choosecols function, sequence(1,5,3) works the same as sequence sequence(5,1,3). If you have a long string in your Query, then putting it in a Let function and inserting some returns makes it simpler.
Thanks a million pal. That information helped me a lot. All the best
Never used SEQUENCE, awesome to learn, thanks!
As always, my 0,02 $ just to give some alternatives:
1. you can use MATCH to find the col number:
=QUERY({TEXT!A2:G};"Select Col"&MATCH(A1;TEXT!1:1;0)&", Col"&MATCH(B1;TEXT!1:1;0)&", Col"&MATCH(C1;TEXT!1:1;0);1)
you need to set the data array to skip the headers and the query sheet will need to have column names in it to look for.
this can be very powerful in combination with a validated list for the column headers
2. you can replace the ARRAYFORMULA with just a join (both formula's below amount to the same):
=ARRAYFORMULA("SELECT " &JOIN(", "; "Col"&Sequence(1;10)))
="SELECT Col" & JOIN(", Col";SEQUENCE(1;10))
Thanks for sharing this
A great video, well presented and very informative. Well done and thanks.
Really useful for large data sets. Thanks as always!
I never comment on videos - but I have been using your video as guides and have managed to automate a lot of my business. I want to sincerely thank you for your videos. You are doing an awesome job and a great service. Always look forward to new videos. I was wondering if you have the time; could you do a video on google sheets and whatsapp integration - for example - like sending whatsapp message from sheets - or even better can take a message from whatsapp and pull corresponding data from the sheet and send back message to whatsapp.
Anyways keep up the good work!
Unfortunately, whatsapp currently doesn't have a publicly available API.
Just what I was looking for. Many Thanks!
Nice Best Unique
Thank you very useful, Can we have the same idea with a dynamic range of rows.
Amazing trick! Seriously, one of the most brilliant! Your channel is the best one.
Wow, thanks!
Thanks a lot! I had same issue early today! 😀
Thanks for putting these great videos together! They’re always helpful and spark new ideas.
I was wondering if there is a way to dynamically select multiple columns by using checkboxes
I have recorded a video on this. It will most likely be published next week.
@@ExcelGoogleSheets can't wait for that one!
Great
That is awesome buddy! You just solved my problem today. Thank you so much. New subscriber here from the Philippines.
👍
will this work if I am getting the data from a different sheet? (i am always watching your tutorial btw, and it helps me a lot! thank you so much!
Great tutorial video! Keep it up! I am trying to create a registration form, what would be the formula within this formula I want to pull data to how many people are registering? Would I have to create several different spreadsheet? For example 1 person, 2 people, 3 people, etc. But I don't want the data for 1 person to show in my spreadsheet for 3 people.
AWESONE...The true Master!!!
AWESOME trick, thanks
Thank you so much.
Thank You!
I currently have a query with such a long column list (C to AF) I almost feel ashamed of it.
This will no longer happen!
Hi sir, you're the best teacher as always! Is there a way that I could select, unselect and select all data in a certain range dynamically thru data validation? Hope you may read my comment. Thank you very much sir!
Brilliant !
Can we do same thing with rows?
Wow.. magic
how would I select every x row
like if x was 5 how would I have it select rows 5 10 15 20 25 etc
That is so clever.
In Join function, fixed Range as per your instruction works well, when I removed fixed Range , data add in another row join function get problem , not update , please guide me
It doesn't work for me when I'm trying to merge two ranges =QUERY({Arkusz2!B3:C;Arkusz2!E3:G};"Select *";1). The error is "In ARRAY_LITERAL, an Array Literal was missing values for one or more row". What's wrong?
have the same problem. help plz!
@@r.lemesh try something like that =QUERY({Dane2013!A2:B25\Dane2013!D2:J25};"Select *";1) it's different, but it works for me
@@maciejrodak3590 Thanks a lot, it works for me too. It seems like in NOT US formatted spreadsheets we should use "\" instead "," for uniting different columns in same range.
@Maciej Rodak Thank you. A character changes everything.
Muito obrigado por compartilhar seu conhecimento!
Is there a way to make the columns that are selected based on a condition? For example, if I want to ignore columns that are blank, can I make it so it only selects columns that have text or numbers in them?
You should do a lesson on having a column name be dynamically linked within a query. (There's a fun hack so that if you change column order, you don't need to rewrite the overall query)
"Select "®exreplace(ADDRESS(1,COLUMN('Sheet1'!$A$1),4),"[0-9].*","")&" where...
sir will the query function do column wise copy.for example if there are sheets with names Eng,Maths,Language and Each sheet with column labels test1,test2,test3. i want to pull out the values of test1/test2/test3 of all 3 subjects in a single table based on the drop down list.What is the query to be given. help in this regard
Great video thanks so much. Is it possible to add WHERE conditions in this formula ? I mean SELECT * WHERE Col 1 = 2021 for example.
Sure. Just chain it with &" WHERE Col1 = 2021"
Thank you very much for this video. I found the technique very useful in simplifying some of my QUERY formulas. But if I may ask a follow-up question. I tried to use the same technique of JOIN and SEQUENCE with ARRAYFORMULA, but this time with a VLOOKUP within a QUERY (so I could do some 'sumproduct-like' calculations with a pivot table). Anyway within the VLOOKUP formula I was trying to use your technique above to create the index array (as I needed something like {9,10,11,...19,20} to specify the columns I wanted to use, which will change. However I got an error 'Can't perform the function sum on values that are not numbers'. Any pointers, or is it just the wrong tool to do this? Many thanks
Apologies, I ended up answering my own question above with some further experimentation. All I needed was ArrayFormula(SEQUENCE(1,12,9). You don't need the JOIN to put the commas in or the concatenation of the {} brackets. Much simpler!
👍
Hi. Is it possible to use this formula, but I want to skip blank data on an included column?
WHERE Col4 is not null
Do you know if it's possible to do QUERY not with columns but with rows? I can't make it :(
Is there a way to query multiple sheets (same worksheet but different tabs) where I query different columns in different tabs? The data I'm using is autopopulated and the "Impressions" tabs for each are in different columns. So in one tab it might be column B but in another tab it is Column F (as an example). I know how to query from multiple tabs in a range but not when there data is in different columns. I currently use =QUERY({'Sheet1'!A2:C;'Sheet2'!A2:C;'Sheet3'!A2:C}, "select * where Col1 is not null",0) but instead of having to format each tab is there a way to just select which columns I want to pull per sheet?
Sure, it's the same exact formula, just change the column to whatever column you want.
Hello Sir...can you make a tutorial on how to import data (in google sheets) from webpages which require login id and password..thank you
I need a help ,. I need to copy a range from sheet1 and paste to last empty row in sheet2 in google sheet . Please help
Awesome :D
How to get the file. I wanna practice it.....Please can have this.
What if i'm trying to bring a lot of Columns, but I don't want to bring it any null cels (ex: =QUERY({'TEXT!A2:AB;'NUMBERS2!A3:AB};"SELECT Col3, Col9, Col10, Col11, Col12, Col13, Col14"...) If I put just "WHERE IS NOT NULL" or "WHERE Col3, Col9, Col10... IS NOT NULL" it doesn't work.
Can someone give me a hand here?
veerry fuckin coool!! master!!, could you please make a video with querys that search by separated comma words and other cell DISCARDING comma sepparated words from other column?
Thank so much you by the way!. I already learned html, css, javascript and jquery. I have encouraged myself to search and learn all that knowledge on my own being motivated for all your videos dude! Is really awesome that can you share this videoss!! Thankyou again we'll see you in other videos.
Tell me, how can I make a similar request, only through the API to get an answer in JSON format? You do not have a video on this topic?
Maybe check this on my other channel ruclips.net/p/PLRmEk9smitaVGAAhgU0Pdc2sEs7yxDrEk
I tried to run this script so that I can rearrange the sheets but nothing is happening and I keep getting this error
“ Exception: Cannot call SpreadsheetApp.getUi() from this context. “
// global
var app = SpreadsheetApp.getUi();
var ss = SpreadsheetApp.getActiveSpreadsheet();
// create menu entry in spreadsheet upon opening
function onOpen() {
app.createMenu('Move Sheet')
.addItem('Move Active To Right Of', 'moveactiveSheet')
.addToUi();
}
// move active sheet to position zero
function moveactiveSheet() {
var name = app.prompt('Move active sheet to the right of...', 'Insert name of sheet after which to move the active sheet', app.ButtonSet.OK_CANCEL)
.getResponseText();
var foundit = null;
sheets = ss.getSheets();
for (i=0; i
Try to replace variable app with SpreadsheetApp.getUi() and remove the line var app = SpreadsheetApp.getUi();
@@ExcelGoogleSheets thank you I got it to work. What do I have to change so it goes to the left instead of the right
ss.moveActiveSheet(0);
Hi, does anyone know how to replace AAPL with a cell reference in google sheets? www.stockcharts.com/h-sc/ui?s=AAPL
ruclips.net/video/kRnntnyTgFU/видео.html
="www.stockcharts.com/h-sc/ui?s="&A1
@@ExcelGoogleSheets Thanks a million!
Awesome :D