spreadsheet

package module
v2.0.0-...-7677e81 Latest Latest
Warning

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

 Go to latest Published: Sep 15, 2023 License: MIT Imports: 14 Imported by: 0

README

spreadsheet

Build Status Coverage Status GoReport GoDoc License

Package spreadsheet provides fast and easy-to-use access to the Google Sheets API for reading and updating spreadsheets.

Any pull-request is welcome.

Installation

go get gopkg.in/Iwark/spreadsheet.v2

Preparation

This package uses oauth2 client for authentication. You need to get service account key from Google Developer Console. Place the client_secret.json to the root of your project.

Usage

First you need service to start using this package.

data, err := ioutil.ReadFile("client_secret.json")
checkError(err)

conf, err := google.JWTConfigFromJSON(data, spreadsheet.Scope)
checkError(err)

client := conf.Client(context.TODO())
service := spreadsheet.NewServiceWithClient(client)

Or there is a shortcut which does the same things:

service, err := spreadsheet.NewService()
Fetching a spreadsheet
spreadsheetID := "1mYiA2T4_QTFUkAXk0BE3u7snN2o5FgSRqxmRrn_Dzh4"
spreadsheet, err := service.FetchSpreadsheet(spreadsheetID)
Create a spreadsheet
ss, err := service.CreateSpreadsheet(spreadsheet.Spreadsheet{
	Properties: spreadsheet.Properties{
		Title: "spreadsheet title",
	},
})
Find a sheet
// get a sheet by the index.
sheet, err := spreadsheet.SheetByIndex(0)

// get a sheet by the ID.
sheet, err := spreadsheet.SheetByID(0)

// get a sheet by the title.
sheet, err := spreadsheet.SheetByTitle("SheetTitle")
Get cells
// get the B1 cell content
sheet.Rows[0][1].Value

// get the A2 cell content
sheet.Columns[0][1].Value
Update cell content
row := 1
column := 2
sheet.Update(row, column, "hogehoge")
sheet.Update(3, 2, "fugafuga")

// Make sure call Synchronize to reflect the changes.
err := sheet.Synchronize()
Expand a sheet
err := service.ExpandSheet(sheet, 20, 10) // Expand the sheet to 20 rows and 10 columns
Delete Rows / Columns
err := sheet.DeleteRows(0, 3) // Delete first three rows in the sheet

err := sheet.DeleteColumns(1, 4) // Delete columns B:D

More usage can be found at the godoc.

Example

package main

import (
	"fmt"
	"io/ioutil"

	"gopkg.in/Iwark/spreadsheet.v2"
	"golang.org/x/net/context"
	"golang.org/x/oauth2/google"
)

func main() {
	data, err := ioutil.ReadFile("client_secret.json")
	checkError(err)
	conf, err := google.JWTConfigFromJSON(data, spreadsheet.Scope)
	checkError(err)
	client := conf.Client(context.TODO())

	service := spreadsheet.NewServiceWithClient(client)
	spreadsheet, err := service.FetchSpreadsheet("1mYiA2T4_QTFUkAXk0BE3u7snN2o5FgSRqxmRrn_Dzh4")
	checkError(err)
	sheet, err := spreadsheet.SheetByIndex(0)
	checkError(err)
	for _, row := range sheet.Rows {
		for _, cell := range row {
			fmt.Println(cell.Value)
		}
	}

	// Update cell content
	sheet.Update(0, 0, "hogehoge")

	// Make sure call Synchronize to reflect the changes
	err = sheet.Synchronize()
	checkError(err)
}

func checkError(err error) {
	if err != nil {
		panic(err.Error())
	}
}

License

Spreadsheet is released under the MIT License.

Documentation

Index

Constants

View Source 
const (

	// Scope is the API scope for viewing and managing your Google Spreadsheet data.
	// Useful for generating JWT values.
	Scope = "https://spreadsheets.google.com/feeds"

	// SecretFileName is used to get client.
	SecretFileName = "client_secret.json"
)

Variables

This section is empty.

Functions

This section is empty.

Types

type Cell 

type Cell struct {
	Row    uint
	Column uint
	Value  string
	Note   string
	// contains filtered or unexported fields
}

Cell describes a cell data

func (*Cell) EffectiveValue 

func (cell *Cell) EffectiveValue() ExtendedValue

EffectiveValue is the effective value of a cell. Cells with formulas will return the value of that formula.

func (*Cell) Pos 

func (cell *Cell) Pos() string

Pos returns the cell's position like "A1"

func (*Cell) RawValue 

func (cell *Cell) RawValue() ExtendedValue

RawValue returns the raw value of a cell as entered by a user. Cells with formulas, for example, return the formula rather than the value of that formula.

type CellData 

type CellData struct {
	UserEnteredValue ExtendedValue `json:"userEnteredValue"`
	EffectiveValue   ExtendedValue `json:"effectiveValue"`
	FormattedValue   string        `json:"formattedValue"`
	// UserEnteredFormat *CellFormat `json:"userEnteredFormat"`
	// EffectiveFormat *CellFormat `json:"effectiveFormat"`
	Hyperlink string `json:"hyperlink"`
	Note      string `json:"note"`
}

CellData is data about a specific cell.

type DimensionProperties 

type DimensionProperties struct {
	HiddenByFilter bool `json:"hiddenByFilter"`
	HiddenByUser   bool `json:"hiddenByUser"`
	PixelSize      uint `json:"pixelSize"`
}

DimensionProperties is properties about a dimension.

type ErrorValue 

type ErrorValue struct {
	Type    string `json:"type"`
	Message string `json:"message"`
}

ErrorValue is an error in a cell.

type ExtendedValue 

type ExtendedValue struct {
	NumberValue  float64    `json:"numberValue"`
	StringValue  string     `json:"stringValue"`
	BoolValue    bool       `json:"boolValue"`
	FormulaValue string     `json:"formulaValue"`
	ErrorValue   ErrorValue `json:"errorValue"`
}

ExtendedValue is the kinds of value that a cell in a spreadsheet can have.

type FetchSpreadsheetOption 

type FetchSpreadsheetOption func(*spreadsheetConfig)

FetchSpreadsheetOption is the option for FetchSpreadsheet function

func WithCache 

func WithCache(interval time.Duration) FetchSpreadsheetOption

WithCache gives a cacheInterval option for FetchSpreadsheet function

type GridData 

type GridData struct {
	StartRow       uint                   `json:"startRow"`
	StartColumn    uint                   `json:"startColumn"`
	RowData        []RowData              `json:"rowData"`
	RowMetadata    []*DimensionProperties `json:"rowMetadata"`
	ColumnMetadata []*DimensionProperties `json:"columnMetadata"`
}

GridData is data in the grid, as well as metadata about the dimensions.

type GridProperties 

type GridProperties struct {
	RowCount          uint `json:"rowCount"`
	ColumnCount       uint `json:"columnCount"`
	FrozenRowCount    uint `json:"frozenRowCount"`
	FrozenColumnCount uint `json:"frozenColumnCount"`
	HideGridlines     bool `json:"hideGridlines"`
}

GridProperties is properties of a grid.

type Properties 

type Properties struct {
	Title      string `json:"title"`
	Locale     string `json:"locale"`
	AutoRecalc string `json:"autoRecalc"`
	TimeZone   string `json:"timezone"`
}

Properties is properties of a spreadsheet.

type RowData 

type RowData struct {
	Values []CellData `json:"values"`
}

RowData is data about each cell in a row.

type Service 

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

Service represents a Sheets API service instance. Service is the main entry point into using this package.

func NewService 

func NewService() (s *Service, err error)

NewService makes a new service with the secret file.

func NewServiceWithClient 

func NewServiceWithClient(client *http.Client) *Service

NewServiceWithClient makes a new service by the client.

func (*Service) AddSheet 

func (s *Service) AddSheet(spreadsheet *Spreadsheet, sheetProperties SheetProperties) (err error)

AddSheet adds a sheet

func (*Service) CreateSpreadsheet 

func (s *Service) CreateSpreadsheet(spreadsheet Spreadsheet) (resp Spreadsheet, err error)

CreateSpreadsheet creates a spreadsheet with the given title

func (*Service) DeleteColumns 

func (s *Service) DeleteColumns(sheet *Sheet, start, end int) (err error)

DeleteColumns deletes columns from the sheet

func (*Service) DeleteRows 

func (s *Service) DeleteRows(sheet *Sheet, start, end int) (err error)

DeleteRows deletes rows from the sheet

func (*Service) DeleteSheet 

func (s *Service) DeleteSheet(spreadsheet *Spreadsheet, sheetID uint) (err error)

DeleteSheet deletes the sheet

func (*Service) DuplicateSheet 

func (s *Service) DuplicateSheet(spreadsheet *Spreadsheet, sheet *Sheet, index int, title string) (err error)

DuplicateSheet duplicates the contents of a sheet

func (*Service) ExpandSheet 

func (s *Service) ExpandSheet(sheet *Sheet, row, column uint) (err error)

ExpandSheet expands the range of the sheet

func (*Service) FetchSpreadsheet 

func (s *Service) FetchSpreadsheet(id string, options ...FetchSpreadsheetOption) (spreadsheet Spreadsheet, err error)

FetchSpreadsheet fetches the spreadsheet by the id.

func (*Service) ReloadSpreadsheet 

func (s *Service) ReloadSpreadsheet(spreadsheet *Spreadsheet) (err error)

ReloadSpreadsheet reloads the spreadsheet

func (*Service) SyncSheet 

func (s *Service) SyncSheet(sheet *Sheet) (err error)

SyncSheet updates sheet

type Sheet 

type Sheet struct {
	Properties SheetProperties `json:"properties"`
	Data       SheetData       `json:"data"`

	Spreadsheet *Spreadsheet `json:"-"`
	Rows        [][]Cell     `json:"-"`
	Columns     [][]Cell     `json:"-"`
	// contains filtered or unexported fields
}

Sheet is a sheet in a spreadsheet.

func (*Sheet) DeleteColumns 

func (sheet *Sheet) DeleteColumns(start, end int) (err error)

DeleteColumns deletes columns from the sheet

func (*Sheet) DeleteRows 

func (sheet *Sheet) DeleteRows(start, end int) (err error)

DeleteRows deletes rows from the sheet

func (*Sheet) Synchronize 

func (sheet *Sheet) Synchronize() (err error)

Synchronize reflects the changes of the sheet.

func (*Sheet) UnmarshalJSON 

func (sheet *Sheet) UnmarshalJSON(data []byte) error

UnmarshalJSON embeds rows and columns to the sheet.

func (*Sheet) Update 

func (sheet *Sheet) Update(row, column int, val string)

Update updates cell changes

func (*Sheet) UpdateNote 

func (sheet *Sheet) UpdateNote(row, column int, note string)

UpdateNote updates a cell's note

type SheetData 

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

SheetData is data of the sheet

func (*SheetData) UnmarshalJSON 

func (d *SheetData) UnmarshalJSON(data []byte) error

UnmarshalJSON let SheetData to be unmarshaled

type SheetProperties 

type SheetProperties struct {
	ID             uint           `json:"sheetId,omitempty"`
	Title          string         `json:"title,omitempty"`
	Index          uint           `json:"index,omitempty"`
	SheetType      string         `json:"sheetType,omitempty"`
	GridProperties GridProperties `json:"gridProperties,omitempty"`
	Hidden         bool           `json:"hidden,omitempty"`
	TabColor       TabColor       `json:"tabColor,omitempty"`
	RightToLeft    bool           `json:"rightToLeft,omitempty"`
}

SheetProperties is properties of a sheet.

type Spreadsheet 

type Spreadsheet struct {
	ID         string     `json:"spreadsheetId"`
	Properties Properties `json:"properties"`
	Sheets     []Sheet    `json:"sheets"`
	// contains filtered or unexported fields
}

Spreadsheet represents a spreadsheet.

func (*Spreadsheet) SheetByID 

func (spreadsheet *Spreadsheet) SheetByID(id uint) (sheet *Sheet, err error)

SheetByID gets a sheet by the given ID.

func (*Spreadsheet) SheetByIndex 

func (spreadsheet *Spreadsheet) SheetByIndex(index uint) (sheet *Sheet, err error)

SheetByIndex gets a sheet by the given index.

func (*Spreadsheet) SheetByTitle 

func (spreadsheet *Spreadsheet) SheetByTitle(title string) (sheet *Sheet, err error)

SheetByTitle gets a sheet by the given title.

func (*Spreadsheet) UnmarshalJSON 

func (spreadsheet *Spreadsheet) UnmarshalJSON(data []byte) error

UnmarshalJSON embeds spreadsheet to sheets.

type TabColor 

type TabColor struct {
	Red   float32 `json:"red"`
	Green float32 `json:"green"`
	Blue  float32 `json:"blue"`
	Alpha float32 `json:"alpha"`
}

TabColor is color of a tab.

Source Files

View all Source files

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.