Retrieve Google user and use it as data in Google Data Studio

Question

What I have now: I have a static HTML site where I'm creating a dashboard with Google Data Studio. The spreadsheet connected to Google Data Studio has sensitive data of 100s of different users, each identified by their e-mail address in the organization.

Goal: Display only the rows of a table where a user's e-mail is stated. I want to do it retrieving the user e-mail from session, passing it to Google Data Studio and automatically show rows corresponding to the address.

Code snippet:
- Retrieve the user's e-mail from session:
var email = Session.getActiveUser().getEmail();

But this is all I know. I don't know:
- Wether I have to make a connector, write it on Google Apps scripts then link it or write it in the Spreadsheets Apps Script (makes no sense for me).
- How to send the 'email' retrieved to Google Data Studio.

Any lights on this issue would be really useful. Thanks!

Answer 1

It's not possible to retrieve the active user's email unless there are certain conditions. From the documentation we can read that:

If security policies do not allow access to the user's identity, User.getEmail() returns a blank string.

The circumstances in which the email address is available vary: for example, the user's email address is not available in any context that allows a script to run without that user's authorization...

...these restrictions generally do not apply if the developer runs the script themselves or belongs to the same G Suite domain as the user.

This means you would need to deploy the script as a web app with the option:

Execute the app as: User accessing the web app

Answer 2

Steps:

1. Data Set: [Done] In the the spreadsheet, create a field (e.g. email) which includes an email address on each row.

2. Data Source: At the Data Source (editing an existing or creating a new one) the top left now includes an email filter where the field with the email address (email) can be selected as the field to filter the report by.

Answer 3

Edit: The Filter by email address feature was launched last week. This can be a much simpler solution to your requirements.

Original Answer: Implement Row level filtering with Data Studio. However, instead of using BigQuery as your source of data, fetch the data from the associated Sheet.