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, valuefrom 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, valuefrom googlesheets_cellwhere 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, valuefrom googlesheets_cellwhere 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, valuefrom googlesheets_cellwhere sheet_name = 'Students' and row = 2 and col = 'A';
Or you can use A1 notation with the range
column:
select sheet_name, cell, valuefrom googlesheets_cellwhere 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, valuefrom googlesheets_cellwhere sheet_name = 'Students' and row = 1;
Or by using A1 notation with the range
column:
select sheet_name, cell, valuefrom googlesheets_cellwhere 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, valuefrom googlesheets_cellwhere sheet_name = 'Students' and col = 'A';
Or with A1 notation and the range
column:
select sheet_name, cell, valuefrom googlesheets_cellwhere range = 'Students!A:A';
List cells with hyperlink information
select sheet_name, cell, value, hyperlinkfrom googlesheets_cellwhere sheet_name = 'Students' and hyperlink is not null;
List cells with a formula
select sheet_name, cell, value, formulafrom googlesheets_cellwhere sheet_name = 'Employees' and formula is not null;
List cells with formula parse errors
select sheet_name, cell, value, formulafrom googlesheets_cellwhere 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, valuefrom googlesheets_cellwhere 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, valuefrom googlesheets_cellwhere range = 'Students!R1C1:R5C1';
.inspect googlesheets_cell
Retrieve information of cells of a sheet in a spreadsheet.
Name | Type | Description |
---|---|---|
_ctx | jsonb | Steampipe context in JSON form, e.g. connection_name. |
cell | text | The address of a cell. |
col | text | The ID of the column. |
formula | text | The formula configured for a cell. |
hyperlink | text | A hyperlink this cell points to, if any. If the cell contains multiple hyperlinks, this field will be empty. |
note | text | A user defined note on a cell. |
range | text | The ranges to retrieve from the spreadsheet. |
row | bigint | The index of the row. |
sheet_name | text | The name of the sheet. |
spreadsheet_id | text | The ID of the spreadsheet. |
value | text | The value of a cell. |