Query Google Sheets with SQL

4 months ago 3

A Go ORM for Google Sheets that allows you to query and manipulate Google Sheets data using both a fluent API and SQL-like syntax.

Fluent API: Chainable query builder similar to popular Go ORMs
SQL Support: Write raw SQL queries against Google Sheets
Struct Mapping: Map sheet rows to Go structs with tags
Type Safety: Automatic type conversion and validation
Rich Querying: Support for WHERE, LIMIT, OFFSET, and various operators
Insert Operations: Add new rows to sheets
Idiomatic Go: Follows Go best practices and conventions

go get github.com/johannes/sheetsql

Create a Google Cloud project and enable the Google Sheets API
Create service account credentials and download the JSON file
Share your Google Sheet with the service account email

package main import ( "context" "fmt" "log" "github.com/johannes/sheetsql" "google.golang.org/api/option" ) type User struct { ID int `sheet:"ID"` Name string `sheet:"Name"` Email string `sheet:"Email"` Age int `sheet:"Age"` City string `sheet:"City"` } func main() { ctx := context.Background() // Initialize client client, err := sheetsql.NewClient( ctx, "your-spreadsheet-id", option.WithCredentialsFile("path/to/credentials.json"), ) if err != nil { log.Fatal(err) } // Query all users var users []User err = client.From("Users").Get(&users) if err != nil { log.Fatal(err) } for _, user := range users { fmt.Printf("%s (%d) - %s\n", user.Name, user.Age, user.City) } }

// Get all rows var users []User err := client.From("Users").Get(&users) // With WHERE clause err = client.From("Users"). Where("Age", ">", 18). Get(&users) // Multiple WHERE clauses (AND) err = client.From("Users"). Where("Age", ">", 18). Where("City", "=", "New York"). Get(&users) // With LIMIT and OFFSET err = client.From("Users"). Where("Age", ">", 18). Limit(10). Offset(5). Get(&users)

= or == - Equal
!= - Not equal
> - Greater than
< - Less than
>= - Greater than or equal
<= - Less than or equal
LIKE - Contains (case-insensitive)

newUser := User{ Name: "John Doe", Email: "[email protected]", Age: 30, City: "New York", } err := client.From("Users").Insert(newUser)

For those who prefer SQL syntax:

parser := sheetsql.NewSQLParser(client) // Basic SELECT var users []User err := parser.Query("SELECT * FROM Users", &users) // With WHERE clause err = parser.Query("SELECT * FROM Users WHERE Age > 25", &users) // Complex queries err = parser.Query(` SELECT * FROM Users WHERE Age > 20 AND City = 'New York' LIMIT 10 OFFSET 5 `, &users)

SELECT * FROM table
WHERE clauses with AND conditions
LIMIT and OFFSET
Operators: =, !=, <>, >, <, >=, <=, LIKE
String literals with single or double quotes
Automatic type conversion for numbers and booleans

Use the sheet tag to map struct fields to sheet columns:

type User struct { ID int `sheet:"ID"` FullName string `sheet:"Name"` // Maps to "Name" column Email string `sheet:"Email"` Age int `sheet:"Age"` Location string `sheet:"City"` // Maps to "City" column }

If no tag is provided, the struct field name is used as the column name.

string
int, int8, int16, int32, int64
float32, float64
bool

Set up environment variables and run integration tests:

export GOOGLE_CREDENTIALS_FILE=/path/to/credentials.json go test -v ./... -run Integration

To enable tests that modify the sheet:

export ENABLE_WRITE_TESTS=true go test -v ./... -run Integration

Run the example program:

cd examples export GOOGLE_CREDENTIALS_FILE=/path/to/credentials.json go run main.go

Enable write examples:

export ENABLE_WRITE_EXAMPLES=true go run main.go

Sheet Structure Requirements

Your Google Sheet should have:

Header row: First row contains column names
Data rows: Subsequent rows contain data
Consistent columns: All rows should have the same number of columns

Example sheet structure:

| ID | Name | Email | Age | City | |----|-----------|-----------------|-----|-----------| | 1 | John Doe | [email protected] | 30 | New York | | 2 | Jane Smith| [email protected] | 25 | Boston |

Service Account (Recommended)

Create a service account in Google Cloud Console
Download the JSON credentials file
Share your sheet with the service account email

client, err := sheetsql.NewClient( ctx, spreadsheetID, option.WithCredentialsFile("credentials.json"), )

Other Authentication Methods

// With credentials JSON directly client, err := sheetsql.NewClient( ctx, spreadsheetID, option.WithCredentialsJSON([]byte(credentialsJSON)), ) // With API key (read-only, public sheets) client, err := sheetsql.NewClient( ctx, spreadsheetID, option.WithAPIKey("your-api-key"), )

The library returns detailed errors for common issues: