Filter By Email: Try it for yourself!

By now you probably know how much I love the Filter by Email feature of Data Studio, which was released in February 2020. This feature allows us to create Data Studio reports which ONLY display specific data based on the logged-in user. I realized, however, that I hadn't really posted any concrete "how-to" documents illustrating how it works - mostly because any example would need to be customized to include a user's email address in order for that user to see how it works.

So, I've put together an example data set and example report, with instructions on how to copy these and modify them so you can try this feature out for yourself! For this example, we will use some student reading log data (submitted via Google Form), and use it to create a reading log that only displays data for the logged in user.

Instructions:

1. Make your own copy of the example data set (and name it so that you know that this copy is yours!) This data set contains student reading log data for all students in a single class.

2. Check out the sample report and note that it currently displays all of the books read by the entire class (82 books). 

3. Make a copy of the report by clicking the Copy button in the upper right corner. 

4. The "Copy this report" dialog box will appear. This is where you'll connect your copy of the data to make your own copy of the report. To do this, under "New Data Source." select the drop-down menu and select "Create New Data Source."

5. Select the Google Sheets option, then select your copy of the example data set (see screen shot below), and click Connect in the upper right corner of your screen.

6. On the next screen, you will see a list of all the fields from your Sheet that are available for your report. Select "Filter by Email," check off "Filter data by viewer email," and click the button that says "Select Email Field." 

7. Choose Email as the field you would like to use and click Done and then Close in the upper right corner.

NOTE: If, when you click "Select Email Field," in Step 6, you are not able to choose Email as the desired field, then uncheck "Filter data by Viewer Email" and click Done and then Close in the upper right corner. Then, from the Resource menu, choose "Manage Added Data Sources," and Edit the data source to return to the screen for Step 6 and try again.

8. When asked whether you'd like to grant consent to access your email address, choose Allow. This step is what permits the report to use your logged-in email address to display specific data.

9. Click the View button in the upper right corner of the report, and note that the report contains "No Data." This is because you are logged into your own Google account, but the sample data set does not contain your email address anywhere in the Email field. Let's change that!

10. Open your copy of the data source Sheet. Locate all rows that contain Lana Mulherin's reading log information (they are colored yellow so you can find them easily.) Replace Lana's email address with your own in all 8 places. If you want to test this with an additional Google account, replace Sergio Morton's (blue) email address with a different email address.

11. Go back to the Data Studio report (which should still be in View mode) and click the refresh data button (round arrow) in the upper right corner of the screen. The report should now display the data for only the rows where your email address appears (Lana's list).

12. If you like, share the report with your second Google account (as view only) and see how the list that appears is different (Sergio's list.) 

13. Note that the report viewer does not need to be granted access to the spreadsheet, only the report. If the report viewer's email address is not contained in the Sheet, the report viewer will see no data. However, I would still recommend sharing the report with specific Google accounts (and/or just a domain) rather than "anyone with the link can view." Also, before you ask, the feature does not support using multiple email addresses in a cell.

I am sure you can think of lots of ways this can be used in K-12 education, particularly with Sheets that results from a Google Form where the user's email address is captured automatically. This example uses student email addresses, but you could also use teacher emails in order to only share data with a single teacher! Please let me know what you come up with.

Resources:

• Data Studio Help: Filter by email address
• Ideas for Reports using Filter by Email (in the K-12 Data Studio Report Collection)
• Remote Learning Project Log (Laura Tilton)
• Student Check-In Dashboard (Matt Heusman)
• Mehdi Oudjida's Analytics Blog post
• Michael Howe-Ely's Blog Post

Custom Bookmark Links in Data Studio Reports

When I first started using Data Studio a few years ago, the idea of creating a single report that could deliver different information to different users was really just a pipe dream. Since then, there have been some fantastic approaches developed through the use of BigQuery and more recently through the Filter by Email feature built into Data Studio. 

One downside to both of these approaches is that the data source needs to list one user per row of data. So, if I want two people to have access to a particular set of data, I have to double the number of rows of data....three people, triple...and so on. Not ideal if I want to make a subset of my data accessible to multiple people.

One useful feature of Data Studio is found in under File > Report Settings, where there is an option to "Enable viewer filters in report link" in order to create custom bookmark links. Essentially what checking this box does is modify the report's URL so that it reflects any filter controls that are selected. When the modified URL is sent to a viewer, the viewer sees the report with the selected filters in place.

It's a great way to share a specific report view with a targeted group of people. I've enabled custom bookmark links in my Massachusetts Cities and Towns report as an example. This report allows us to choose any town in Massachusetts to see information including its location on Google Maps and town seal. 

The "main" URL of this report is as follows:

https://datastudio.google.com/u/0/reporting/597b2d3d-3f91-4870-8940-beb747027d5c/page/M7pPB

However, if we choose Plymouth from the list of towns, the URL changes:

https://datastudio.google.com/u/0/reporting/597b2d3d-3f91-4870-8940-beb747027d5c/page/M7pPB?params=%7B%22df4%22:%22include%25EE%2580%25800%25EE%2580%2580IN%25EE%2580%2580Plymouth%22%7D

That's an awful lot of gobbledygook, but you can see Plymouth now appears in the URL. If I sent that link to a colleague in Plymouth, it would take them directly to the Plymouth-selected page. 

Now, imagine that you had a report which contained data for a number of schools in your district, and you wanted to create a link specific to each school to send to that school's principal. You could enable custom bookmark links which would then allow you to create a link specific to each school.

However, if you didn't want one school to have the ability to access another school's data, it gets a little more complicated. There would have to be a filter control on the original report allowing you (the report author) to choose the school in order to create the custom report link. You could make that filter control inaccessible by either moving it off the report canvas OR you could put a rectangle over the top so that it couldn't be clicked and changed. (You would do those things after you had generated all the school-specific URLs.)

But not being able to access the filter control still doesn't totally solve the issue. A savvy user might notice that the URL contains the name of the school (the way Plymouth appears in the URL above) and could still change it within the URL to get different data. (You can try it above by copying and pasting the Plymouth URL and substituting "Plymouth" with another Massachusetts town name.)

There's a way around this, if you set up the report to pass "secret codes" instead of filter values, and set up a default value to avoid URL tampering, both of which are explained further in this article. I set up a "proof of concept" report here if you'd like to take a look. Although I am not explaining the nuances of this advanced approach in this blog post, I hope these resources are helpful to anyone who wants to explore this concept further. I'm happy to share more info, just ask!

Resources:

• Google Data Studio Help: Create custom bookmark links with report settings
• Stephane Hamel's article on advanced bookmark link approaches

Simple Row-Level Display Control

We've seen a number of Data Studio report examples that display information that might be collected via a Google Form. But what if you don't want the Form information to display in your Data Studio report immediately? What if it needs to be "approved" first, or what if you want to be able to show/hide different entries from your data source at different times?

Some examples of reports that could benefit from this functionality might be:

• a staff directory report (might want to show only current staff but keep other names in the data source)
• a digital tools learning organizer (where staff are submitting ideas via Form but the instructional technology lead is "approving" them for inclusion in a shared list)
• a remote learning project sharing report (where students are submitting images via Form but the teacher needs to mark them "approved" in order to include them in a public listing)

Fortunately, there's an easy solution for this - built-in report filters! As I outlined in my Fun with Filters blog entry, built-in filters (not filter controls) are a way to restrict the data appearing within a report or report element on the "back end."

I'll use the Staff Directory report as an example. To control which entries from my Sheet data source are displayed in my report, I needed to do two things.

Step 1: I added a column called "Include?" to my Google Sheet data source, and I marked the rows I want to include with "y". In this case, I didn't include a "y" next to Al Banks, Director of Support, although his data is still included in my source Sheet.

Step 2: I added a filter to the report to specify that only rows where "Include?" is equal to "y" should be included in my report. I did this from File > Report Settings > Add a Filter, which will put the filter on the entire report (alternatively, the filter could instead be added to individual report elements like tables and filter controls.)



You'll notice that now the report does not include Al Banks, Director of Support, even though his information is included in the data source, since this filter has been applied. If I wanted to include him in the list, I would simply add a "y" in the "Include?" column in the data source, and Voila! He'll be back in the report.

This is a very simple, but very useful, way to control the data that is displayed in a report at the row level on the back end. BTW, I use this simple approach for the entries that are included in the K-12 Data Studio Report Collection, which allows me to approve form-submitted entries before they are included.