QUERY & MYSELECT functions, Select by Column Names (Headers, Labels) - Google Sheets
Вставка
- Опубліковано 7 вер 2024
- Learn how to create MYSELECT function and use with QUERY function in Google Sheets to achieve a select statement where you can use column names (headers, labels) instead of regular A, B, C or Col1, Col2, Col3 syntax.
#QUERY #GoogleSheets #advanced
i have no words how stunned I am about your skills, your approaches to solve trouble-shootings and YOUR skill to teach. thank you SIR!!!
This has save me hours of breaking my head matching columns, thanks a million!! I've been following your videos for a bit now and the work you do is really invaluable. I'm looking at referencing cells within this Myselect function but have not found the way. Perhaps you have shared this in another of your videos?
Wow! Mind-bogglingly amazing. Your depth of knowledge is incredible! Thank you.
Thanks for this one. Very useful indeed and solves a problem that wasn't easy to fix until Named Functions arrived in Sheets recently. Your videos are amazing. To the point, showing all the steps you take to understand and debug what's going on and making them available to all! I've probably learned more from following your train of thought in your channel than from any other resource :)
👍
Oh my... great idea, but in my case, I would use an object that I have in a script file (with my tables description and locations in the sheets) to generate the desired SQL columns.
This would save me a lot of time configuring named ranges (and their changes) in each spreadsheet that I have (I have a CRM/ERP/Payment system with Sheets!).
Thanks, best classes ever!
Thanks for making my life easier.
This video is The best Spreadsheet Tutorial of all time.
🙏
Cleanest solution I've found for this. Thanks!
Cool!
Your knowledge is other level. Dude you are a master. Congrats.
👍
This is the best channel ever
Excellent video
Excellent 👍👍👍👍
Excellent and very clear
Thank you 4 another great tutorial, ican't figure out where do you got this ideas and how you develop them. By the way i appreciate very much the way to do things on the scrap, that's is called learning by doing. My point of view, the best way to learn, is learning from mystakes. Thank's again mr K.
You are very generous for sharing all this incredible knowledge! Question, is there any way instead of writing the header [Date], I could refer to cell where the user would type which column they want to show? It seems something everybody would need but my brain can't think of how to solve it. Something like =QUERY({range}, "Select Col1, Col5, Col"&A1&" where not Col5 contain 'Blabla' group by etc). A1 would be the column I need to show but is variable . So when the user picks Jan in a dropdown in the cel A2 for example, a simple match in A1 finds which column we need example X, so the query would become =QUERY({range}, "Select Col1, Col5, ColX where not Col5 contain 'Blabla' group by etc) . It would be amazing to be able to have a conditional to select columns as we have for rows with "where". Thanks!
What you have should work if you add a MATCH function in A1 to find the position of your column you select. For example, if you select your dropdown in A2 and your headers are in A4:G4, then A1 would be =XMATCH(A2,A4:G4)
Great learning...
Excellent video as always. Keep up the good work. Thanks.
You are my heroe. Thanks a lot!
Game changer!
Hi In Minh, your fan from Vietnam, im learning a lot from you with googlesheet, google data studio especially query function. I would hope to switch my career to work as OA system (A kind of CRM system) dealing with work flow and data. Do you have any idea which skillset or language or course i should take? Thank you
Thanks a lot. Eline sağlık in Turkish :)
Interesting, but very annoying not to have the code in commentary
I was just working on the same issue, glad you have a video guide made. I wonder if this works the same if the data source is from another worksheet?
Why wouldn't it?
It Works like a charn!! Thank you!
Could you plz make a video about how to sync Google contacts with some sheet ..
thank you so much for those videos ..ammmazing
Super!!! Thanks!!!
Brilliant!
I took some ideas from your video and implemented the same but with named functions (4 of them, in fact). No scripting. My select's accept "having" clause and simplify labels managment. Should anyone be interested: any suggestions about exchanging contact data without publishing them?
another way, loop thru header fields, create header.field="Col"+columnNUMBER. loop thru header query=query.replace(field,header.field)
Hi, great content always... Is there any workaround for building indexes too in Google sheets for faster querying??? Thnx for help... Really appreciate ur selfless work... ✨
I am trying to replicate this scrip but I get this error in the line 13 TypeError: text.matchALL is not a function (line 13, file "Code")Dismiss
maybe you wrote
let results=text.matchAll(/\[.*?\]/g)
try to
let results = text.matchAll(/\[.*?\]/g)
How would you handle is the column names were dates?
Great video, thanks. Although is not working for me when columns names have () for example column name [Cost (EUR)], otherwise is very useful.
Yea, that would require additional handling or you can use a different qualifier in the code instead of [] to wrap the columns.Maybe {}
I have a question out of the topic, does class image have a function to call the image - what I mean is I have assigned a script to this image but this script is basically not within my google apps script and it comes from add ons and no way to call it from google apps script or any other way but within draw and images only.
Could you please post the script in the description? I have tried rebuilding it, but I am having issues with the "matchAll...."
Hi Sir, How are you? Sir, for couple of days, I've a problem to merge Data from different files. I want to say, - suppose you have 3 clients or 3 folders. "Client A", "Client B" and "Client C". Under each folder or client, there are multiple files. Let's say, "Client A" folder contains 5, "Client B" folder contains 10 and "Client C" folder contains 15 files. The main problem is, the spelling and the sequence of the Headers are same in all the files in the same folder but not in the others.
Let's say for an example, "Client A" folder consists of 5 files which are of same headers like (Emp ID, Emp Name, Age, Ph, State, Sales). Likewise, "Client B" folder has 10 files, which also have the same heading like (Name, ID, Phone, Age, State, Revenue, Zip Code), but not exactly same as "Client A". In the same manner, "Client C" has 15 Files having same headers like (Zip Code, State, Zone, E-Name, E-ID, Sales, Contact No., Age) but not same as headers in "Client A" and "Client B" Folders.
In this situation, how can I combine the whole data and make a pivot on it? I kindly request you to please make a video on this topic Sir.
Thank you!
Hi, not really related to the video but it’s the most recent upload. I have a question, say I want to delete row 2 but I want to keep cell C2 when deleting the row, is there a way to lock that cell when deleting row 2? I’ve tried looking everywhere but all I get are results for setting permissions which doesn’t seem to work. Any help is appreciated!!! Thank you so much.
first of all: ALL my knowledge is from this superb channel; SO DON´T CALL ME A "PRO". ;)) to your topic: if you delete a row: YOU delete it. either you set all the other values in that row (in your case row #2 to "blank" (.set value("")) or you get the value from your C-COLUMN before deleting and set it somewhere else (wherever you want it). hope it helps a bit
fe: if you always want to delete the content in ROW2 but want to keep C2: ws.getRange(2, 1, 1, 2).setValues(""); ws.getRange(2, 4, 1, getLastColumn()-1).setValues("");
@@blockwest2379 thank you!
Thanks
Thank You!
i simply use an auxiliar row avobe the headers and fill the cells with Col1,Col2,Col3,... and can avoid all this:
=query(range_of_table),"select "&query(transpose(range of auxiliar_row_and_headers),"select Col1 where Col2="&header_name_of_the_column_i_want&" ")&" ")
for selecting multiple columns, many continuations can be built from previous function
(i currently use only 1 column for my needs, so i don't have any continuation, yet)
Cool! One question for you: when I use Query aggregate function Sum, the head shows "Sum" in front of the original header name, eg. "Sum Sales". How do I get rid of the word "Sum" in the header? Thanks!
@Aida Aman it works! Thanks so much!
I have very difficult question in which it is possible to be done in Google sheets but I haven't see a single video to do the same thing on Excel online which is combining multiple sheets into a master sheet in Excel online. Plus I don't see query function to put conditions in Excel online. Please make a video on that.
Probably not the most sleek solution, but you could do this ua-cam.com/video/BAdl5_cXWdE/v-deo.html
Thanks a ton.
Thank You!
The Apps Script he is using looks different that what I see from Script Editor and going to projects etc. Is that a more recent update or something? Also, does the "let" allow not having to use semicolons at the end of each row of code?
he uses the "old" IDE. (you can switch to the old version (top-right ==> USE OLD EDITOR)
@@blockwest2379 Thanks!
The lack of semicolons came when Google updated the apps script engine to their "v8" version. That allows a set of new more modern Javascript language features to be used including the extra variable declaration types like "let", and "const", as well as dropping semicolons.
There is a fuller description of the changes here developers.google.com/apps-script/guides/v8-runtime
@@johnrumm4786 Thank you! I'll have to see about upgrading, that doe slook easier to use.
the question is why would you put a [ ] around column names if you are gonna remove it in the script
Hi - I think that's to make it easy for the MYSELECT function to identify column names. The alternative is a full query parser that recognises column names based on understanding the query syntax itself. That's a lot harder.
is there someone tried to copy this and then put him in his Google sheet after that just turn and succeed?
why are you running the old version of google apps script
What if we have columns with the same name?
Which video do I need to search for ~~~~ =QUERY(Data!A1:G, "Select A, C, G where G > 8000", 1) ~~~~~~ But sub __G > 8000__ for __if G says "yes"__?
ALSO THANK YOU SO MUCH FOR THESE VIDEOS
The first video in the series ua-cam.com/play/PLv9Pf9aNgemvAMlqvHP9RhXPW98g_eo7d.html
could not see script editor under tools tab, please help
Under Extensions-> Apps Script now.
Can you share script
brutal