Workbooks allow creating reports and dashboards. A single workbook can have multiple dashboards and a dashboard can be based on one or more data sources. The primary source of data is a file on Google Sheets or Google Drive.
Following file types are supported from Google Sheets & Google Drive.
- csv - These files are stored on Google Drive.
- xlsx - These files may be stored on Google Sheets or Google Drive. However, they are downloaded via Google Sheets API.
- Sheets - A Google Sheets document directly stored in Google Sheets.
When there is a choice to store a file on Google Drive as csv or xlsx file, use the former as it is much faster to download them.
When working with a workbook, either to edit or view data, users are asked to sign-in with their Google account (work account) and give permission to read files from Google Drive. Only files used by the Workbook are downloaded. Data is only downloaded into the browser while working with the workbook.
Permission to read the Google Drive is valid for 1 hr. Hence, after every hour (or on reloading the application), users will be asked to grant access to read their files to the Sheets BI app.
Sheets BI stores the access token in-memory (and not even cookie or local storage) and hence is very secure.
By default the creator of a workbook becomes the
admin of the workbook. This allows the user to share the workbook with other users and/or teams.
While sharing the workbook, the following access levels can be granted to either the user or the team.
admin- users with this workbook access role can manage sharing of the workbook. In addition they have the privileges of an
editorof the workbook.
editor- users with this workbook access role can edit the workbook. In addition they have the privileges of a
viewerof the workbook.
viewer- users can view the workbook and the dashboards and do ad-hoc data analysis within the shared workbooks
The workbook sharing only provides access to the workbook and not the data referenced by the workbook. Data access is still governed by the access permission on Google Sheets and Google Drive.
Sharing with a user
A workbook can be shared directly with a user giving one of the above mentioned roles.
Sharing with a team
A workbook can be shared with the entire team. However, the user of a team gets the least permission role based on the workbook role shared to the team
and the team role of the user. For example, if the workbook role shared is
admin but the user has an
editor role for that team, the user only
editor role for the workbook.
Following matrix shows the effective role of a user w.r.t a workbook based on the role of the user with the team (rows) and the workbook role granted to the team (columns).
The effective role derivation is actually based on a 3-d matrix rather than a 2-d matrix as the system user role is also applied in deriving the effective access level of a user for a given workbook.
When launching the workbook designer, the workbook is automatically locked for editing. From within the designer, user can edit the workbook by adding
- data sources
- sheets - charts, pivot tables and others
Locking & unlocking
As workbook authoring can be a long process, locking prevents other users from accidentally overwriting the changes. They can save their changes at anytime and continue to edit. There is an option to Unlock at which time the workbook is unlocked. Users can also exit the workbook designer without unlocking by using the Cancel button. This allows them to comeback and edit it later and not permit other users to change it till they are done.
It is possible for the admin of a workbook to force unlock a workbook.
Workbook viewer is meant for read-only access of the workbook. The users can slice and dice the data using slicers, filters and other visualization tools provided for a milli-second latency interactions made possible thanks to in-browser in-memory data analytics capabilities of SQL Frames.