Google Data Studio Community Connector - Hire a consultant

[urwa shabir]

Hi,

I am looking to solve a couple of issues in Google Data Studio Community Connector that uses BigQuery as datasource with row-level sharing of reports. Please let me know if you are an expert in this and any past experience with this. Looking to hire on hourly basis.

Thanks.

[Dimu Designs]

I wouldn't call myself an expert but I'm fairly adept with the technologies underlying Community Connectors and Big Query (ie. Apps Script and SQL respectively). What exactly are you trying to accomplish?

[urwa shabir]

Hi,

Thank you for your reaching out. Are you aware of row-level sharing for reports? We have two sets of people: admin(see everything), non-admin (limited view based on email of logged in user.)

Here is the scenario:

ISSUE 1:
I have a community connector for BigQuery for my reports. I am sharing the reports to several clients who have to authorize the connector. How can I ensure my clients are not able to view the apps script for the connector since it contains confidential information of my sql instance.

ISSUE 2:

I have one column/dimension that is being used for calculated fields in the data studio. Admin can see  this column but I do not want non-admin users to see the column at all. But since schema for dimensions and metrics is defined before the data is loaded using getSchema() I am not sure how to 'hide' it. Even if the dimension is not showing up in the report I share. There is a chance that user (non-admin) creates a new report using the community connector and uses this dimension and see the data.

Thanks,

U. Shabbir

--
You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/W2AjuHItP1o/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/a15b55c4-5ee4-43c1-95b9-af7c188a888a%40googlegroups.com.

[Dimu Designs]

Not exactly clear on what you mean by "row-level sharing", but it sounds a lot like Role-Based Access Control where depending on role (admin, non-admin, etc) a user is granted varying levels of access to content stored in a database. So with that as my frame of reference lets proceed to your issues.

 

ISSUE 1:
I have a community connector for BigQuery for my reports. I am sharing the reports to several clients who have to authorize the connector. How can I ensure my clients are not able to view the apps script for the connector since it contains confidential information of my sql instance.

You can store your database credentials using PropertiesService (script level, not user). You should only grant your clients read access to the shared script (ie. select "can view" when you share the script) so they won't be able to see or directly access what's stored. This is considered best practice.

ISSUE 2:
I have one column/dimension that is being used for calculated fields in the data studio. Admin can see  this column but I do not want non-admin users to see the column at all. But since schema for dimensions and metrics is defined before the data is loaded using getSchema() I am not sure how to 'hide' it. Even if the dimension is not showing up in the report I share. There is a chance that user (non-admin) creates a new report using the community connector and uses this dimension and see the data.

Now this is a bit tricky, even at the database level (assuming Cloud SQL/MySQL) the most fine-grained privileges you can set are for operations acting on tables as a whole (I don't think you can gate access to a specific column or row). But there are a few things that can probably be done to filter data based on user role (admin, non-admin). I could augment your connector with the necessary functionality....for a price. Do you have a client account with Guru.com?

[urwa shabir]

Hi Dimu,

Thank you for your response. Let me consult my team and get back to you if needed.

Thanks once again,

U. Shabbir

On Mon, Jan 27, 2020 at 2:29 AM Dimu Designs <dimud...@gmail.com> wrote:

Not exactly clear on what you mean by "row-level sharing", but it sounds a lot like Role-Based Access Control where depending on role (admin, non-admin, etc) a user is granted varying levels of access to content. So with that as my frame of reference lets proceed to your issues.

 

ISSUE 1:
I have a community connector for BigQuery for my reports. I am sharing the reports to several clients who have to authorize the connector. How can I ensure my clients are not able to view the apps script for the connector since it contains confidential information of my sql instance.

You can store your database credentials using PropertiesService (script level, not user). You should only grant your clients read access to the shared script (ie. select "can view" when you share the script) so they won't be able to see what's stored. This is considered best practice.

ISSUE 2:
I have one column/dimension that is being used for calculated fields in the data studio. Admin can see  this column but I do not want non-admin users to see the column at all. But since schema for dimensions and metrics is defined before the data is loaded using getSchema() I am not sure how to 'hide' it. Even if the dimension is not showing up in the report I share. There is a chance that user (non-admin) creates a new report using the community connector and uses this dimension and see the data.

Now this is a bit tricky, even at the database level (assuming Cloud SQL) the most fine-grained privileges you can set are at the table level (I don't think you can gate access to a specific column). There are a few things that can probably be done to filter data based on user role (admin, non-admin). I could augment you connector with the necessary functionality....for a price. Do you have an account with Guru.com?

--
You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/W2AjuHItP1o/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/31b6ae7a-d241-4203-b620-d271ea31bb67%40googlegroups.com.

[Dimu Designs]

So I did a little digging and found this:

https://developers.google.com/datastudio/solution/row-level-filtering

It looks like the above will solve your issue.