Before tonight’s turkey coma kicks in, I wanted to revisit the QUERY function from last post. We did a few neat things with survey response data and creating automatically-updating sub-sheets from a master. That’s all well and good, but I promised we’d go a level deeper. I promised IMPORTRANGE.
We’ve already defined QUERY as:
So what about IMPORTRANGE makes QUERY even better? You can find the official help article from Google here.
IMPORTRANGE Syntax
IMPORTRANGE lets you connect data from one Google Sheet to another. It’s a one-way connection, and that ties in fantastically with our automatically-updating sub-sheets. The power with IMPORTRANGE is that you only need View access on the source Sheet in order to pull the data into your own file.
Formal Syntax: =IMPORTRANGE(spreadsheet_url, range_string)
- spreadsheet_url: the Drive link to the source spreadsheet
- range_string: the range of data written as a string in the standard reference format of Sheet_Name!Range (e.g., Sheet1!A1:M1000)
Not too bad, right? All in all, IMPORTRANGE could be classified as neat:
Separately, QUERY and IMPORTRANGE are both fantastic functions. So what happens when we combine them? In short:
Let’s break it down with an example. Specifically, let’s break it down with the example that prompted me to write about QUERY in the first place.
Securing, yet sharing
Working with data about students, faculty, staff, etc. means being conscious of who all has access. Working with data about minors coming to campus for programs? Let’s take access restrictions up about fifty notches.
The question brought to me a couple weeks back involved this kind of data. High school students from all across the world would be on campus for a week-long program. Nothing too unusual there. The breakdown in this particular case was that some of the student data (like their names) needed to be shared with group leaders.
Legally, these leaders can’t see any data from the other groups, and this meant there was all too much copy/paste going on to create individual spreadsheets for each group lead. Whenever information about a student changed, the master sheet needed to be updated as well as the group leader sheet. Doing that a couple times? Okay. Doing that 50 times a day for two months? No thanks.
Enter IMPORTRANGE and QUERY. By combining these two, we can connect the group lead sheet to the master sheet and filter the data to only display what the lead is allowed to see. Supersize it by creating a template sheet that pulls the group lead name from a cell and now things are starting to heat up.
Master Sheet
Available here, we have a sheet that collects the application number, student names, week they’re attending, dues, and whether or not the permission slips are in order. Add in a little data validation (particularly for the Group Lead Name) and things look good.
Group Lead Sheet
Let’s set this up. We want to provide the group lead with the student names, the week they’re attending, the amount they owe, and whether or not their permission slip is in. On top of that, we only want to display the data for their group.
Downside: this means creating a new file for each group lead.
Upside: we can share out the sheet with the group lead and never have to touch it again.
Our sample sheet is available here with the function in cell A9.
Said function: =SORT(QUERY(IMPORTRANGE(“1Q7vcphWwMnwuXUQCyRQVr6rd5J7HUv-Y7s-TesQMhRM”,”Students!A2:J1000″),”select Col3,Col2,Col4,Col8,Col10 where Col5 = ‘”&C1&”‘”,0),3,TRUE)
Hoo boy. Take a deep breath and let’s break it down.
IMPORTRANGE(“1Q7vcphWwMnwuXUQCyRQVr6rd5J7HUv-Y7s-TesQMhRM”,”Students!A2:J1000″) connects to the master sheet with a nice big range that will accommodate more students joining the program later on.
QUERY(…, “select Col3,Col2,Col4,Col8,Col10 where Col5 = ‘”&C1&”‘”,0) starts getting fancy. Normally, we reference columns by their letter. When we’re referencing an imported range, we instead need to go by column number in the range. So instead of C for our student last name we use Col3.
Keeping it fancy, we don’t put the group lead’s name in the query statement. Instead, we break the statement up using ampersands to reference a cell. While it seems like overkill here, if we have to make 10, 15, 100 of these group lead sheets, it’s going to be easier to File > Make a copy and change a cell each time versus editing the function.
If you’re breaking up the query statement like this, don’t forget to include the string apostrophes before and after: …'”&cell&”‘”
SORT(…,3,TRUE) takes our imported, queried data and groups it based on which week students are attending. A quick look at the SORT function:
Formal Syntax: =SORT(range, sort_column, is_ascending, [sort_column2, is_ascending2, …])
- range: data to sort; in this case, the imported, queried data
- sort_column: of the range, which column you want to sort by first; in this case, the column for Week Attending
- is_ascending: TRUE for A-Z sorting, FALSE for Z-A
If we wanted, we could add another two arguments to our SORT function that would alphabetize the list of students each week: SORT(…,3,TRUE,1,TRUE)
We’ll save that for another time. You can see more about SORT in Google’s help article here.
Phew.
We made it.
Give yourself a pat on the back. We’ve combined two of Google Sheets’ best functions to create a system that allows for a singular master sheet that populates a host of other sheets that can be shared without worry. That’s no small task.
Now that I’ve got QUERY out of my system, what else would you like to see? Let me know in the comments, with an email, or on Twitter. See you next post!
Cheers,
Annie
Annie Sauer 