Google Sheets + Steampipe
Google Sheets is an online spreadsheet app that lets you create and format spreadsheets and work with other people.
Steampipe is an open source CLI to instantly query cloud APIs using SQL.
Query all the sheets in your spreadsheet:
select titlefrom googlesheets_sheet;
+-----------+| title |+-----------+| Marks || Students || Dashboard || Books || Employees |+-----------+
A table is automatically created to represent each sheet mentioned in the
configured sheets
config argument.
For instance, if using the following configuration:
connection "googlesheets" { plugin = "googlesheets"
# Google Sheets Plugin - Sample School Data spreadsheet_id = "11iXfj-RHpFsil7_hNK-oQjCqmBLlDfCvju2AOF-ieb4" sheets = ["Dashboard", "Students", "Books", "Marks", "Employees"]}
To get details for a specific table, inspect it by name:
.inspect "Students"+--------------------------+------+-------------+| column | type | description |+--------------------------+------+-------------+| Class Level | text | Field 2. || Extracurricular Activity | text | Field 5. || GPA | text | Field 7. || Home State | text | Field 3. || ID | text | Field 1. || Major | text | Field 4. || Mentor | text | Field 6. || Student Name | text | Field 0. |+--------------------------+------+-------------+
You can query data from the Students
sheet using the sheet's column names:
select "Student Name", "Class Level", "GPA"from "Students";
+--------------+-------------+------+| Student Name | Class Level | GPA |+--------------+-------------+------+| Andrew | Freshman | 3.50 || Alexandra | Senior | 3.20 || Karen | Sophomore | 3.90 || Edward | Junior | 3.40 || Ellen | Freshman | 2.90 || Becky | Sophomore | 3.10 |+--------------+-------------+------+
You can also query data from a sheet by cell address:
select cell, valuefrom googlesheets_cellwhere sheet_name = 'Students';
+------+--------------+| cell | value |+------+--------------+| A1 | Student Name || A2 | Alexandra || A3 | Andrew || A4 | Anna || A5 | Becky || B1 | ID || B2 | 1 || B3 | 2 || B4 | 3 || B5 | 4 |+------|--------------+
Documentation
Get started
Install
Download and install the latest Google Sheets plugin:
steampipe plugin install googlesheets
Credentials
Item | Description |
---|---|
APIs | 1. Go to the Google API Console. 2. Select the project that contains your credentials. 3. Click Enable APIs and Services . 4. Enable both the Google Drive API and Google Sheets API . |
Credentials | 1. To use OAuth client, configure your credentials. 2. To use domain-wide delegation, generate your service account and credentials and delegate domain-wide authority to your service account. Use https://www.googleapis.com/auth/drive.readonly and https://www.googleapis.com/auth/spreadsheets.readonly OAuth 2.0 scopes. |
Radius | Each connection represents a single Google spreadsheet. |
Resolution | 1. Credentials from the JSON file specified by the credentials parameter in your Steampipe config.2. Credentials from the JSON file specified by the token_path parameter in your Steampipe config.3. Credentials from the default json file location ( ~/.config/gcloud/application_default_credentials.json ). |
Configuration
Installing the latest googlesheets plugin will create a config file (~/.steampipe/config/googlesheets.spc
) with a single connection named googlesheets
:
connection "googlesheets" { plugin = "googlesheets"
# The spreadsheet ID can be found in the spreadsheet's URL, e.g., https://docs.google.com/spreadsheets/d/11iXfj-RHpFsil7_hNK-oQjCqmBLlDfCvju2AOF-ieb4 # spreadsheet_id = "11iXfj-RHpFsil7_hNK-oQjCqmBLlDfCvju2AOF-ieb4"
# If no sheets are specified, then all sheets will be retrieved # sheets = ["Dashboard", "Students", "Books", "Marks", "Employees"]
# You may connect to Google Sheet using more than one option:
# 1. To authenticate using domain-wide delegation, specify a service account credential file and the user email for impersonation # `credentials` - Either the path to a JSON credential file that contains Google application credentials, # or the contents of a service account key file in JSON format. If `credentials` is not specified in a connection, # credentials will be loaded from: # - The path specified in the `GOOGLE_APPLICATION_CREDENTIALS` environment variable, if set; otherwise # - The standard location (`~/.config/gcloud/application_default_credentials.json`) # - The path specified for the credentials.json file ("/path/to/my/creds.json") # credentials = "~/.config/gcloud/application_default_credentials.json"
# `impersonated_user_email` - The email (string) of the user which should be impersonated. Needs permissions to access the Admin APIs. # impersonated_user_email = "username@domain.com"
# 2. To authenticate using OAuth 2.0, specify a client secret file # `token_path` - The path to a JSON credential file that contains Google application credentials. # If `token_path` is not specified in a connection, credentials will be loaded from: # - The path specified in the `GOOGLE_APPLICATION_CREDENTIALS` environment variable, if set; otherwise # - The standard location (`~/.config/gcloud/application_default_credentials.json`) # token_path = "/Users/myuser/.config/gcloud/application_default_credentials.json"}
The default values for the spreadsheet_id
and sheets
arguments are
configured to be used with our Google Sheets Plugin - Sample School
Data
spreadsheet, which is a public spreadsheet maintained by the Steampipe team.
All of our examples in this document, and in our table documents, are based on
this sample spreadsheet.
Get involved
- Open source: https://github.com/turbot/steampipe-plugin-googlesheets
- Community: Slack Channel
Advanced configuration options
Authenticate using OAuth client
You can use client secret credentials to protect the user's data by only granting tokens to authorized requestors. Use following steps to configure credentials:
Create an OAuth client ID credential with the application type
Desktop app
, and download the client secret JSON file.Wherever you have the Google Cloud SDK installed, run the following command with the correct client secret JSON file parameters:
gcloud auth application-default login \--client-id-file=client_secret.json \--scopes="https://www.googleapis.com/auth/drive.readonly,https://www.googleapis.com/auth/spreadsheets.readonly"In the browser window that just opened, authenticate as the user you would like to make the API calls through.
Review the output for the location of the Application Default Credentials file, which usually appears following the text
Credentials saved to file:
.Set the Application Default Credentials filepath in the Steampipe config
token_path
or in theGOOGLE_APPLICATION_CREDENTIALS
environment variable.