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;

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

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form.
capabilities	jsonb	Specifies a set of capabilities the current user has on this spreadsheet.
copy_requires_writer_permission	boolean	Indicates whether the options to copy, print, or download this spreadsheet, should be disabled for readers and commenters.
created_time	timestamp with time zone	The time at which the spreadsheet was created.
drive_id	text	ID of the shared drive the spreadsheet resides in.
explicitly_trashed	boolean	Indicates whether the spreadsheet has been explicitly trashed, or not.
export_links	jsonb	Links for exporting Docs Editors files to specific formats.
has_thumbnail	boolean	Indicates whether the spreadsheet has a thumbnail, or not.
icon_link	text	A static, unauthenticated link to the spreadsheets's icon.
id	text	The ID of the spreadsheet.
is_app_authorized	boolean	Indicates whether the spreadsheet was created or opened by the requesting app, or not.
last_modifying_user	jsonb	Specifies the details of last user to modify the spreadsheet.
link_share_metadata	jsonb	Specifies details about the link URLs that clients are using to refer to this item.
modified_by_me	boolean	Indicates whether the spreadsheet has been modified by you, or not.
modified_by_me_time	timestamp with time zone	The last time the spreadsheet was modified by the user.
modified_time	timestamp with time zone	The last time the spreadsheet was modified by anyone.
name	text	The name of the spreadsheet.
owned_by_me	boolean	Indicates whether the spreadsheet owns by you, or not.
owners	jsonb	Specifies the owner of this spreadsheet.
permission_ids	jsonb	A list of permission IDs for users with access to this spreadsheet.
permissions	jsonb	The full list of permissions for the spreadsheet.
properties	jsonb	A collection of arbitrary key-value pairs which are visible to all apps.
quota_bytes_used	bigint	The number of storage quota bytes used by the spreadsheet.
shared	boolean	Indicates whether the spreadsheet has been shared, or not.
sp_connection_name	text	Steampipe connection name.
sp_ctx	jsonb	Steampipe context in JSON form.
spaces	jsonb	The list of spaces which contain the spreadsheet.
spreadsheet_id	text	The ID of the spreadsheet.
starred	boolean	Indicates whether the user has starred the spreadsheet, or not.
thumbnail_link	text	A short-lived link to the spreadsheet's thumbnail, if available.
thumbnail_version	bigint	The thumbnail version for use in thumbnail cache invalidation.
trashed	boolean	Indicates whether the spreadsheet has been trashed, either explicitly or from a trashed parent folder, or not.
trashed_time	timestamp with time zone	The time that the item was trashed.
version	bigint	A monotonically increasing version number for the spreadsheet.
viewed_by_me	boolean	Indicates whether the spreadsheet has been viewed by this user, or not.
viewed_by_me_time	timestamp with time zone	The last time the spreadsheet was viewed by the user.
viewers_can_copy_content	boolean	Indicates whether the spreadsheet has been viewed by this user, or not.
web_view_link	text	A link for opening the spreadsheet in a relevant Google editor or viewer in a browser.
writers_can_share	boolean	Indicates 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