herschel

package module
v0.0.5 Latest Latest
Warning

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

 Go to latest Published: Jul 4, 2022 License: MIT Imports: 10 Imported by: 0

README

Herschel

The Google Spreadsheet Writing / Reading Library for Golang.

Maintainability

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")

titles, err := client.SheetTitles("spreadsheetID")
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

Documentation

Index

Constants

This section is empty.

Variables

This section is empty.

Functions

This section is empty.

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) SheetTitles added in v0.0.4 

func (client Client) SheetTitles(spreadsheetID string) ([]string, error)

SheetTitles returns a slice of sheet titles.

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 added in v0.0.2 

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

ToCSV writes table in csv format

func (*Table) ToMap added in v0.0.3 

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

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

func (*Table) ToMapSlice added in v0.0.5 

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

ToMapSlice returns a slice of map from table values.

func (*Table) Values 

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

Values returns slice of cell values

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.