CODEWITHSUNDEEP
google-sheetsgoogle-bigquery
Edward Wu
Edward Wu

Reputation: 485

BigQuery Google Sheets Connected Sheets pivot table permissions

Short summary: I'm trying to give a user access to edit a Pivot Table created from a BigQuery Connected Sheets connection and running into problems. When the user tries to access the Pivot Table, all of the Pivot Table controls are grayed out.

Here's my BigQuery setup:

Project: sales_reports

Dataset: all_sales This is for sales for the entire company

Table: salesWithProductDetails This contains sales data for the entire company

Dataset: dept_x_sales This is sales for dept_x

Table: dept_x_sales
This is a Table created via a query on all_sales.
  e.g. SELECT * FROM `sales_reports.all_sales.salesWithProductDetails` WHERE STARTS_WITH(customer_no, "W")

    View: dept_x_sales_last2Years
e.g. SELECT * FROM `sales_reports.dept_x_sales WHERE fiscal_year >= extract(year from current_date) - 1

Next, I have a Google Sheet, with a Connected Sheets connection to the dept_x_sales_last2Years And I created a Pivot Table from it. It works great for me.

But I want to give someone else (let's say "Bob") access so:

  1. They can adjust the Pivot Table as they see fit so they can do sales analysis
  2. They only have access to the dept_x_sales_last2Years raw data

I've tried following the examples on Google's docs on creating authorized views but it's not working.

I've done the following:

  1. Gone into IAM and given Bob a BigQuery User role for the Project.
  2. In IAM, given Bob a BigQuery Job User role for the Project.
  3. In BigQuery and given Bob a BigQuery Data Viewer role for the Dataset.
  4. In BQ and made the dept_x_sales_last2Years an Authorized View.

If I'm using the Sheet as "Bob", the Pivot Table selectors are all grayed out. And if I try to go to the Connect Sheets tab and even do a Refresh preview, I get an error "You do not have access to the connected BigQuery table."

I've also tried:

  1. In BQ, made Bob a BigQuery User for the dept_x_sales Dataset
  2. In BQ, made Bob a Project Editor
  3. In BQ, made Bob a BigQuery Admin for the dept_x_sales Dataset

What am I missing?

Upvotes: 1

Views: 1872

Answers (1)

Abkarino
Abkarino

Reputation: 1446

The requirements fit the Authorized Views option.

An authorized view allows you to share query results with particular users and groups without giving them access to the underlying source data. You can also use the view's SQL query to restrict the columns (fields) the users are able to query.

This involves creating a new dataset and adding a view to that dataset that references the original dataset.

  1. You assign Bob the project-level bigquery.user role which does not give them the ability to view or query table data in the dataset containing the tables queried by the view. But allows them to run jobs.

  2. You give Bob bigquery.dataViewer on the dataset with the authorized view.

  3. Lastly, you authorize the view to access the source dataset.

Steps are from a tutorial by Google.

Upvotes: 2

Related Questions