sheetkv

package module
v1.0.0 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Jul 2, 2025 License: MIT Imports: 8 Imported by: 0

README

go-sheetkv

A Go library that provides a Key-Value Store (KVS) backed by spreadsheets. Supports both Google Sheets and Excel files.

Features

  • Use Google Sheets and Excel as a KVS backend
  • Fast access with memory caching
  • Automatic synchronization
  • Type-safe API
  • Built-in retry mechanism
  • Multiple authentication methods (Google Sheets)
  • No authentication required for local Excel files

Important Note

⚠️ This package is designed for simple batch processing and does not support concurrent access from multiple processes. All data is cached in memory within each process, and there is no inter-process synchronization mechanism. Using this package from multiple processes simultaneously may result in data inconsistencies.

Installation

go get github.com/ideamans/go-sheetkv

Usage

import (
    sheetkv "github.com/ideamans/go-sheetkv"
    "github.com/ideamans/go-sheetkv/adapters/googlesheets"
    "github.com/ideamans/go-sheetkv/adapters/excel"
)
Basic Example
package main

import (
    "context"
    "log"
    "time"
    
    sheetkv "github.com/ideamans/go-sheetkv"
    "github.com/ideamans/go-sheetkv/adapters/googlesheets"
)

func main() {
    ctx := context.Background()
    
    // Configure and create adapter
    adapterConfig := googlesheets.Config{
        SpreadsheetID: "your-spreadsheet-id",
        SheetName:     "users",
    }
    adapter, err := googlesheets.NewWithJSONKeyFile(ctx, adapterConfig, "./credentials.json")
    if err != nil {
        log.Fatal(err)
    }

    // Create client with recommended defaults for Google Sheets
    clientConfig := googlesheets.DefaultClientConfig()
    // Optionally customize:
    // clientConfig.SyncInterval = 30 * time.Second
    
    client := sheetkv.New(adapter, clientConfig)
    
    // Initialize and load existing data
    if err := client.Initialize(ctx); err != nil {
        log.Fatal(err)
    }
    defer client.Close()

    // Append a record
    record := &sheetkv.Record{
        Values: map[string]interface{}{
            "name": "John Doe",
            "age":  25,
        },
    }
    err = client.Append(record)
    if err != nil {
        log.Fatal(err)
    }

    // Query records
    results, err := client.Query(sheetkv.Query{
        Conditions: []sheetkv.Condition{
            {Column: "age", Operator: ">=", Value: 20},
        },
    })
    if err != nil {
        log.Fatal(err)
    }

    // Display results
    for _, r := range results {
        name := r.GetAsString("name", "")
        age := r.GetAsInt64("age", 0)
        log.Printf("Row %d: %s (age: %d)", r.Key, name, age)
    }
}
Using Excel
package main

import (
    "context"
    "log"
    "time"
    
    sheetkv "github.com/ideamans/go-sheetkv"
    "github.com/ideamans/go-sheetkv/adapters/excel"
)

func main() {
    // Configure Excel adapter (no authentication required)
    adapterConfig := &excel.Config{
        FilePath:  "./data.xlsx",
        SheetName: "users",
    }
    adapter, err := excel.New(adapterConfig)
    if err != nil {
        log.Fatal(err)
    }

    // Create client with recommended defaults for Excel
    client := sheetkv.New(adapter, excel.DefaultClientConfig())
    
    ctx := context.Background()
    if err := client.Initialize(ctx); err != nil {
        log.Fatal(err)
    }
    defer client.Close()

    // Operations are the same as Google Sheets
}

Authentication

Google Sheets Authentication
1. Service Account JSON File
adapter, err := googlesheets.NewWithJSONKeyFile(ctx, adapterConfig, "./service-account.json")
2. Environment Variable
// Uses GOOGLE_APPLICATION_CREDENTIALS environment variable
adapter, err := googlesheets.NewWithJSONKeyFile(ctx, adapterConfig, "")
3. Service Account Key Direct
adapter, err := googlesheets.NewWithServiceAccountKey(
    ctx, 
    adapterConfig,
    "service-account@project.iam.gserviceaccount.com",
    "-----BEGIN PRIVATE KEY-----\n...",
)

Data Types

Record Values are map[string]interface{}, but type-safe helper methods are provided:

// Getter methods
name := record.GetAsString("name", "default")
age := record.GetAsInt64("age", 0)
price := record.GetAsFloat64("price", 0.0)
active := record.GetAsBool("active", false)
tags := record.GetAsStrings("tags", []string{})
created := record.GetAsTime("created_at", time.Now())

// Setter methods
record.SetString("name", "New Name")
record.SetInt64("age", 30)
record.SetFloat64("price", 1980.0)
record.SetBool("active", true)
record.SetStrings("tags", []string{"tag1", "tag2"})
record.SetTime("updated_at", time.Now())

Queries

Combine multiple conditions for complex queries:

results, err := client.Query(sheetkv.Query{
    Conditions: []sheetkv.Condition{
        {Column: "status", Operator: "==", Value: "active"},
        {Column: "age", Operator: ">=", Value: 18},
        {Column: "age", Operator: "<=", Value: 65},
        {Column: "role", Operator: "in", Value: []interface{}{"admin", "user"}},
    },
    Limit:  10,
    Offset: 0,
})
Supported Operators
  • == : Equal
  • != : Not equal
  • > : Greater than
  • >= : Greater than or equal
  • < : Less than
  • <= : Less than or equal
  • in : In array (value must be an array)
  • between : Between range (value must be [2]interface{})

Spreadsheet Structure

  • Row 1: Column names (schema definition)
  • Row 2+: Data records
  • Keys are row numbers (starting from 2)

Synchronization Strategies

This library implements two synchronization strategies:

Gap-Preserving Sync (Default for Scheduled Sync)
  • Deleted records are synchronized as empty rows
  • Maintains consistency between memory row numbers (keys) and spreadsheet row numbers
  • When appending new records, keys continue incrementing from the highest existing key
  • Used automatically during periodic synchronization
Compacting Sync (Used on Close)
  • Deleted records are removed and remaining data is compacted
  • Provides optimal spreadsheet size by removing empty rows
  • Row numbers in the spreadsheet may not match record keys after sync
  • Automatically removes trailing empty rows to maintain clean data
  • Used automatically when calling Close() to finalize the session

Default Configurations

Google Sheets
  • SyncInterval: 10 seconds
  • MaxRetries: 3
  • RetryInterval: 20 seconds
Excel
  • SyncInterval: 1 second
  • MaxRetries: 3
  • RetryInterval: 5 seconds

Development

Running Tests
# Unit tests only
make test-unit

# Integration tests (requires .env configuration)
make test-integration

# API tests (requires .env configuration)
make test-api

# All tests
make test
Environment Variables

Tests require a .env file:

# For Google Sheets testing
GOOGLE_APPLICATION_CREDENTIALS=./service-account.json
TEST_GOOGLE_SHEET_ID=your-test-spreadsheet-id

# Additional authentication methods (optional)
TEST_CLIENT_EMAIL=service-account@project.iam.gserviceaccount.com
TEST_CLIENT_PRIVATE_KEY=-----BEGIN PRIVATE KEY-----\n...\n-----END PRIVATE KEY-----

# Note:
# - Sheet names are automatically set (integration/api)
# - If Google Sheets credentials are not configured, tests automatically fall back to Excel
# - Excel adapter is always tested
CI/CD

To run tests with Google Sheets in GitHub Actions, configure these repository secrets:

  • SERVICE_ACCOUNT_JSON: Service account JSON file content
  • TEST_CLIENT_EMAIL: Service account email
  • TEST_CLIENT_PRIVATE_KEY: Service account private key
  • TEST_GOOGLE_SHEET_ID: Test spreadsheet ID

See .github/CI_SECRETS.md for details.

License

MIT License

Documentation

Index

Constants

This section is empty.

Variables

View Source 
var (
	ErrKeyNotFound   = errors.New("key not found")
	ErrDuplicateKey  = errors.New("duplicate key")
	ErrSyncFailed    = errors.New("sync failed")
	ErrQuotaExceeded = errors.New("quota exceeded")
)

Functions

func MergeSchemas 

func MergeSchemas(current, sheet []string) []string

MergeSchemas merges current schema with sheet schema preserving order

func ValidateQuery 

func ValidateQuery(query Query) error

ValidateQuery validates query structure

Types

type Adapter 

type Adapter interface {
	// Load retrieves all records and schema from the spreadsheet
	Load(ctx context.Context) ([]*Record, []string, error)

	// Save replaces all data in the spreadsheet with the provided records
	// The strategy parameter determines how deleted records are handled
	Save(ctx context.Context, records []*Record, schema []string, strategy SyncStrategy) error

	// BatchUpdate performs multiple operations in a single request
	BatchUpdate(ctx context.Context, operations []Operation) error
}

Adapter interface defines methods for interacting with different spreadsheet backends

type Cache 

type Cache struct {
	// contains filtered or unexported fields
}

Cache manages in-memory storage of records

func NewCache 

func NewCache() *Cache

NewCache creates a new Cache instance

func (*Cache) Append 

func (c *Cache) Append(record *Record) error

Append adds a new record (fails if key already exists)

func (*Cache) Clear 

func (c *Cache) Clear()

Clear removes all data

func (*Cache) ClearDirty 

func (c *Cache) ClearDirty()

ClearDirty marks all records as clean

func (*Cache) Delete 

func (c *Cache) Delete(key int) error

Delete removes a record

func (*Cache) Get 

func (c *Cache) Get(key int) (*Record, error)

Get retrieves a record by key (row number)

func (*Cache) GetAllRecords 

func (c *Cache) GetAllRecords() []*Record

GetAllRecords returns all records sorted by key

func (*Cache) GetDirtyKeys 

func (c *Cache) GetDirtyKeys() []int

GetDirtyKeys returns keys of modified records

func (*Cache) GetSchema 

func (c *Cache) GetSchema() []string

GetSchema returns the current schema

func (*Cache) Load 

func (c *Cache) Load(records []*Record, schema []string)

Load replaces all data with the provided records

func (*Cache) Query 

func (c *Cache) Query(query Query) ([]*Record, error)

Query searches for records matching the given conditions

func (*Cache) Set 

func (c *Cache) Set(key int, record *Record) error

Set stores or updates a record

func (*Cache) SetSchema 

func (c *Cache) SetSchema(schema []string)

SetSchema sets the schema

func (*Cache) Size 

func (c *Cache) Size() int

Size returns the number of records

func (*Cache) Update 

func (c *Cache) Update(key int, updates map[string]interface{}) error

Update partially updates a record

type Client 

type Client struct {
	// contains filtered or unexported fields
}

Client is the main KVS client

func New 

func New(adapter Adapter, config *Config) *Client

New creates a new KVS client with the given adapter and configuration

func (*Client) Append 

func (c *Client) Append(record *Record) error

Append adds a new record

func (*Client) Close 

func (c *Client) Close() error

Close closes the client and ensures final sync

func (*Client) Delete 

func (c *Client) Delete(key int) error

Delete removes a record

func (*Client) Get 

func (c *Client) Get(key int) (*Record, error)

Get retrieves a record by key

func (*Client) Initialize 

func (c *Client) Initialize(ctx context.Context) error

Initialize loads initial data from the adapter

func (*Client) Query 

func (c *Client) Query(query Query) ([]*Record, error)

Query searches for records matching the given conditions

func (*Client) Set 

func (c *Client) Set(key int, record *Record) error

Set stores or updates a record

func (*Client) Sync 

func (c *Client) Sync() error

Sync forces synchronization with the backend

func (*Client) Update 

func (c *Client) Update(key int, updates map[string]interface{}) error

Update partially updates a record

type Condition 

type Condition struct {
	Column   string      // カラム名
	Operator string      // 演算子: ==, !=, >, >=, <, <=, in, between
	Value    interface{} // 比較値（inの場合は[]interface{}, betweenの場合は[2]interface{}）
}

Condition represents a single query condition

type Config 

type Config struct {
	SyncInterval  time.Duration // Interval for periodic sync (default: 30s)
	MaxRetries    int           // Maximum number of retries for API calls (default: 3)
	RetryInterval time.Duration // Base interval between retries for exponential backoff (default: 1s)
}

Config represents configuration for the KVS client

type Operation 

type Operation struct {
	Type   OperationType
	Record *Record
}

Operation represents a single data operation

type OperationType 

type OperationType int

OperationType represents the type of operation 

const (
	OpAdd OperationType = iota
	OpUpdate
	OpDelete
)

type Query 

type Query struct {
	Conditions []Condition // AND条件として評価
	Limit      int
	Offset     int
}

Query represents a query with multiple conditions

type Record 

type Record struct {
	Key    int                    // 行番号 (2から始まる、1行目はカラム定義)
	Values map[string]interface{} // カラム名と値のマップ
}

func ApplyQuery 

func ApplyQuery(records []*Record, query Query) []*Record

ApplyQuery filters records based on query conditions

func (*Record) GetAsBool 

func (r *Record) GetAsBool(col string, defaultValue bool) bool

GetAsBool returns the value as bool or defaultValue if not found

func (*Record) GetAsFloat64 

func (r *Record) GetAsFloat64(col string, defaultValue float64) float64

GetAsFloat64 returns the value as float64 or defaultValue if not found

func (*Record) GetAsInt64 

func (r *Record) GetAsInt64(col string, defaultValue int64) int64

GetAsInt64 returns the value as int64 or defaultValue if not found

func (*Record) GetAsString 

func (r *Record) GetAsString(col string, defaultValue string) string

GetAsString returns the value as string or defaultValue if not found

func (*Record) GetAsStrings 

func (r *Record) GetAsStrings(col string, defaultValue []string) []string

GetAsStrings returns the value as []string or defaultValue if not found

func (*Record) GetAsTime 

func (r *Record) GetAsTime(col string, defaultValue time.Time) time.Time

GetAsTime returns the value as time.Time or defaultValue if not found

func (*Record) MatchesQuery 

func (r *Record) MatchesQuery(query Query) bool

MatchesQuery checks if a record matches all conditions in the query

func (*Record) SetBool 

func (r *Record) SetBool(col string, value bool)

SetBool sets a bool value

func (*Record) SetFloat64 

func (r *Record) SetFloat64(col string, value float64)

SetFloat64 sets a float64 value

func (*Record) SetInt64 

func (r *Record) SetInt64(col string, value int64)

SetInt64 sets an int64 value

func (*Record) SetString 

func (r *Record) SetString(col string, value string)

SetString sets a string value

func (*Record) SetStrings 

func (r *Record) SetStrings(col string, value []string)

SetStrings sets a []string value (stored as comma-separated string)

func (*Record) SetTime 

func (r *Record) SetTime(col string, value time.Time)

SetTime sets a time.Time value (stored as ISO 8601 string)

type SyncManager 

type SyncManager struct {
	// contains filtered or unexported fields
}

SyncManager manages periodic synchronization

func NewSyncManager 

func NewSyncManager(client *Client, interval time.Duration) *SyncManager

NewSyncManager creates a new sync manager

func (*SyncManager) Start 

func (sm *SyncManager) Start()

Start begins the periodic sync process

func (*SyncManager) Stop 

func (sm *SyncManager) Stop()

Stop stops the sync manager and waits for ongoing sync

type SyncStrategy 

type SyncStrategy int

SyncStrategy represents the synchronization strategy 

const (
	// SyncStrategyGapPreserving maintains deleted rows as empty rows to preserve row numbers
	SyncStrategyGapPreserving SyncStrategy = iota
	// SyncStrategyCompacting removes deleted rows and compacts the data
	SyncStrategyCompacting
)

Source Files

View all Source files

Directories

Path Synopsis
adapters
excel
googlesheets
excel command
tests
common

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.