turbot/googlesheets
steampipe plugin install googlesheets

Table: googlesheets_spreadsheet - Query Google Sheets Spreadsheets using SQL

Google Sheets is a web-based application that allows users to create, update, and modify spreadsheets and share the data live online. The service is part of the Google Suite of applications and is designed to be a free, web-based alternative to traditional spreadsheet software such as Microsoft Excel. Google Sheets allows multiple users to collaborate in real-time, providing a platform for data manipulation and analysis.

Table Usage Guide

The googlesheets_spreadsheet table provides insights into the spreadsheets within Google Sheets. As a data analyst, explore spreadsheet-specific details through this table, including metadata, content, and sharing permissions. Utilize it to uncover information about spreadsheets, such as those shared with specific users, the content of the spreadsheets, and the metadata associated with each spreadsheet.

Examples

Basic info

Explore which Google Sheets spreadsheets have been recently modified, owned, shared, starred, or linked on the web. This can help in managing access and tracking changes to important documents.

select
name,
modified_time,
owned_by_me,
shared,
starred,
web_view_link
from
googlesheets_spreadsheet;
select
name,
modified_time,
owned_by_me,
shared,
starred,
web_view_link
from
googlesheets_spreadsheet;

Get information about last modifying user

Explore which user last modified a Google Sheets spreadsheet and when the modification occurred. This can be useful for tracking changes and maintaining accountability in collaborative environments.

select
name as spreadsheet_name,
last_modifying_user ->> 'displayName' as user_display_name,
last_modifying_user ->> 'emailAddress' as user_email_address,
modified_time
from
googlesheets_spreadsheet;
select
name as spreadsheet_name,
json_extract(last_modifying_user, '$.displayName') as user_display_name,
json_extract(last_modifying_user, '$.emailAddress') as user_email_address,
modified_time
from
googlesheets_spreadsheet;

Check if current user has capability to edit the spreadsheet

Explore the access rights of the current user to understand if they have the necessary permissions to edit a Google Sheets spreadsheet. This can be useful in managing user permissions and ensuring data integrity.

select
name as spreadsheet_name,
web_view_link,
case
when capabilities -> 'canEdit' is null then false
else (capabilities ->> 'canEdit') :: boolean
end as can_edit
from
googlesheets_spreadsheet;
select
name as spreadsheet_name,
web_view_link,
case
when json_extract(capabilities, '$.canEdit') is null then 0
else json_extract(capabilities, '$.canEdit')
end as can_edit
from
googlesheets_spreadsheet;

Get sharing info with permissions

Explore the sharing details and permissions of your Google Sheets spreadsheets. This query can help you understand who has access to your documents, their roles, and whether they can discover files, providing a comprehensive view of your document's security.

select
name,
permission ->> 'type' as grantee_type,
permission ->> 'displayName' as grantee_display_name,
permission ->> 'emailAddress' as email_address,
permission ->> 'domain' as domain_address,
permission ->> 'role' as role,
permission ->> 'allowFileDiscovery' as allow_file_discovery
from
googlesheets_spreadsheet,
jsonb_array_elements(permissions) as permission;
select
name,
json_extract(permission.value, '$.type') as grantee_type,
json_extract(permission.value, '$.displayName') as grantee_display_name,
json_extract(permission.value, '$.emailAddress') as email_address,
json_extract(permission.value, '$.domain') as domain_address,
json_extract(permission.value, '$.role') as role,
json_extract(permission.value, '$.allowFileDiscovery') as allow_file_discovery
from
googlesheets_spreadsheet,
json_each(permissions) as permission;

Schema for googlesheets_spreadsheet

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form, e.g. connection_name.
capabilitiesjsonbSpecifies a set of capabilities the current user has on this spreadsheet.
copy_requires_writer_permissionbooleanIndicates whether the options to copy, print, or download this spreadsheet, should be disabled for readers and commenters.
created_timetimestamp with time zoneThe time at which the spreadsheet was created.
drive_idtextID of the shared drive the spreadsheet resides in.
explicitly_trashedbooleanIndicates whether the spreadsheet has been explicitly trashed, or not.
export_linksjsonbLinks for exporting Docs Editors files to specific formats.
has_thumbnailbooleanIndicates whether the spreadsheet has a thumbnail, or not.
icon_linktextA static, unauthenticated link to the spreadsheets's icon.
idtextThe ID of the spreadsheet.
is_app_authorizedbooleanIndicates whether the spreadsheet was created or opened by the requesting app, or not.
last_modifying_userjsonbSpecifies the details of last user to modify the spreadsheet.
link_share_metadatajsonbSpecifies details about the link URLs that clients are using to refer to this item.
modified_by_mebooleanIndicates whether the spreadsheet has been modified by you, or not.
modified_by_me_timetimestamp with time zoneThe last time the spreadsheet was modified by the user.
modified_timetimestamp with time zoneThe last time the spreadsheet was modified by anyone.
nametextThe name of the spreadsheet.
owned_by_mebooleanIndicates whether the spreadsheet owns by you, or not.
ownersjsonbSpecifies the owner of this spreadsheet.
permission_idsjsonbA list of permission IDs for users with access to this spreadsheet.
permissionsjsonbThe full list of permissions for the spreadsheet.
propertiesjsonbA collection of arbitrary key-value pairs which are visible to all apps.
quota_bytes_usedbigintThe number of storage quota bytes used by the spreadsheet.
sharedbooleanIndicates whether the spreadsheet has been shared, or not.
spacesjsonbThe list of spaces which contain the spreadsheet.
starredbooleanIndicates whether the user has starred the spreadsheet, or not.
thumbnail_linktextA short-lived link to the spreadsheet's thumbnail, if available.
thumbnail_versionbigintThe thumbnail version for use in thumbnail cache invalidation.
trashedbooleanIndicates whether the spreadsheet has been trashed, either explicitly or from a trashed parent folder, or not.
trashed_timetimestamp with time zoneThe time that the item was trashed.
versionbigintA monotonically increasing version number for the spreadsheet.
viewed_by_mebooleanIndicates whether the spreadsheet has been viewed by this user, or not.
viewed_by_me_timetimestamp with time zoneThe last time the spreadsheet was viewed by the user.
viewers_can_copy_contentbooleanIndicates whether the spreadsheet has been viewed by this user, or not.
web_view_linktextA link for opening the spreadsheet in a relevant Google editor or viewer in a browser.
writers_can_sharebooleanIndicates whether users with only writer permission can modify the spreadsheet's permissions, or not.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- googlesheets

You can pass the configuration to the command with the --config argument:

steampipe_export_googlesheets --config '<your_config>' googlesheets_spreadsheet