Google Sheets + Steampipe

select
  title
from
  googlesheets_sheet;

+-----------+
| title     |
+-----------+
| Marks     |
| Students  |
| Dashboard |
| Books     |
| Employees |
+-----------+

connection "googlesheets" {
  plugin = "googlesheets"

  # Google Sheets Plugin - Sample School Data
  spreadsheet_id = "11iXfj-RHpFsil7_hNK-oQjCqmBLlDfCvju2AOF-ieb4"
  sheets         = ["Dashboard", "Students", "Books", "Marks", "Employees"]
}

.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.    |
+--------------------------+------+-------------+

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 |
+--------------+-------------+------+

select
  cell,
  value
from
  googlesheets_cell
where
  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            |
+------|--------------+

steampipe plugin install 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"

  # List of sheets that will be created as dynamic tables.
  # No dynamic tables will be created if this arg is empty or not set.
  # Wildcard based searches are supported.

  # For example:
  #  - "*" matches all sheets
  #  - "Student*" matches all sheets starting with "Student"
  #  - "Books" matches a sheet named "Books"

  # Defaults to all sheets
  # sheets = ["*"]

  # 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"
}

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/postgres.sh)" -- googlesheets

CREATE EXTENSION IF NOT EXISTS steampipe_postgres_googlesheets;
CREATE SERVER steampipe_googlesheets FOREIGN DATA WRAPPER steampipe_postgres_googlesheets OPTIONS (config '<your_config>');
CREATE SCHEMA googlesheets;
IMPORT FOREIGN SCHEMA googlesheets FROM SERVER steampipe_googlesheets INTO googlesheets;

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/sqlite.sh)" -- googlesheets

$ sqlite3
sqlite> .load ./steampipe_sqlite_extension_googlesheets.so
sqlite> select steampipe_configure_googlesheets('<your_config>');

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- googlesheets

steampipe_export_googlesheets --config '<your_config>' <table_name>