turbot/googlesheets

GitHub
steampipe plugin install googlesheetssteampipe plugin install googlesheets

Table: googlesheets_cell

Query cell data from sheets in a Google Sheets spreadsheet. Cells that have no data, i.e., no value or formula, will not be returned.

All examples below can be used with the Google Sheets Plugin - Sample School Data spreadsheet, which is a public spreadsheet maintained by the Steampipe team.

Examples

Query cells in all sheets

select
sheet_name,
cell,
value
from
googlesheets_cell

Query cells in a specific sheet

You can query all cells from a specific sheet using the sheet_name column:

select
sheet_name,
cell,
value
from
googlesheets_cell
where
sheet_name = 'Students';

Query a range of cells

Using the A1 notation, you can query specific cells using the range column:

select
sheet_name,
cell,
value
from
googlesheets_cell
where
range = 'Students!B1:C2';

Query a specific cell

You can query a specific cell with the sheet_name, row, and col columns:

select
sheet_name,
cell,
value
from
googlesheets_cell
where
sheet_name = 'Students'
and row = 2
and col = 'A';

Or you can use A1 notation with the range column:

select
sheet_name,
cell,
value
from
googlesheets_cell
where
range = 'Students!A2';

Query cells in a row

Similar to the examples above, you can also query a specific row using the sheet_name and row columns:

select
sheet_name,
cell,
value
from
googlesheets_cell
where
sheet_name = 'Students'
and row = 1;

Or by using A1 notation with the range column:

select
sheet_name,
cell,
value
from
googlesheets_cell
where
range = 'Students!1:1';

Query cells in a column

Specific columns can also be queried using the sheet_name and col columns:

select
sheet_name,
cell,
value
from
googlesheets_cell
where
sheet_name = 'Students'
and col = 'A';

Or with A1 notation and the range column:

select
sheet_name,
cell,
value
from
googlesheets_cell
where
range = 'Students!A:A';
select
sheet_name,
cell,
value,
hyperlink
from
googlesheets_cell
where
sheet_name = 'Students'
and hyperlink is not null;

List cells with a formula

select
sheet_name,
cell,
value,
formula
from
googlesheets_cell
where
sheet_name = 'Employees'
and formula is not null;

List cells with formula parse errors

select
sheet_name,
cell,
value,
formula
from
googlesheets_cell
where
sheet_name = 'Employees'
and formula is not null
and value in (
'#N/A',
'#DIV/0!',
'#VALUE!',
'#REF!',
'#NAME?',
'#NUM!',
'#ERROR!',
'#NULL!'
);

Advanced examples

Query cells in a specific sheet using range

In A1 notation, just the sheet name can be passed in as the range to return all cells from that sheet:

select
sheet_name,
cell,
value
from
googlesheets_cell
where
range = 'Students';

Query cells using R1C1 notation

In addition to A1 notation, the range column also supports R1C1 notation.

For instance, to get the first five cells in the first column:

select
sheet_name,
cell,
value
from
googlesheets_cell
where
range = 'Students!R1C1:R5C1';

.inspect googlesheets_cell

Retrieve information of cells of a sheet in a spreadsheet.

NameTypeDescription
_ctxjsonbSteampipe context in JSON form, e.g. connection_name.
celltextThe address of a cell.
coltextThe ID of the column.
formulatextThe formula configured for a cell.
hyperlinktextA hyperlink this cell points to, if any. If the cell contains multiple hyperlinks, this field will be empty.
notetextA user defined note on a cell.
rangetextThe ranges to retrieve from the spreadsheet.
rowbigintThe index of the row.
sheet_nametextThe name of the sheet.
spreadsheet_idtextThe ID of the spreadsheet.
valuetextThe value of a cell.