Table: googlesheets_sheet - Query Google Sheets Sheets 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 Sheets within Google Sheets are individual tabs of a document, where data is input and organized. Each sheet contains cells, and the data within these cells can be manipulated using formulas, functions, and data validation rules.

Table Usage Guide

The googlesheets_sheet table provides insights into Sheets within Google Sheets. As a data analyst, explore sheet-specific details through this table, including metadata, content, and associated cell data. Utilize it to uncover information about sheets, such as their structure, the data they contain, and the relationships between different data points.

Examples

Basic info

Explore which Google Sheets are available in your account and their respective identifiers. This can be beneficial for managing and tracking your documents systematically.

select
  title,
  sheet_id,
  spreadsheet_id
from
  googlesheets_sheet;

select
  title,
  sheet_id,
  spreadsheet_id
from
  googlesheets_sheet;

Get information about a specific sheet

Explore which Google Sheets contain specific information by identifying instances where the title matches a certain term. This allows you to quickly locate and analyze data within a vast collection of spreadsheets.

select
  title,
  sheet_id,
  spreadsheet_id,
  hidden,
  sheet_type
from
  googlesheets_sheet
where
  title = 'Students';

select
  title,
  sheet_id,
  spreadsheet_id,
  hidden,
  sheet_type
from
  googlesheets_sheet
where
  title = 'Students';

List hidden sheets

Uncover the details of hidden sheets within your Google Sheets. This query is useful for identifying which sheets are hidden, allowing you to better manage and organize your data.

select
  title,
  sheet_id,
  spreadsheet_id,
  hidden,
  sheet_type
from
  googlesheets_sheet
where
  hidden;

select
  title,
  sheet_id,
  spreadsheet_id,
  hidden,
  sheet_type
from
  googlesheets_sheet
where
  hidden = 1;

List sheets with protected ranges

Explore which Google Sheets contain protected ranges. This can be useful to identify instances where data is safeguarded, helping to maintain data integrity and control access.

select
  title,
  sheet_id,
  spreadsheet_id,
  protected_ranges
from
  googlesheets_sheet
where
  protected_ranges is not null;

select
  title,
  sheet_id,
  spreadsheet_id,
  protected_ranges
from
  googlesheets_sheet
where
  protected_ranges is not null;

Schema for googlesheets_sheet

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
banded_ranges	jsonb	The banded (alternating colors) ranges on this sheet.
basic_filter	jsonb	The filter on this sheet, if any.
charts	jsonb	The specifications of every chart on this sheet.
column_groups	jsonb	All column groups on this sheet, ordered by increasing range start index, then by group depth.
conditional_formats	jsonb	The conditional format rules in this sheet.
data	jsonb	Data in the grid, if this is a grid sheet.
data_source_sheet_properties	jsonb	Specifies the properties specific to the DATA_SOURCE sheet.
developer_metadata	jsonb	The developer metadata associated with a sheet.
filter_views	jsonb	The filter views in this sheet.
grid_properties	jsonb	Additional properties of the sheet if this sheet is a grid.
hidden	boolean	Indicates whether the sheet is hidden, or not.
index	bigint	The index of the sheet within the spreadsheet.
merges	jsonb	The ranges that are merged together.
protected_ranges	jsonb	The protected ranges in this sheet.
right_to_left	boolean	Indicates whether sheet is an RTL sheet instead of an LTR sheet.
row_groups	jsonb	All row groups on this sheet, ordered by increasing range start index, then by group depth.
sheet_id	bigint	The ID of the sheet.
sheet_type	text	The type of sheet. Defaults to GRID.
slicers	jsonb	The slicers on this sheet.
spreadsheet_id	text	The ID of the spreadsheet.
tab_color	jsonb	The color of the tab in the UI.
tab_color_style	jsonb	The color of the tab in the UI.
title	text	The name of the sheet.

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_sheet