Query Function Examples in Google Spreadsheets

hello everybody my name is Cameron crest today I'm going to show you some examples of the query function in a Google spreadsheet so a lot of people ask me what is this query function within google spreadsheets and how can it do all this stuff that you say it can do so I'm gonna show you what you're looking at right here is a spreadsheet which I'm gonna be making available publicly at the end of this video you'll be able to click a link and actually open this spreadsheet if you'd like when you're done this and the other spreadsheet that has the queries they can be duplicated into your own Google account so you can play around with them by going to file make a copy and it will make an editable copy of the spreadsheet for yourself so and then you guys can have fun with these and try to figure out how they're working but as you can see here I try to make this a little bit fun this is dummy data this is the source spreadsheet this is where all of our data is going to be pulled from using the query function within google spreadsheets if you haven't seen this video I thoroughly recommend it it's right here it's a public service announcement about talking to your children about Star Wars great anyway let's just examine our data really quick here we've got date hours project tasks notes person and as you can see these are characters from Star Wars notes about different things that they did on tasks for specific projects hours they worked on what days so these are this is a bunch of timesheet data kind of all jumbled together gathered from from different time sheets and such I have a query that will generate this from 16 different spreadsheets but that's not what we're gonna do here we're just going to take this aggregated information that's listed in one spreadsheet we're gonna go over to another spreadsheet this one's called the query source spreadsheet we're going to go over to another one right now and this one is called the query example spreadsheet so the first query I'm going to show you here and here it is in cell number a for this this query is generating everything you see on the page here from row four down so without this query I'm just gonna give you an example I'm going to delete that and you see that there's nothing here okay so the query formula will just do a ctrl Z to get that back okay the query formula is right here and it I'm not going to explain this entirely there's there's documentation on this but I'm using another function within the query function called import range this is necessary if you're gonna grab data outside of a spreadsheet if you're grabbing data within a spreadsheet you can just put in your range a 1/2 a whatever but if you're grabbing something outside of a spreadsheet you need to use import range import range works with the key of another spreadsheet which is right here you can find the key I'm going to go back to the source Virginian you can find the key right up here where it says key equals that's your key you just copy that over place it into the first part of the import range and then you need the sheet and the range sheet is always followed by an exclamation if you have a space in your sheet mean you need to put single quotes on either side of it and then the exclamation after so so that is the range section right there and I'm using import range then you're going to have your actual query okay queries are very sequel like okay if you don't know sequel that's fine you don't have to know sequel but they're written very similarly instead of referencing tables like you do in sequel you reference column needs and in the case of going outside of the spreadsheet to another spreadsheet you use Co l 1-2-3 and what you're referencing here in this source data is the number of the column column one two three four five six I put the numbers here so I don't lose my sanity when I'm writing a query you don't have to do that and your source data if you don't want to if you are not referencing an outside spreadsheet and instead you're just pulling from the same spreadsheet but say another sheet down here you would you would reference the the column letter so you would instead of call one you would just put and you have to use capitals and a so you're selecting a okay within this range over here that you've specified so so this is this is a very simple query on this page that that basically just pulls all the data from the source so when you're all done here you see the exact same thing as you did on the source source you yeah it's just the same thing so this is I'm calling this a clone query I made that up really has nothing to do with Star Wars it just kind of worked out I guess and the last part of the query that I want to mention is this over here this is this is the number of header rows that are in your source data so we're grabbing everything in the source from a3 all the way down to F I think two thousand or a hundred thousand or whatever and we're telling it here at the end that there is one header row so that just tells the function to take the first row and not you know make it part of the equation just make it display headers you know for that first row of the information here's a much simpler version of how to look at the query and as I said there's all kinds of documentation about these these queries within Google but not very many explanations which is what I'm trying to do with this video here so let me move on you're gonna have a lot of fun with this so this is a clone query simple one here's a filtered query it's another query just like the one we looked at except for all I did is in the query part itself I spy a data where clause here where column four contains support and column two is greater than three so in essence what this or what this query is doing is it's it's pulling from that big list anyone who worked on a support task longer than three hours so you you can put a where clause within your your query here and you can limit the amount of data that it pulls back it's pretty cool all right let's move on so this is a person summary query it's the same query but what I'm doing here is I am listing out every single person and the sum of the hours that they worked okay so same data we're just having the query actually do some work it's it's doing some summing and you can you can see that query right there it's pretty small select column six which is the name and then some column two which happens to be the hours and then group by column six all right here next one daily summary query this is the same thing but it's got a little more specific data in it it's it's grouped by day so the granularity has gotten a little bit smaller what we're looking at here is we're grabbing the person the date okay summing the hours and then we're grouping it by the date then the person so we go backwards we go date person on the group by and then we're ordering it by column six which is the person so what you get is a list of each person and the amount of hours that they worked on specific days so if they worked more than once like if Han Solo work worked a couple of different shifts or whatever they'd be added up here you know that's pretty cool now this next one this next example is it's really a testament to the power of of this function you see a list of people here and you've got their total hours by day and the dates are listed out across the top here okay so the way you accomplish this is in your query your select statement here you actually you select the person you so you sum the date you group by the person and then you pivot and it's right there you pivot column 1 column 1 is the date when you pivot a column puts it across the top and it makes multiple listings based on the number of unique items within that column and if you wanted to you could wrap this column one in a month function and it would it would take that and it would just list the total by month you know there's a lot of different things you can do with this um and then of course you're ordering it by the name but then I went ahead and quit descending at the end here so now you can see it's in Reverse off about a quarter Admiral Ackbar's at the bottom it's pretty cool so that's another example okay the last example I want to show you is it's pretty simple it's um well simple to me but it it involves incorporating other functions in Google Spreadsheets alongside the query function to come up with what's what I call an ad-hoc query report a lot of the time someone will come to you and they'll ask you to create a report and at the end of the day when when you're done they'll usually nine out of ten times okay thank you very much now show me something else or the answer is usually you know this is exactly what I asked for but it's not what I want so you end up you know going back and forth if you if you work in reporting you know what I'm talking about and it's just kind of the nature of the game so okay so I've put together a little report here that allows the user to come in and choose from a drop-down what information they want to see so for instance I'm going to select all here and it's going to show me everybody who entered timesheet data that we're working on the task of requisition from this state going forward so each one of these is a drop-down so I know a lot of people worked on test or testing so I'll just switch to that and sure enough I'll get all these people to do testing on all these different days that's that's pretty cool well let's let's narrow all them down to people that worked on testing from the eighth go going forward and it just updates as you go through here and let's look at on the 12th here Wow you know so this is this is a very dynamic report and it allows people using it to you know kind of look at their data in different ways and there's a number of different ways that you can customize something like this to interact with a query function and I just clicked on the function here and you're gonna see that this this is a big query function and it's very complex and I'll give you a hint you guys can try to figure out exactly what it's doing but it does work with dates so there's some in from in interesting syntax involved when you're working with dates within query functions and you can do a start and an end date that you can choose at the top and it would you know structure your query accordingly it also it also wraps everything it's actually two queries and they're wrapped in an if statement that's as much as I'm going to tell you but as I said all of this and and all the formulas are going to be available at the end here one last thing I'm going to say I'm I've made lists of the persons the tasks and the days which are all contained over here in the clone query okay and I'll give you another hint all of these are using special functions one of them is called unique and one of them is called sort and they're nested and they're dynamically generating these lists for me and I've applied data validation to these cells up here I colored them white so that it kind of draws the users attention to them when you click on them you get a drop-down that is accomplished through data validation built-in feature just go to data validation and you can see that my criteria is I'm getting items from a list and the list can be determined by clicking here and you just select what it is you want to show up make sure you have this click show list items in a drop-down menu so anyway I'm just gonna let you guys have some fun with this you're welcome to email me emails up in the the top right here hopefully I don't get flooded but there's a lot more you can do with this function and other functions which you can only find in Google Spreadsheets have fun and have a great day you

Channel: Cameron Crest

Views: 129,632

Rating: 4.8397932 out of 5

Keywords: Google Query Function, Query Function, Google Spreadsheets, Google Docs (Software)

Id: R3DIVDNEslc

Length: 15min 30sec (930 seconds)

Published: Wed Mar 14 2012