sheetsdb

package module
v0.4.0 Latest Latest
Warning

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

 Go to latest Published: Apr 23, 2026 License: MIT Imports: 6 Imported by: 0

README

sheets-db

Go Reference

Use Google Sheets as a simple database from Go.

Install

go get codeberg.org/emicklei/sheets-db

Features

  • Table lifecycle support: create a sheet-backed table, and load existing table metadata from headers.
  • Record-oriented API with chainable setters and typed helpers for int, bool, float, and day values.
  • Row operations with 1-based data indexes: append, replace, read all, update row, delete row, and clear row.
  • Cell-level operations: update and read a single cell by row index and column name.
  • Query support using a filter function (func(record Record) bool) with nil meaning "match all".
  • Backend abstraction through SheetService, with a Google Sheets implementation included.
  • Create-time row and column trimming (when backend supports it) to remove trailing unused rows and columns.
  • Optional cell-level styling (bold, colors, number formats, …) applied automatically on Append / UpdateRow when the backend implements CellFormatter.

Usage

package main

import (
	"context"
	"fmt"
	"log"
	"time"

	sheetsdb "codeberg.org/emicklei/sheets-db"
	sheetsgoogle "codeberg.org/emicklei/sheets-db/google"
	"google.golang.org/api/option"
	"google.golang.org/api/sheets/v4"
)

func main() {
	ctx := context.Background()

	// Create a Google Sheets API client using a service account credentials file.
	srv, err := sheets.NewService(ctx, option.WithAuthCredentialsFile(option.ServiceAccount,"credentials.json"))
	if err != nil {
		log.Fatalf("create sheets service: %v", err)
	}

	// Wrap it with the sheets-db service.
	service := sheetsgoogle.NewSheetService(srv)

	spreadsheetID := "your-spreadsheet-id"

	// Define a table with column names.
	people := sheetsdb.NewTable("People", spreadsheetID, []string{"name", "age", "active", "score", "joined"})

	// Create the sheet and write the header row.
	if err := people.Create(service); err != nil {
		log.Fatalf("create table: %v", err)
	}

	// Append records.
	alice := sheetsdb.NewRecord().
		Set("name", "Alice").
		Int("age", 30).
		Bool("active", true).
		Float("score", 98.5).
		Day("joined", time.Date(2026, time.April, 1, 0, 0, 0, 0, time.UTC)).
		Bold("name").
		Background("score", sheetsdb.RGB(255, 255, 0))

	bob := sheetsdb.NewRecord().
		Set("name", "Bob").
		Int("age", 25).
		Bool("active", false).
		Float("score", 87.25).
		Day("joined", time.Date(2026, time.April, 2, 0, 0, 0, 0, time.UTC))

	if err := people.Append(service, alice, bob); err != nil {
		log.Fatalf("append: %v", err)
	}

	// Read all records back.
	records, err := people.ReadAll(service)
	if err != nil {
		log.Fatalf("read: %v", err)
	}
	for _, r := range records {
		age, _ := r.GetInt("age")
		active, _ := r.GetBool("active")
		score, _ := r.GetFloat("score")
		joined, _ := r.GetDay("joined")
		fmt.Printf("%s (age %d, active=%t, score=%.2f, joined=%s)\n", r.Get("name"), age, active, score, joined.Format("2006-01-02"))
	}

	// Read a single cell by data row and column.
	age, err := people.ReadCell(service, 1, "age")
	if err != nil {
		log.Fatalf("read cell: %v", err)
	}
	fmt.Println("age of first person:", age)

	// Update the first data row (1-based index).
	updated := sheetsdb.NewRecord().
		Set("name", "Alice").
		Int("age", 31).
		Bool("active", true).
		Float("score", 99.0).
		Day("joined", time.Date(2026, time.April, 1, 0, 0, 0, 0, time.UTC))

	if err := people.UpdateRow(service, 1, updated); err != nil {
		log.Fatalf("update: %v", err)
	}

	// Delete the second data row.
	if err := people.DeleteRow(service, 2); err != nil {
		log.Fatalf("delete: %v", err)
	}

	// Query for matching rows.
	filter := sheetsdb.Filter(func(r sheetsdb.Record) bool {
		return r.Get("age") == "31"
	})
	matched, err := people.Query(service, filter)
	if err != nil {
		log.Fatalf("query: %v", err)
	}
	fmt.Println("matching records:", len(matched))

	// Load an existing table definition from its sheet header.
	loaded, err := sheetsdb.GetTable(service, spreadsheetID, "People")
	if err != nil {
		log.Fatalf("get table: %v", err)
	}
	fmt.Println("loaded columns:", loaded.Columns)
}

Limitations

Google Sheets is not designed to be a database. Use this library for simple reporting or lightweight data collection only.

  • No transactions — there is no concurrency control; concurrent writes may overwrite each other.
  • No ACID guarantees — atomicity, consistency, isolation, and durability are not provided.
  • Full table scans — every query reads the entire sheet, so performance degrades linearly with the number of rows.
  • All values are strings — there is no column typing; numbers, dates, and booleans are stored and returned as strings.
  • Google Sheets API rate limits apply — see the Google Sheets API usage limits.
  • No schema migration — column changes require manual updates to both code and spreadsheet.

Documentation

Index

Constants

View Source 
const (
	HorizontalAlignLeft   = "LEFT"
	HorizontalAlignCenter = "CENTER"
	HorizontalAlignRight  = "RIGHT"

	VerticalAlignTop    = "TOP"
	VerticalAlignMiddle = "MIDDLE"
	VerticalAlignBottom = "BOTTOM"

	WrapStrategyOverflow = "OVERFLOW_CELL"
	WrapStrategyClip     = "CLIP"
	WrapStrategyWrap     = "WRAP"
)

Horizontal and vertical alignment values accepted by CellStyle. These map directly to the Google Sheets API enum values.

Variables

View Source 
var ErrColumnNotFound = errors.New("column not found")

ErrColumnNotFound is returned when a referenced column does not exist in the table.

View Source 
var ErrTableNotFound = errors.New("table not found")

ErrTableNotFound is returned when a requested table (sheet) does not exist.

Functions

This section is empty.

Types

type CellFormatUpdate 

type CellFormatUpdate struct {
	RowIndex int
	ColIndex int
	Style    CellStyle
}

CellFormatUpdate describes a single cell format change at 0-based sheet coordinates. RowIndex 0 is the header row; RowIndex 1 is the first data row.

type CellFormatter 

type CellFormatter interface {
	UpdateCellFormats(spreadsheetID, sheetName string, updates []CellFormatUpdate) error
}

CellFormatter is an optional capability a SheetService may implement to apply cell-level formatting (bold, colors, number formats, etc.). When a backend does not implement CellFormatter, style information on records is silently ignored.

type CellStyle 

type CellStyle struct {
	Bold                *bool
	Italic              *bool
	Underline           *bool
	Strikethrough       *bool
	FontFamily          *string
	FontSize            *int
	TextColor           *Color
	BackgroundColor     *Color
	HorizontalAlignment *string
	VerticalAlignment   *string
	WrapStrategy        *string
	NumberFormat        *NumberFormat
}

CellStyle describes optional visual formatting for a single cell. All fields are pointers so the caller can express "unset" distinctly from a zero value; only the set fields are applied.

func (CellStyle) FieldMask 

func (s CellStyle) FieldMask() string

FieldMask returns a comma-separated Google Sheets API field mask naming the userEnteredFormat subfields that are set on this style. Returns an empty string when the style is empty.

func (CellStyle) IsEmpty 

func (s CellStyle) IsEmpty() bool

IsEmpty reports whether the style has no fields set.

type Color 

type Color struct {
	R, G, B, A float32
}

Color represents an RGBA color with components in the [0,1] range, matching the Google Sheets API color model.

func Hex 

func Hex(s string) (Color, error)

Hex builds a Color from a hex string (e.g. "#ff8800" or "ff8800"). Returns an error for malformed input.

func RGB 

func RGB(r, g, b uint8) Color

RGB builds a Color from 8-bit RGB components with full opacity.

type Filter 

type Filter func(record Record) bool

Filter returns whether a record matches. A nil Filter means all records match.

type NumberFormat 

type NumberFormat struct {
	Type    string
	Pattern string
}

NumberFormat describes how a numeric/date value should be displayed. Type is one of the Google Sheets number format types (e.g. "NUMBER", "CURRENCY", "DATE", "TIME", "DATE_TIME", "PERCENT", "TEXT"). Pattern is an optional format pattern (e.g. "#,##0.00", "yyyy-mm-dd").

type Record 

type Record struct {
	Values   map[string]string
	Styles   map[string]CellStyle
	RowIndex int // 1-based data row index, set when read from a Table
}

Record represents a row of data in a Table. Values are keyed by column name.

func NewRecord 

func NewRecord() Record

NewRecord creates a new empty Record.

func (Record) Background 

func (r Record) Background(column string, color Color) Record

Background sets the background color for the cell at the given column.

func (Record) Bold 

func (r Record) Bold(column string) Record

Bold marks the cell for the given column as bold.

func (Record) Bool added in v0.4.0 

func (r Record) Bool(column string, value bool) Record

Bool sets a boolean value for the given column and returns the Record for chaining.

func (Record) Day added in v0.4.0 

func (r Record) Day(column string, value time.Time) Record

Day sets a date value for the given column in yyyy-mm-dd format and returns the Record for chaining.

func (Record) Float added in v0.4.0 

func (r Record) Float(column string, value float64) Record

Float sets a float value for the given column and returns the Record for chaining.

func (Record) Get 

func (r Record) Get(column string) string

Get returns the value for the given column.

func (Record) GetBool 

func (r Record) GetBool(column string) (bool, error)

GetBool returns the boolean value for the given column.

func (Record) GetDay 

func (r Record) GetDay(column string) (time.Time, error)

GetDay returns the date value for the given column parsed from yyyy-mm-dd format.

func (Record) GetFloat 

func (r Record) GetFloat(column string) (float64, error)

GetFloat returns the float value for the given column.

func (Record) GetInt 

func (r Record) GetInt(column string) (int, error)

GetInt returns the integer value for the given column.

func (Record) GetStyle 

func (r Record) GetStyle(column string) CellStyle

GetStyle returns the CellStyle set for the given column (zero value if none).

func (Record) Int added in v0.4.0 

func (r Record) Int(column string, value int) Record

Int sets an integer value for the given column and returns the Record for chaining.

func (Record) Italic 

func (r Record) Italic(column string) Record

Italic marks the cell for the given column as italic.

func (Record) NumberFormat 

func (r Record) NumberFormat(column string, format NumberFormat) Record

NumberFormat sets the number format for the cell at the given column.

func (Record) Set 

func (r Record) Set(column, value string) Record

Set sets a value for the given column and returns the Record for chaining.

func (Record) Style added in v0.4.0 

func (r Record) Style(column string, style CellStyle) Record

Style sets the full CellStyle for the given column, replacing any previously set style for that column.

func (Record) TextColor 

func (r Record) TextColor(column string, color Color) Record

TextColor sets the foreground (text) color for the cell at the given column.

func (Record) Underline 

func (r Record) Underline(column string) Record

Underline marks the cell for the given column as underlined.

type SheetService 

type SheetService interface {
	// WithContext returns a new SheetService that uses the provided context for all operations.
	WithContext(context.Context) SheetService
	// CreateSheet adds a new sheet (tab) to the spreadsheet.
	CreateSheet(spreadsheetID, title string) error
	// GetRows returns all rows from the given range.
	GetRows(spreadsheetID, readRange string) ([][]any, error)
	// AppendRows appends rows to the given range.
	AppendRows(spreadsheetID, appendRange string, values [][]any) error
	// UpdateRows updates rows at the given range.
	UpdateRows(spreadsheetID, updateRange string, values [][]any) error
	// DeleteRow deletes a single row from the sheet.
	// sheetRowIndex is the 0-based row index within the sheet.
	DeleteRow(spreadsheetID, sheetName string, sheetRowIndex int) error
	// ClearRows clears rows in the given range (blanks content, keeps the row).
	ClearRows(spreadsheetID, clearRange string) error
	// GetSheetNames returns the names of all sheets in the spreadsheet.
	GetSheetNames(spreadsheetID string) ([]string, error)
}

SheetService defines the operations needed to interact with a spreadsheet backend.

type Table 

type Table struct {
	Name          string
	SpreadsheetID string
	Columns       []string
}

Table represents a sheet within a Google Spreadsheet, treating it as a database table.

func GetTable 

func GetTable(service SheetService, spreadsheetID, sheetName string) (*Table, error)

GetTable retrieves an existing Table by reading its header row from the given sheet. Returns ErrTableNotFound if the sheet does not exist.

func NewTable 

func NewTable(name, spreadsheetID string, columns []string) *Table

NewTable creates a new Table.

func (*Table) Append 

func (t *Table) Append(service SheetService, records ...Record) error

Append adds one or more records as new rows at the end of the sheet. Returns ErrColumnNotFound if any record contains a column that is not defined in the table.

func (*Table) ClearRow 

func (t *Table) ClearRow(service SheetService, rowIndex int) error

ClearRow blanks the data row at the given 1-based row index. The row remains in the sheet but its contents are removed.

func (*Table) Create 

func (t *Table) Create(service SheetService) error

Create creates the sheet in the spreadsheet and writes the header row.

func (*Table) DeleteRow 

func (t *Table) DeleteRow(service SheetService, rowIndex int) error

DeleteRow removes the data row at the given 1-based row index.

func (*Table) Query 

func (t *Table) Query(service SheetService, filter Filter) ([]Record, error)

Query returns all records that match the filter. Each returned Record has its RowIndex set.

func (*Table) ReadAll 

func (t *Table) ReadAll(service SheetService) ([]Record, error)

ReadAll returns all records from the sheet (excluding the header row).

func (*Table) ReadCell 

func (t *Table) ReadCell(service SheetService, rowIndex int, column string) (string, error)

ReadCell reads a single cell identified by the 1-based data row index and column name.

func (*Table) Replace 

func (t *Table) Replace(service SheetService, records ...Record) error

Replace removes all existing data rows from the sheet and writes the given records as the new contents. The header row is preserved. Returns ErrColumnNotFound if any record contains a column that is not defined in the table.

func (*Table) UpdateCell 

func (t *Table) UpdateCell(service SheetService, rowIndex int, column, value string) error

UpdateCell updates a single cell in the sheet identified by the 1-based data row index and column name.

func (*Table) UpdateCellStyle 

func (t *Table) UpdateCellStyle(service SheetService, rowIndex int, column string, style CellStyle) error

UpdateCellStyle applies the given style to a single cell identified by the 1-based data row index and column name. The backend must implement CellFormatter; otherwise the call is a no-op.

func (*Table) UpdateRow 

func (t *Table) UpdateRow(service SheetService, rowIndex int, record Record) error

UpdateRow replaces the data row at the given 1-based row index (row 1 is the first data row, not the header). Returns ErrColumnNotFound if the record contains a column that is not defined in the table.

Source Files

View all Source files

Directories

Path Synopsis

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.