Orri

Orri exposes a Google spreadsheet as a JDBC database.

Available versions

dependencies {
    // ...
    implementation 'io.github.rahulsom:orri-jdbc:<VERSION>'
}

Summary

Each worksheet becomes a table. The first row in each worksheet becomes the column header. The remaining rows become table data. Google Sheets filter views are exposed as JDBC views. Boolean cells can come from either literal true and false values or checkbox-backed cells. Connections are writable by default for worksheet tables.

JDBC URL

The JDBC URL uses the spreadsheet id directly.

private static final String SPREADSHEET_URL = "jdbc:orri:1yRm-oPjuUGvfy4uhGc-woytgXQ7nSpxalWVX9djruSQ";

For the sample spreadsheet below, the spreadsheet id is 1yRm-oPjuUGvfy4uhGc-woytgXQ7nSpxalWVX9djruSQ.

https://docs.google.com/spreadsheets/d/1yRm-oPjuUGvfy4uhGc-woytgXQ7nSpxalWVX9djruSQ/edit

Authentication

This driver supports several ways to authenticate with the Google Sheets API. Use an API key for public spreadsheets. Use a service account or an OAuth access token for private spreadsheets.

Public Spreadsheet With API Key

Properties properties = new Properties();
properties.setProperty("apiKey", "your-google-api-key");
properties.setProperty("readOnly", "true");

return driver.connect(SPREADSHEET_URL, properties);

Private Spreadsheet With A Service Account

Properties properties = new Properties();
properties.setProperty("credentialsFile", "/path/to/service-account.json");

return driver.connect(SPREADSHEET_URL, properties);

The credentialsFile property should point at a Google service account JSON key file. The service account must have access to the target spreadsheet.

Inspecting Schema

Use JDBC metadata to discover worksheets, filter views, and columns.

Properties properties = new Properties();
properties.setProperty("apiKey", "your-google-api-key");
properties.setProperty("readOnly", "true");

List<String> relations = new ArrayList<>();
List<String> columns = new ArrayList<>();
try (Connection connection = driver.connect(SPREADSHEET_URL, properties)) {
    DatabaseMetaData metaData = connection.getMetaData();

    try (ResultSet tables = metaData.getTables(null, null, "%", null)) {
        while (tables.next()) {
            String schemaName = tables.getString("TABLE_SCHEM");
            String tableName = tables.getString("TABLE_NAME");
            String tableType = tables.getString("TABLE_TYPE");
            if (!"INFORMATION_SCHEMA".equalsIgnoreCase(schemaName)) {
                relations.add(tableName + " (" + normalizeTableType(tableType) + ")");
            }
        }
    }

    try (ResultSet resultSet = metaData.getColumns(null, null, "Employees", "%")) {
        while (resultSet.next()) {
            String columnName = resultSet.getString("COLUMN_NAME");
            String typeName = resultSet.getString("TYPE_NAME");
            columns.add(columnName + " " + normalizeTypeName(typeName));
        }
    }
}
return new SchemaSnapshot(relations, columns);

Worksheets are reported as TABLE. Filter views are reported as VIEW.

Managing Worksheets and Filter Views.

A Worksheet in Google Sheets is created with CREATE TABLE.

statement.execute("""
        create table "%s" (
            "Name" varchar,
            "Active" boolean,
            "Score" decimal(10, 2)
        )
        """.formatted(tableName));

A Filter View in Google Sheets is created with CREATE VIEW.

statement.execute("""
        create view "%s" as
        select "Name", "Active"
        from "%s"
        where "Active" = true
        order by "Name"
        """.formatted(viewName, tableName));

These can also be deleted with DROP TABLE and DROP VIEW.

statement.execute("drop view \"%s\"".formatted(renamedViewName));
statement.execute("drop table \"%s\"".formatted(renamedAlterTableName));
statement.execute("drop table \"%s\"".formatted(tableName));

Orri also supports a constrained subset of ALTER TABLE and ALTER VIEW.

statement.execute("""
        create table "%s" (
            "Name" varchar,
            "Score" decimal(10, 2)
        )
        """.formatted(alterTableName));
statement.executeUpdate("""
        insert into "%s" ("Name", "Score") values
        ('Ada', 10.5)
        """.formatted(alterTableName));
statement.execute("""
        alter table "%s"
        rename to "%s"
        """.formatted(alterTableName, renamedAlterTableName));
statement.execute("""
        alter table "%s"
        add column "Notes" varchar
        """.formatted(renamedAlterTableName));
statement.executeUpdate("""
        update "%s"
        set "Notes" = 'top performer'
        where "Name" = 'Ada'
        """.formatted(renamedAlterTableName));
statement.execute("""
        alter table "%s"
        alter column "Notes"
        rename to "Comment"
        """.formatted(renamedAlterTableName));
statement.execute("""
        alter table "%s"
        drop column "Score"
        """.formatted(renamedAlterTableName));

statement.execute("""
        alter view "%s"
        rename to "%s"
        """.formatted(viewName, renamedViewName));
statement.execute("""
        alter view "%s" as
        select "Name"
        from "%s"
        where "Name" = 'Ada'
        """.formatted(renamedViewName, tableName));

ALTER TABLE … DROP COLUMN … is rejected when the worksheet has dependent views.

Modifying Data

INSERT, UPDATE, and DELETE are supported for worksheet tables by default.

Authenticate with OAuth or a service account when you need write access.

Filter views remain read-only.

statement.executeUpdate("""
        insert into "%s" ("Name", "Active", "Score") values
        ('Ada', true, 10.5),
        ('Bob', false, 7.25),
        ('Cy', true, 9.75),
        ('Dee', false, 5.50),
        ('Eve', true, 8.00)
        """.formatted(tableName));

statement.executeUpdate("""
        update "%s"
        set "Active" = true, "Score" = 6.50
        where "Name" = 'Dee'
        """.formatted(tableName));

statement.executeUpdate("""
        delete from "%s"
        where "Name" = 'Bob'
        """.formatted(tableName));

After a successful mutation, the driver synchronizes the affected worksheet back to Google Sheets. If you want to force a read-only connection, set readOnly=true in the JDBC URL or connection properties.

Querying Data

Once connected, you can query worksheets and filter views with normal SQL. Worksheets use their tab names as table names. Filter views use their filter view titles as view names.

List<String> finalRows = selectRows(statement, """
                select "Name", "Active", "Score"
                from "%s"
                order by "Name"
                """.formatted(tableName));
List<String> alteredTableRows = selectRows(statement, """
                select "Name", "Comment"
                from "%s"
                order by "Name"
                """.formatted(renamedAlterTableName));
List<String> finalViewRows = selectRows(statement, """
                select "Name"
                from "%s"
                order by "Name"
                """.formatted(renamedViewName));

In the example above, Employees is a worksheet and Active Employees is a filter view. Quoted identifiers are recommended when sheet names or filter view names contain spaces.

Type Mapping

Columns are inferred from the worksheet contents.

Boolean columns map to JDBC booleans.
Numeric columns map to JDBC decimals.
All other columns map to JDBC strings.
If a checkbox column is used in Google Sheets, the driver exposes it as a boolean column when the rendered values are true and false.