gsheets

README

gsheets

A golang wrapper package for golang.org/x/oauth2 and google.golang.org/api/sheets/v4. You can easily manipulate spreadsheets.

!!! Only for personal use !!!

Installation

go get github.com/delve/gsheets

Requirement

This package uses Google OAuth2.0. So before executing tool, you have to prepare credentials.json. See Go Quickstart, or Blog (Japanese) for the details.

Usage

Create Cache

If you want to use the cache, initialize the context. If you are updating sheets, you should not use Cache.

ctx := gsheets.WithCache(ctx)

Create New Client

client, err := gsheets.New(ctx, `{"credentials": "json"}`, `{"token": "json"}`)

client, err := gsheets.NewForCLI(ctx, "credentials.json")

If you are updating sheets, create a client with ClientWritable option.

client, err := gsheets.New(ctx, `{"credentials": "json"}`, `{"token": "json"}`, gsheets.ClientWritable())

Get Sheet Information

func (*Client) GetTitle(ctx context.Context, spreadsheetID string) (string, error)

func (*Client) GetSheetNames(ctx context.Context, spreadsheetID string) ([]string, error)

func (*Client) GetSheet(ctx context.Context, spreadsheetID, sheetName string) (Sheet, error)

Update Sheet Values

func (c *Client) Update(ctx context.Context, spreadsheetID, sheetName string, rowNo int, values []interface{}) error

func (c *Client) BatchUpdate(ctx context.Context, spreadsheetID string, updateValues ...UpdateValue) error

Manipulate Sheet Values

If the index is out of range, Value method returns empty string.

s, err := client.GetSheet(ctx, "spreadsheetID", "sheetName")
if err != nil {
  return err
}

fmt.Println(s.Value(row, clm))

for _, r := range s.Rows() {
  fmt.Println(r.Value(clm))
}

Documentation

Overview

Package gsheets is a wrapper package for `golang.org/x/oauth2` and `google.golang.org/api/sheets/v4`. This package is supported only for personal use.

Example

package main

import (
	"context"
	"fmt"
	"os"

	"github.com/delve/gsheets"
)

func main() {

	// https://docs.google.com/spreadsheets/d/1-kaYsJxISA2MJMKtTIzWwOFsMm9Wp21U3Nbqnfru65w
	spreadsheetID := "1-kaYsJxISA2MJMKtTIzWwOFsMm9Wp21U3Nbqnfru65w"

	ctx := gsheets.WithCache(context.Background())
	client, err := gsheets.New(ctx,
		os.Getenv("GOOGLE_API_CREDENTIALS"), os.Getenv("GOOGLE_API_TOKEN"))
	if err != nil {
		panic(err)
	}

	title, err := client.GetTitle(ctx, spreadsheetID)
	if err != nil {
		panic(err)
	}
	fmt.Printf("title: %s\n", title)

	sheetNames, err := client.GetSheetNames(ctx, spreadsheetID)
	if err != nil {
		panic(err)
	}

	for _, sheetName := range sheetNames {
		sheet, err := client.GetSheet(ctx, spreadsheetID, sheetName)
		if err != nil {
			panic(err)
		}
		fmt.Printf("sheetName: %s, A1: %s\n", sheetName, sheet.Value(0, 0))
	}

}

Output:

title: test-sheet
sheetName: foo, A1: This value is written in A1 of the foo sheet.
sheetName: bar, A1: This value is written in A1 of the bar sheet.

Index

• func ClientWritable() func(c *Client) *Client
• func WithCache(ctx context.Context) context.Context
• type Client
  • func New(ctx context.Context, opts ...option.ClientOption) (*Client, error)
  • func NewForCLI(ctx context.Context, authFile string, opts ...ClientOption) (*Client, error)
  • func (c *Client) BatchUpdate(ctx context.Context, spreadsheetID string, updateValues ...UpdateValue) error
  • func (c *Client) GetSheet(ctx context.Context, spreadsheetID, sheetName string) (*Sheet, error)
  • func (c *Client) GetSheetNames(ctx context.Context, spreadsheetID string) ([]string, error)
  • func (c *Client) GetTitle(ctx context.Context, spreadsheetID string) (string, error)
  • func (c *Client) Update(ctx context.Context, spreadsheetID, sheetName string, rowNo int, ...) error
• type ClientOption
• type Row
  • func (r *Row) Value(clm int) string
• type Sheet
  • func NewSheet(t *testing.T, values [][]interface{}) *Sheet
  • func (s *Sheet) Rows() []sheets.Row
  • func (s *Sheet) Value(row, clm int) string
• type UpdateValue

Examples

• Package
• Client.BatchUpdate

Functions

func ClientWritable

func ClientWritable() func(c *Client) *Client

ClientWritable is an option to change client writable.

func WithCache

func WithCache(ctx context.Context) context.Context

WithCache returns a context with gsheets cache. If you want to use the cache, initialize the context.

Types

type Client

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

Client is a gsheets client.

func New

func New(ctx context.Context, opts ...option.ClientOption) (*Client, error)

New returns a gsheets client.

func NewForCLI

func NewForCLI(ctx context.Context, authFile string, opts ...ClientOption) (*Client, error)

TODO: Convert this to the way New() works with direct google clientoptions NewForCLI returns a gsheets client. This function is intended for CLI tools.

func (*Client) BatchUpdate

func (c *Client) BatchUpdate(ctx context.Context, spreadsheetID string, updateValues ...UpdateValue) error

BatchUpdate updates multiple rows.

Example

package main

import (
	"context"
	"fmt"
	"os"

	"github.com/delve/gsheets"
)

func main() {

	// https://docs.google.com/spreadsheets/d/1-kaYsJxISA2MJMKtTIzWwOFsMm9Wp21U3Nbqnfru65w
	spreadsheetID := "1-kaYsJxISA2MJMKtTIzWwOFsMm9Wp21U3Nbqnfru65w"

	ctx := context.Background()
	client, err := gsheets.New(ctx,
		os.Getenv("GOOGLE_API_CREDENTIALS"), os.Getenv("GOOGLE_API_TOKEN"),
		gsheets.ClientWritable())
	if err != nil {
		panic(err)
	}

	// Update
	data := []gsheets.UpdateValue{
		{SheetName: "foo", RowNo: 2, Values: []interface{}{"a", "b", "c"}},
		{SheetName: "bar", RowNo: 2, Values: []interface{}{"1", "2", "3"}},
	}

	err = client.BatchUpdate(ctx, spreadsheetID, data...)
	if err != nil {
		fmt.Println(err)
		return
	}

	// Check
	sheetNames, err := client.GetSheetNames(ctx, spreadsheetID)
	if err != nil {
		panic(err)
	}

	for _, sheetName := range sheetNames {
		sheet, err := client.GetSheet(ctx, spreadsheetID, sheetName)
		if err != nil {
			panic(err)
		}
		fmt.Printf("%s: %s, %s, %s\n", sheetName, sheet.Value(2, 0), sheet.Value(2, 1), sheet.Value(2, 2))
	}

	// Clear
	data = []gsheets.UpdateValue{
		{SheetName: "foo", RowNo: 2, Values: []interface{}{"", "", ""}},
		{SheetName: "bar", RowNo: 2, Values: []interface{}{"", "", ""}},
	}

	err = client.BatchUpdate(ctx, spreadsheetID, data...)
	if err != nil {
		fmt.Println(err)
		return
	}

}

Output:

foo: a, b, c
bar: 1, 2, 3

func (*Client) GetSheet

func (c *Client) GetSheet(ctx context.Context, spreadsheetID, sheetName string) (*Sheet, error)

GetSheet returns a Sheet.

func (*Client) GetSheetNames

func (c *Client) GetSheetNames(ctx context.Context, spreadsheetID string) ([]string, error)

GetSheetNames returns sheet name list.

func (*Client) GetTitle

func (c *Client) GetTitle(ctx context.Context, spreadsheetID string) (string, error)

GetTitle returns sheet title.

func (*Client) Update

func (c *Client) Update(ctx context.Context, spreadsheetID, sheetName string, rowNo int, values []interface{}) error

Update updates a single row. `rowNo` is started from zero.

type ClientOption

type ClientOption func(c *Client) *Client

Type and optionFunc are now only used in NewForCli, which i am not using atm ClientOption is an option function.

type Row

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

Row is a row of google spreadsheets.

func (*Row) Value

func (r *Row) Value(clm int) string

Value returns the string value.

type Sheet

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

Sheet is a sheet of google spreadsheets.

func NewSheet

func NewSheet(t *testing.T, values [][]interface{}) *Sheet

NewSheet returns a new sheet instance with argument values. This function is for testing.

func (*Sheet) Rows

func (s *Sheet) Rows() []sheets.Row

Rows returns rows which the sheet has.

func (*Sheet) Value

func (s *Sheet) Value(row, clm int) string

Value returns the string value.

type UpdateValue

type UpdateValue struct {
	SheetName string
	RowNo     int
	Values    []interface{}
}

UpdateValue is data structure for BatchUpdate method. `RowNo` is started from zero.