Question

A finance analyst builds a Connected Sheet in Google Sheets that queries a large BigQuery revenue table and shares the spreadsheet with 30 regional managers who need to refresh pivot tables and charts themselves. During review, the managers report errors when they click 'Refresh' even though they can open the spreadsheet. The BigQuery table sits in a project where the managers have no IAM roles. What is the correct way to enable the managers to refresh the Connected Sheet data?

Accepted Answer

Grant each regional manager at minimum the BigQuery Data Viewer and BigQuery Job User roles (or equivalent) on the project/dataset containing the source table, in addition to sharing the spreadsheet.

Answer

Share the spreadsheet with 'Editor' permission instead of 'Viewer' so Sheets propagates the analyst's BigQuery credentials to the managers on refresh.

Answer

Export the BigQuery table to a Google Sheet as a static import so no BigQuery permissions are required for refresh.

Answer

Enable BigQuery BI Engine on the dataset, which automatically authorizes any user who can open a Connected Sheet to run refresh queries.