Thursday, 1 May 2014

Interactive reports using QUERY and IMPORTRANGE

Using the formula =IMPORTRANGE in a Google sheet allows you to easily bring through data from other sheets. For some more powerful functions you can combine QUERY and IMPORTRANGE as in the examples below, and make these interactive using dropdowns or values directly entered in a cell

On one sheet I have uploaded some historical data on NIHR Trust performance, published on the Guardian's Clinical Research Zone.  I will then use this spreadsheet as the source to bring in selected data on another target sheet.

In the first example, using data validation, I have put in a dropdown in cell B3 containing all the different trust types.  Using the following formula, I can then return the list of trusts that match exactly the value selected in that cell:

=(QUERY(IMPORTRANGE("15Q9ppH0-Pcdwak0Eyns4NNtMMv0Ft0PeqGf2RDfWot4","A:k"),"SELECT Col1, Col2, Col4 WHERE Col3 =" & "'" & B3 & "'"))




In the next example, I have amended the dropdown to include some shorter keywords, that will match more items.  I have then adjusted the formula, so that it does not look for an 'equals' match, but instead a 'contains'

=(QUERY(IMPORTRANGE("15Q9ppH0-Pcdwak0Eyns4NNtMMv0Ft0PeqGf2RDfWot4","A:k"),"SELECT Col1, Col2, Col4 WHERE Col3 CONTAINS" & "'" & B3 & "'"))



In the next example, I wanted to show all the trusts that have above a certain number of recruiting studies. Instead of a dropdown, I just have a cell that users can type a number in.   The formula for this one is:

=(QUERY(IMPORTRANGE("15Q9ppH0-Pcdwak0Eyns4NNtMMv0Ft0PeqGf2RDfWot4","A:k"),"SELECT Col1, Col2, Col7 WHERE Col7 >"&B3))


In the final example, I wanted to use a SUM function, to calculate the total number of studies for each of the trust types, and apply a custom title/label "Total number of studies" to that column.  The formula for this is

=(QUERY(IMPORTRANGE("15Q9ppH0-Pcdwak0Eyns4NNtMMv0Ft0PeqGf2RDfWot4","A:k"),"SELECT Col3, sum(Col7) where Col3 contains"& "'" & B3 & "'" &"Group by Col3 label sum(Col7) 'Total number of studies' "))




NB - a quirk of the new Google sheets, is that you need to authorise a connection between the source and the target sheet when you do an IMPORTRANGE.   When you use a standard IMPORTRANGE, it gives an error and prompts you to do this as in the screenshot below:
Unless you have already made this connection, using IMPORTRANGE the QUERY(IMPORTRANGE(  function will not work.  To enable this to work for my demo sheet, all I needed to do was a simple IMPORTRANGE formula on another sheet such as:
=importrange("15Q9ppH0-Pcdwak0Eyns4NNtMMv0Ft0PeqGf2RDfWot4","B:B")
Once the connection has been authorised, the other formulae will work as expected.
  

A copy of the sheet with the formulae in can be downloaded here:



5 comments:

  1. Thank you for the NB about the "quirk of the new Google sheets ..."!

    The QUERY-IMPORTRANGE function is a great way to maintain a master "database" sheet and be able to process various queries against it. We are using the technique extensively for our non-profit to keep track of volunteer information.

    I was very concerned when I discovered that it wasn't working in new Google sheets and the error message "Unable to parse query string for function QUERY parameter 2 ..." was misleading. I even opened a ticket with Google support back in May but they did not know that an authorized connection was needed.

    ReplyDelete
  2. Thanks for your post, James!

    Have you had a look at Import Sheet [1]?

    It's a Google Sheets add-on that provides a visual interface to automatise report creation from Google Sheets and MS Excel datasets. You don't need to combine QUERY and IMPORTRANGE as the add-on has in-built tools that do the same and thus hide the complexity from the end user.

    Looking forward to your thoughts.

    -Robert

    [1] http://importsheet.com/install

    ReplyDelete
    Replies
    1. Thanks Robert - this looks like a really good add-on. Appreciate the heads-up

      Delete
    2. I am glad you find it useful, James!

      Maybe you want to create a short introduction to the add-on for your readers?

      Delete
    3. If you want to put a plug for this in the comments, please feel free. Ill keep the article as is though, as explaining how to do this manually is what the post is about. The add-on also has limitations without purchasing the full version, so will not be for everyone...

      Delete