herschel

README

Herschel

The Google Spreadsheet Writing / Reading Library for Golang.

Features

• Read table from Google Spreadsheet
• Write table to Google Spreadsheet
• Cell decoration and formatting
• Configure sheet

Installation

go get github.com/yokoe/herschel

Quick Start

You need service account credentials or user account credentials for api call. https://developers.google.com/sheets/api/guides/authorizing

spreadsheetID := "1234567890...SpreadsheetID"

client, err := herschel.NewClient(option.WithServiceAccountCredentials("service-account.json"))
if err != nil {
    log.Fatalf("invalid config or token %s", err)
}

// Read data from Spreadsheet
table, err := client.ReadTable(spreadsheetID, "Sheet 1")
if err != nil {
    log.Fatalf("read error %s", err)
}
fmt.Print(table.GetValue(0, 0))

// Edit data
table.PutValue(0, 0, "Hello from Herschel")

// Write to Spreadsheet
err = client.WriteTable(spreadsheetID, "Sheet 1", table)
if err != nil {
    log.Fatalf("write error %s", err)
}

How to use

Writing table

client, err := ...

table := NewTable(2, 2)
table.Put(0, 0, "Updated: "+time.Now().Format("2006-01-02 03:04"))
table.Put(1, 0, 1234567890)
table.Put(0, 1, "fuga")
table.Put(1, 1, 0.2530)

table.SetBackgroundColor(0, 0, color.Black)
table.SetBackgroundColor(1, 1, color.RGBA{128, 0, 0, 0})
table.SetNumberFormatPattern(1, 0, "#,###")
table.SetNumberFormatPattern(1, 1, "#.00%")

table.FrozenRowCount = 1
table.FrozenColumnCount = 2

err = client.WriteTable(spreadsheetID, "Sheet 1", table)
if err != nil {
    // Error handling
}

Reading table

client, err := ...

table, err := client.ReadTable(spreadsheetID, "Sheet 1")
if err != nil {
    // Error handling
}

// table.GetValue(0, 0)

Convert table to map

Key comes from first column value, value from second column value.

m := table.ToMap()

log.Printf("key values: %+v\n", m)

Export table as CSV

buf := bytes.NewBufferString("")
if err := table.ToCSV(buf); err != nil {
	// Error handling
}

csvStr = buf.String()

Table manipulation

Get / Put

table.PutValue(0, 0, "Hello world")
table.PutValuesAtRow(1, "Hello", "World")

table.GetValue(0, 0) // "Hello world"
table.GetStringValue(0, 0) // "Hello world"
table.GetValuesAtRow(1) // "Hello", "World"

Finding row

table.PutValuesAtRow(0, "a", "b", "c")
table.PutValuesAtRow(1, "d", "e", "f")
table.PutValuesAtRow(2, "g", "h", "i")

table.IndexOfRowWithPrefix("d", "e") // 1
table.IndexOfRowWithPrefix("d") // 1
table.IndexOfRowWithPrefix("b") // -1

Freeze rows / cols

table.FrozenRowCount = 1
table.FrozenColumnCount = 2

Removing row / col

table.RemoveRowAtIndex(3)

Sheet manipulation

client.AddSheet("spreadsheetID", "NewSheet")
client.DeleteSheet("spreadsheetID", "NewSheet")
client.RecreateSheet("spreadsheetID", "NewSheet")

Worksheet manipulation

id, err := client.CreateNewSpreadsheet(config, token, "NewWorksheet")

Authentication

You can authenticate using service accounts or user accounts.

client, err := NewClient(option.WithServiceAccountCredentials(credentialsFilePath))
client, err := NewClient(option.WithConfigFileAndTokenFile(configFilePath, userCredentialsFilePath))

Development

Run testcases with api call

Testcases require api access to spreadsheet will be skipped in default. To run all testcases, please set service account credentials json file to SPREADSHEET_CREDENTIAL_FILE.

SPREADSHEET_CREDENTIAL_FILE=/path/to/credentials.json go test . -v -cover

Links

• Go言語で、Google Spreadsheet上のデータを取得する(Japanese)

Documentation

Index

• type Client
  • func NewClient(option option.ClientOption) (*Client, error)
  • func (client Client) AddSheet(spreadsheetID string, sheetTitle string) error
  • func (client Client) ClearSheetValues(spreadsheetID string, sheetTitle string) error
  • func (c *Client) CreateNewSpreadsheet(title string) (string, error)
  • func (client Client) DeleteSheet(spreadsheetID string, sheetTitle string) error
  • func (client *Client) Read(spreadsheetID string, sheetTitle string) ([][]interface{}, error)
  • func (client *Client) ReadTable(spreadsheetID string, sheetTitle string) (*Table, error)
  • func (client Client) RecreateSheet(spreadsheetID string, sheetTitle string) error
  • func (client Client) UpdateSheetGridLimits(spreadsheetID string, sheetTitle string, rows int, columns int) error
  • func (client Client) Write(spreadsheetID string, sheetTitle string, values [][]interface{}) error
  • func (client Client) WriteTable(spreadsheetID string, sheetTitle string, table *Table) error
• type Table
  • func NewTable(rows int, cols int) *Table
  • func (t *Table) AppendTableAtBottom(a *Table) *Table
  • func (t *Table) AppendTableAtRight(a *Table) *Table
  • func (t *Table) ClearValues() error
  • func (t *Table) ClearValuesInRange(rowStart, colStart, numRows, numCols int) error
  • func (t *Table) GetCols() int
  • func (t *Table) GetInt64Value(row int, col int) int64
  • func (t *Table) GetIntValue(row int, col int) int
  • func (t *Table) GetRows() int
  • func (t *Table) GetStringValue(row int, col int) string
  • func (t *Table) GetValue(row int, col int) interface{}
  • func (t *Table) GetValuesAtRow(row int) []interface{}
  • func (t *Table) IndexOfRowWithPrefix(values ...interface{}) int
  • func (t *Table) InsertColAtIndex(index int) error
  • func (t *Table) PutCommaSeparatedInt64(row int, col int, value int64)
  • func (t *Table) PutValue(row int, col int, value interface{})
  • func (t *Table) PutValuesAtRow(row int, values ...interface{})
  • func (t *Table) RemoveColAtIndex(index int) error
  • func (t *Table) SetBackgroundColor(row int, col int, c color.Color)
  • func (t *Table) SetNumberFormatPattern(row int, col int, pattern string)
  • func (t *Table) SetNumberFormatType(row int, col int, formatType string)
  • func (t Table) String() string
  • func (t *Table) SubTable(rowStart, colStart, numRows, numCols int) (*Table, error)
  • func (t *Table) SubTableByFilteringRows(f func(values []interface{}) bool) *Table
  • func (t *Table) ToCSV(w io.Writer) error
  • func (t *Table) ToMap() map[string]interface{}
  • func (t *Table) Values() [][]interface{}

Types

type Client

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

Client provides methods to manipulate spreadsheets.

func NewClient

func NewClient(option option.ClientOption) (*Client, error)

NewClient returns a new instance

func (Client) AddSheet

func (client Client) AddSheet(spreadsheetID string, sheetTitle string) error

AddSheet adds new sheet with title

func (Client) ClearSheetValues

func (client Client) ClearSheetValues(spreadsheetID string, sheetTitle string) error

ClearSheetValues clears values of sheet.

func (*Client) CreateNewSpreadsheet

func (c *Client) CreateNewSpreadsheet(title string) (string, error)

CreateNewSpreadsheet creates new spreadsheet

func (Client) DeleteSheet

func (client Client) DeleteSheet(spreadsheetID string, sheetTitle string) error

DeleteSheet deletes a sheet with title.

func (*Client) Read

func (client *Client) Read(spreadsheetID string, sheetTitle string) ([][]interface{}, error)

Read returns a slice of cell values in sheet.

func (*Client) ReadTable

func (client *Client) ReadTable(spreadsheetID string, sheetTitle string) (*Table, error)

ReadTable returns a table with values read from the spreadsheet set.

func (Client) RecreateSheet

func (client Client) RecreateSheet(spreadsheetID string, sheetTitle string) error

RecreateSheet deletes a sheet with title and adds new one.

func (Client) UpdateSheetGridLimits

func (client Client) UpdateSheetGridLimits(spreadsheetID string, sheetTitle string, rows int, columns int) error

UpdateSheetGridLimits updates grid limits of sheet.

func (Client) Write

func (client Client) Write(spreadsheetID string, sheetTitle string, values [][]interface{}) error

Write writes values to spreadsheet

func (Client) WriteTable

func (client Client) WriteTable(spreadsheetID string, sheetTitle string, table *Table) error

WriteTable writes values of table to spreadsheet

type Table

type Table struct {
	FrozenRowCount    int64
	FrozenColumnCount int64
	// contains filtered or unexported fields
}

Table represents 2 dimension cells.

func NewTable

func NewTable(rows int, cols int) *Table

NewTable returns instance of Table.

func (*Table) AppendTableAtBottom

func (t *Table) AppendTableAtBottom(a *Table) *Table

AppendTableAtBottom returns new instance of table appending another table.

func (*Table) AppendTableAtRight

func (t *Table) AppendTableAtRight(a *Table) *Table

AppendTableAtRight returns new instance of table appending another table to the right.

func (*Table) ClearValues

func (t *Table) ClearValues() error

ClearValues clears all values of table.

func (*Table) ClearValuesInRange

func (t *Table) ClearValuesInRange(rowStart, colStart, numRows, numCols int) error

ClearValuesInRange clears the value of a cell in the specified range.

func (*Table) GetCols

func (t *Table) GetCols() int

GetCols returns numfer of cols

func (*Table) GetInt64Value

func (t *Table) GetInt64Value(row int, col int) int64

GetInt64Value returns value of cell as int.

func (*Table) GetIntValue

func (t *Table) GetIntValue(row int, col int) int

GetIntValue returns value of cell as int.

func (*Table) GetRows

func (t *Table) GetRows() int

GetRows returns number of rows

func (*Table) GetStringValue

func (t *Table) GetStringValue(row int, col int) string

GetStringValue returns value of cell as string.

func (*Table) GetValue

func (t *Table) GetValue(row int, col int) interface{}

GetValue returns value of cell.

func (*Table) GetValuesAtRow

func (t *Table) GetValuesAtRow(row int) []interface{}

GetValuesAtRow returns a slice containing value of cells at row

func (*Table) IndexOfRowWithPrefix

func (t *Table) IndexOfRowWithPrefix(values ...interface{}) int

IndexOfRowWithPrefix returns index of row which contains values as prefix. Returns -1 when there is no row matches.

func (*Table) InsertColAtIndex

func (t *Table) InsertColAtIndex(index int) error

InsertColAtIndex inserts new column at index

func (*Table) PutCommaSeparatedInt64

func (t *Table) PutCommaSeparatedInt64(row int, col int, value int64)

PutCommaSeparatedInt64 set value of cell at (row, col) as comma separated integer.

func (*Table) PutValue

func (t *Table) PutValue(row int, col int, value interface{})

PutValue updates value of cell.

func (*Table) PutValuesAtRow

func (t *Table) PutValuesAtRow(row int, values ...interface{})

PutValuesAtRow sets values of cells at row

func (*Table) RemoveColAtIndex

func (t *Table) RemoveColAtIndex(index int) error

RemoveColAtIndex removes column at index

func (*Table) SetBackgroundColor

func (t *Table) SetBackgroundColor(row int, col int, c color.Color)

SetBackgroundColor sets background color of cell at (row, col)

func (*Table) SetNumberFormatPattern

func (t *Table) SetNumberFormatPattern(row int, col int, pattern string)

SetNumberFormatPattern sets number format pettern of cell at (row, col)

func (*Table) SetNumberFormatType

func (t *Table) SetNumberFormatType(row int, col int, formatType string)

SetNumberFormatType sets number format type of cell at (row, col)

func (Table) String

func (t Table) String() string

func (*Table) SubTable

func (t *Table) SubTable(rowStart, colStart, numRows, numCols int) (*Table, error)

SubTable returns new instance of table with sliced cells copied from original table

func (*Table) SubTableByFilteringRows

func (t *Table) SubTableByFilteringRows(f func(values []interface{}) bool) *Table

SubTableByFilteringRows returns new instance of table with filtered rows from original table.

func (*Table) ToCSV

func (t *Table) ToCSV(w io.Writer) error

ToCSV writes table in csv format

func (*Table) ToMap

func (t *Table) ToMap() map[string]interface{}

ToMap creates map from table. First column value as key, second column value as value.

func (*Table) Values

func (t *Table) Values() [][]interface{}

Values returns slice of cell values