sheets

package module
v0.0.3 Latest Latest
Warning

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

 Go to latest Published: Sep 28, 2024 License: MIT Imports: 15 Imported by: 1

README

Sheets

build status report card godocs

Lightweight Google Spreadsheets Client written in Go.

This package is under active development and a work-in-progress project. You should NOT use it on production. Please consider using the official Google's Sheets client for Go instead.

Installation

The only requirement is the Go Programming Language.

$ go get github.com/kataras/sheets@latest

Getting Started

First of all, navigate to https://developers.google.com/sheets/api and enable the Sheets API Service in your Google Console. Place the secret client service account or token file as client_secret.json near the executable example.

Example Code:

package main

import (
    "context"
    "time"

    "github.com/kataras/sheets"
)

func main() {
    ctx := context.TODO()
    //                            or .Token(ctx, ...)
    client := sheets.NewClient(sheets.ServiceAccount(ctx, "client_secret.json"))

    var (
        spreadsheetID := "1Ku0YXrcy8Nqmji7ABS8AmLAyxP5duQIRwmaAJAqyMYY"
        dataRange := "NamedRange or selectors like A1:E4 or *"
        records []struct{
            Timestamp time.Time
            Email     string
            Username  string
            IgnoredMe string `sheets:"-"`
        }{}
    )

    // Fill the "records" slice from a spreadsheet of one or more data range.
    err := client.ReadSpreadsheet(ctx, &records, spreadsheetID, dataRange)
    if err != nil {
        panic(err)
    }

    // Update a spreadsheet on specific range.
    updated, err := client.UpdateSpreadsheet(ctx, spreadsheetID, sheets.ValueRange{
        Range: "A2:Z",
        MajorDimension: sheets.Rows,
        Values: [][]interface{}{
            {"updated record value: 1.1", "updated record value: 1.2"},
            {"updated record value: 2.1", "updated record value: 2.2"},
        },
    })

    // Clears record values of a spreadsheet.
    cleared, err := client.ClearSpreadsheet(ctx, spreadsheetID, "A1:E5")

    // [...]
}

License

This software is licensed under the MIT License.

Documentation

Index

Constants

View Source 
const (
	// ScopeReadOnly is the readonly oauth2 scope.
	ScopeReadOnly = "https://www.googleapis.com/auth/spreadsheets.readonly"
	// ScopeReadWrite is the full-access oauth2 scope.
	ScopeReadWrite = "https://www.googleapis.com/auth/spreadsheets"
)
View Source 
const Rows = "ROWS"

Rows is the default "ROWS" ValueRange.MajorDimension value.

Variables

View Source 
var (
	// ErrOK can be returned from a custom `FieldDecoder` when
	// it should use the default implementation to decode a specific struct's field.
	//
	// See `DecodeValueRange` package-level function and `ReadSpreadsheet` Client's method.
	ErrOK = fmt.Errorf("ok")
)

Functions

func DecodeValueRange 

func DecodeValueRange(dest interface{}, rangeValues ...ValueRange) error

DecodeValueRange binds "rangeValues" to the "dest" pointer of a struct instance.

func IsResourceError 

func IsResourceError(e *ResourceError, target error) bool

IsResourceError reports whether "target" is "e" ResourceError. Returns true when all fields of "e" are equal to "target" fields or when a "target" matching field is empty.

func ServiceAccount 

func ServiceAccount(ctx context.Context, serviceAccountFile string, scopes ...string) http.RoundTripper

ServiceAccount is an oauth2 authentication function which can be passed on the `New` package-level function.

It requires Sheet -> Share button to the email of the service account but it does not need to keep and maintain a token.

It panics on errors.

func Token 

func Token(ctx context.Context, credentialsFile, tokenFile string, scopes ...string) http.RoundTripper

Token is an oauth2 authentication function which can be passed on the `New` package-level function. It accepts a token file and optionally scopes (see `ScopeReadOnly` and `ScopeReadWrite` package-level variables). At the future it may accept scopes from different APIs (e.g google drive to save the spreadsheets on a specified folder).

It panics on errors.

Types

type BasicChart 

type BasicChart struct {
	// ChartType is the type of the chart.
	//
	// Possible values:
	//   "BAR"
	//   "LINE"
	//   "AREA"
	//   "COLUMN"
	//   "SCATTER"
	//   "COMBO"
	//   "STEPPED_AREA"
	ChartType string `json:"chartType,omitempty"`

	// HeaderCount is the number of rows or columns in the data that are
	// "headers".
	// If not set, Google Sheets will guess how many rows are headers
	// based
	// on the data.
	//
	// (Note that ChartAxis.Title may override the axis title
	//  inferred from the header values.)
	HeaderCount int64 `json:"headerCount,omitempty"`

	// LegendPosition is the position of the chart legend.
	// use.
	//
	// Possible values:
	//   "BOTTOM_LEGEND" - The legend is rendered on the bottom of the
	// chart.
	//   "LEFT_LEGEND" - The legend is rendered on the left of the chart.
	//   "RIGHT_LEGEND" - The legend is rendered on the right of the chart.
	//   "TOP_LEGEND" - The legend is rendered on the top of the chart.
	//   "NO_LEGEND" - No legend is rendered.
	LegendPosition string `json:"legendPosition,omitempty"`

	// StackedType is the stacked type for charts that support vertical
	// stacking.
	// Applies to Area, Bar, Column, Combo, and Stepped Area charts.
	//
	// Possible values:
	//   "NOT_STACKED" - Series are not stacked.
	//   "STACKED" - Series values are stacked, each value is rendered
	// vertically beginning
	// from the top of the value below it.
	//   "PERCENT_STACKED" - Vertical stacks are stretched to reach the top
	// of the chart, with
	// values laid out as percentages of each other.
	StackedType string `json:"stackedType,omitempty"`

	// ThreeDimensional if true to make the chart 3D.
	// Applies to Bar and Column charts.
	ThreeDimensional bool `json:"threeDimensional,omitempty"`

	// LineSmoothing sets whether all lines should be rendered smooth or
	// straight by default. Applies to Line charts.
	LineSmoothing bool `json:"lineSmoothing,omitempty"`

	// Axis is the axis on the chart.
	Axis []ChartAxis `json:"axis,omitempty"`
	// Domains is the domain of data this is charting.
	// Only a single domain is supported.
	Domains []ChartDomain `json:"domains,omitempty"`

	// Series is the data this chart is visualizing.
	Series []ChartSeries `json:"series,omitempty"`
}

BasicChart is the specification for a basic chart.

type BatchUpdateResponse 

type BatchUpdateResponse struct {
	// SpreadsheetID is the spreadsheet the updates were applied to.
	SpreadsheetID string `json:"spreadsheetId,omitempty"`
}

BatchUpdateResponse is the response when a batch update request is fired on a spreadsheet.

type Chart 

type Chart struct {
	// ChartID is The ID of the chart.
	ChartID int64 `json:"chartId,omitempty"`
	// Position is the position of the chart.
	Position ChartPosition `json:"position,omitempty"`
	// Spec is the specification of the chart.
	Spec ChartSpec `json:"spec,omitempty"`
}

Chart a chart embedded in a sheet.

type ChartAxis 

type ChartAxis struct {
	// Position is the position of this axis.
	//
	// Possible values:
	//   "BOTTOM_AXIS" - The axis rendered at the bottom of a chart.
	// For most charts, this is the standard major axis.
	// For bar charts, this is a minor axis.
	//   "LEFT_AXIS" - The axis rendered at the left of a chart.
	// For most charts, this is a minor axis.
	// For bar charts, this is the standard major axis.
	//   "RIGHT_AXIS" - The axis rendered at the right of a chart.
	// For most charts, this is a minor axis.
	// For bar charts, this is an unusual major axis.
	Position string `json:"position,omitempty"`
	// Title is the title of this axis. If set, this overrides any title
	// inferred from headers of the data.
	Title string `json:"title,omitempty"`
}

ChartAxis an axis of the chart. A chart may not have more than one axis per axis position.

type ChartData 

type ChartData struct {
	// SourceRange is the source ranges of the data.
	SourceRange ChartSourceRange `json:"sourceRange,omitempty"`
}

ChartData is the data included in a domain or series.

type ChartDomain 

type ChartDomain struct {
	// Domain is the data of the domain. For example, if charting stock prices
	// over time,
	// this is the data representing the dates.
	Domain ChartData `json:"domain,omitempty"`

	// Series is the data this chart is visualizing.
	Series []ChartSeries `json:"series,omitempty"`

	// Reversed if true to reverse the order of the domain values (horizontal
	// axis).
	Reversed bool `json:"reversed,omitempty"`
}

ChartDomain is the domain of a chart. For example, if charting stock prices over time, this would be the date.

type ChartPosition 

type ChartPosition struct {
	// NewSheet: If true, the embedded object is put on a new sheet whose ID
	// is automatically chosen. Used only when writing.
	NewSheet bool `json:"newSheet,omitempty"`
}

ChartPosition is the position of an embedded object such as a chart.

type ChartSeries 

type ChartSeries struct {
	// Series is the data being visualized in this chart series.
	Series ChartData `json:"series,omitempty"`

	// TargetAxis is the minor axis that will specify the range of values for
	// this series.
	// For example, if charting stocks over time, the "Volume" series
	// may want to be pinned to the right with the prices pinned to the
	// left,
	// because the scale of trading volume is different than the scale
	// of
	// prices.
	// It is an error to specify an axis that isn't a valid minor axis
	// for the chart's type.
	//
	// Possible values:
	//   "BOTTOM_AXIS" - The axis rendered at the bottom of a chart.
	// For most charts, this is the standard major axis.
	// For bar charts, this is a minor axis.
	//   "LEFT_AXIS" - The axis rendered at the left of a chart.
	// For most charts, this is a minor axis.
	// For bar charts, this is the standard major axis.
	//   "RIGHT_AXIS" - The axis rendered at the right of a chart.
	// For most charts, this is a minor axis.
	// For bar charts, this is an unusual major axis.
	TargetAxis string `json:"targetAxis,omitempty"`

	// Type is the type of this series. Valid only if the
	// chartType is
	// COMBO.
	// Different types will change the way the series is visualized.
	// Only LINE, AREA,
	// and COLUMN are supported.
	//
	// Possible values:
	//   "BAR"
	//   "LINE"
	//   "AREA"
	//   "COLUMN"
	//   "SCATTER"
	//   "COMBO"
	//   "STEPPED_AREA"
	Type string `json:"type,omitempty"`
}

ChartSeries is a single series of data in a chart. For example, if charting stock prices over time, multiple series may exist, one for the "Open Price", "High Price", "Low Price" and "Close Price".

type ChartSourceRange 

type ChartSourceRange struct {
	// Sources is the ranges of data for a series or domain.
	// Exactly one dimension must have a length of 1,
	// and all sources in the list must have the same dimension
	// with length 1.
	// The domain (if it exists) & all series must have the same number
	// of source ranges. If using more than one source range, then the
	// source
	// range at a given offset must be in order and contiguous across the
	// domain
	// and series.
	//
	// For example, these are valid configurations:
	//
	//     domain sources: A1:A5
	//     series1 sources: B1:B5
	//     series2 sources: D6:D10
	//
	//     domain sources: A1:A5, C10:C12
	//     series1 sources: B1:B5, D10:D12
	//     series2 sources: C1:C5, E10:E12
	Sources []GridRange `json:"sources,omitempty"`
}

ChartSourceRange is the source ranges for a chart.

type ChartSpec 

type ChartSpec struct {
	// BasicChart is A basic chart specification, can be one of many kinds of charts.
	// See BasicChartType for the list of all
	// charts this supports.
	BasicChart BasicChart `json:"basicChart,omitempty"`
	// Title is the title of the chart.
	Title string `json:"title,omitempty"`
	// Subtitle is the subtitle of the chart.
	Subtitle string `json:"subtitle,omitempty"`
}

ChartSpec is the specifications of a chart.

type ClearValuesResponse 

type ClearValuesResponse struct {
	SpreadsheetID string `json:"spreadsheetId"`
	ClearedRange  string `json:"clearedRange"`
}

ClearValuesResponse is the response when clearing values of a spreadsheet.

type Client 

type Client struct {
	HTTPClient *http.Client
}

Client holds the google spreadsheet custom API Client.

func NewClient 

func NewClient(authentication http.RoundTripper) *Client

NewClient creates and returns a new spreadsheet HTTP Client. It accepts `http.RoundTriper` which is used for oauth2 authentication, see `ServiceAccount` and `Token` package-level functions.

func (*Client) AddChart 

func (c *Client) AddChart(ctx context.Context, spreadsheetID string, chart Chart) (response BatchUpdateResponse, err error)

AddChart creates or updates an existing chart to a spreadsheet.

func (*Client) ClearSpreadsheet 

func (c *Client) ClearSpreadsheet(ctx context.Context, spreadsheetID, dataRange string) (response ClearValuesResponse, err error)

ClearSpreadsheet clears values from a spreadsheet. The caller must specify the spreadsheet ID and range. Only values are cleared -- all other properties of the cell (such as formatting, data validation, etc..) are kept.

func (*Client) Do 

func (c *Client) Do(ctx context.Context, method, url string, body io.Reader, options ...RequestOption) (*http.Response, error)

Do sends an HTTP request and returns an HTTP response. It respects gzip and some settings specified to google's spreadsheet API. The last option can be used to modify a request before sent to the server.

func (*Client) GetSpreadsheetInfo 

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

GetSpreadsheetInfo returns general information about a spreadsheet based on the provided "spreadsheetID".

func (*Client) Range 

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

Range returns record values of a spreadsheet based on the provided "dataRanges", if more than one data range then it sends a batch request. See `ReadSpreadsheet` method too.

func (*Client) ReadJSON 

func (c *Client) ReadJSON(ctx context.Context, method, url string, requestData, toPtr interface{}, options ...RequestOption) error

ReadJSON fires a request to "url" and binds a JSON response to the "toPtr".

func (*Client) ReadSpreadsheet 

func (c *Client) ReadSpreadsheet(ctx context.Context, dest interface{}, spreadsheetID string, dataRanges ...string) error

ReadSpreadsheet binds record values of a spreadsheet to the "dest". See `Range` method too.

func (*Client) UpdateSpreadsheet 

func (c *Client) UpdateSpreadsheet(ctx context.Context, spreadsheetID string, values ValueRange) (response UpdateValuesResponse, err error)

UpdateSpreadsheet updates a spreadsheet of a range of provided "dataRange", if "dataRange" is empty or "*" then it will update all columns specified by "values".

type FieldDecoder 

type FieldDecoder interface {
	DecodeField(h *Header, value interface{}) error
}

FieldDecoder is an inteface which a struct can implement to select custom decode implementation instead of the default one, if `ErrOK` is returned then it will fill the field with the default implementation.

type GridRange 

type GridRange struct {
	// EndColumnIndex is the end column (exclusive) of the range, or not set
	// if unbounded.
	EndColumnIndex int64 `json:"endColumnIndex,omitempty"`

	// EndRowIndex is the end row (exclusive) of the range, or not set if
	// unbounded.
	EndRowIndex int64 `json:"endRowIndex,omitempty"`

	// SheetID is the sheet this range is on.
	SheetID int64 `json:"sheetId,omitempty"`

	// StartColumnIndex is the start column (inclusive) of the range, or not
	// set if unbounded.
	StartColumnIndex int64 `json:"startColumnIndex,omitempty"`

	// StartRowIndex is the start row (inclusive) of the range, or not set if
	// unbounded.
	StartRowIndex int64 `json:"startRowIndex,omitempty"`
}

GridRange is a range on a sheet. All indexes are zero-based. Indexes are half open, e.g the start index is inclusive and the end index is exclusive -- [start_index, end_index). Missing indexes indicate the range is unbounded on that side.

For example, if "Sheet1" is sheet ID 0, then: 

`Sheet1!A1:A1 == sheet_id: 0,
                start_row_index: 0, end_row_index: 1,
                start_column_index: 0, end_column_index: 1`

`Sheet1!A3:B4 == sheet_id: 0,
                start_row_index: 2, end_row_index: 4,
                start_column_index: 0, end_column_index: 2`

`Sheet1!A:B == sheet_id: 0,
              start_column_index: 0, end_column_index: 2`

`Sheet1!A5:B == sheet_id: 0,
               start_row_index: 4,
               start_column_index: 0, end_column_index: 2`

`Sheet1 == sheet_id:0`

The start index must always be less than or equal to the end index. If the start index equals the end index, then the range is empty. Empty ranges are typically not meaningful and are usually rendered in the UI as `#REF!`. 

type Header struct {
	Name       string // the sheet header name value.
	FieldIndex int
	FieldName  string // the field name, may be identical to Name.
	FieldType  reflect.Type
}

Header is the row's header of a struct field.

type NamedRange 

type NamedRange struct {
	ID    string `json:"namedRangeId"`
	Name  string `json:"name"`
	Range Range  `json:"range"`
}

NamedRange represents the namedRange of a request.

type Query 

type Query url.Values

Query is a `RequestOption` which sets URL query values to the Request.

func (Query) Apply 

func (q Query) Apply(r *http.Request)

Apply implements the `RequestOption` interface. It adds the "q" values to the request.

type Range 

type Range struct {
	SheetID          string `json:"sheetId"`
	StartRowIndex    int    `json:"startRowIndex"`
	EndRowIndex      int    `json:"endRowIndex"`
	StartColumnIndex int    `json:"startColumnIndex"`
	EndColumnIndex   int    `json:"endColumnIndex"`
}

Range holds the range request and response values.

type RequestOption 

type RequestOption interface{ Apply(*http.Request) }

A RequestOption can be passed on `Do` method to modify a Request.

type ResourceError 

type ResourceError struct {
	Method     string
	URL        string
	StatusCode int
	Message    string
}

ResourceError is a Client type error. It returns from Client's method when server replies with an error. It holds the HTTP Method, URL, Status Code and the actual error message came from server.

See `IsResourceError` and `IsStatusError` too.

func IsStatusError 

func IsStatusError(statusCode int, target error) (*ResourceError, bool)

IsStatusError reports whether a "target" error is type of `ResourceError` and the status code is the provided "statusCode" one. Usage: resErr, ok := IsStatusError(http.StatusNotFound, err) 

if ok {
	[ressErr.Method, URL, StatusCode, Message...]
}

See `IsResourceError` too.

func (*ResourceError) Error 

func (e *ResourceError) Error() string

Error implements a Go error and returns a human-readable error text.

func (*ResourceError) Is 

func (e *ResourceError) Is(target error) bool

Is implements the standard`errors.Is` internal interface. It's equivalent of the `IsResourceError` package-level function.

type Sheet 

type Sheet struct {
	Properties SheetProperties `json:"properties"`
}

Sheet holds the sheet fields.

func (*Sheet) RangeAll 

func (s *Sheet) RangeAll() string

RangeAll returns a data range text which can be used to fetch all rows of a sheet.

type SheetGrid 

type SheetGrid struct {
	RowCount       int `json:"rowCount"`
	ColumnCount    int `json:"columnCount"`
	FrozenRowCount int `json:"frozenRowCount"`
}

SheetGrid represents the `Grid` field of `SheetProperties`.

type SheetProperties 

type SheetProperties struct {
	ID        string    `json:"sheetId"`
	Title     string    `json:"title"`
	Index     int       `json:"index"`
	SheetType SheetType `json:"sheetType"`
	Grid      SheetGrid `json:"gridProperties,omitempty"`
}

SheetProperties holds the properties of a sheet.

type SheetType 

type SheetType string

SheetType represents the type of a Sheet. 

const (
	// Grid is a Sheet type.
	Grid SheetType = "GRID"
)

type Spreadsheet 

type Spreadsheet struct {
	ID          string                `json:"spreadsheetId"`
	Properties  SpreadsheetProperties `json:"properties"`
	Sheets      []Sheet               `json:"sheets"`
	NamedRanges []NamedRange          `json:"namedRanges"`
	URL         string                `json:"spreadsheetUrl"`
}

Spreadsheet holds a spreadsheet's fields.

func (*Spreadsheet) GetSheet 

func (sd *Spreadsheet) GetSheet(title string) (Sheet, bool)

GetSheet finds and returns a sheet based on its "title" inside the "sd" Spreadsheet value.

type SpreadsheetProperties 

type SpreadsheetProperties struct {
	Title    string `json:"title"`
	Locale   string `json:"locale"`
	Timezone string `json:"timeZone"`
}

SpreadsheetProperties holds the properties of a spreadsheet.

type UpdateValuesResponse 

type UpdateValuesResponse struct {
	// The spreadsheet the updates were applied to.
	SpreadsheetID string `json:"spreadsheetId"`
	// The range (in A1 notation) that updates were applied to.
	UpdatedRange string `json:"updatedRange"`
	// The number of rows where at least one cell in the row was updated.
	UpdatedRows int `json:"updatedRows"`
	// The number of columns where at least one cell in the column was updated.
	UpdatedColumns int `json:"updatedColumns"`
	// The number of cells updated.
	UpdatedCells int `json:"updatedCells"`
	// The values of the cells after updates were applied.
	// This is only included if the request's includeValuesInResponse field was true.
	UpdatedData []ValueRange `json:"updatedData"`
}

UpdateValuesResponse is the response when updating a range of values in a spreadsheet.

type ValueRange 

type ValueRange struct {
	// Range are the values to cover, in A1 notation. For output, this range indicates the entire requested range, even though the values will exclude trailing rows and columns. When appending values,
	// this field represents the range to search for a table,
	// after which values will be appended.
	Range string `json:"range"`
	// The major dimension of the values.
	MajorDimension string `json:"majorDimension"`
	// Values holds the data that was read or to be written.
	// This is a slice of slices, the outer array representing all the data and each inner array representing a major dimension.
	// Each item in the inner array corresponds with one cell.
	//
	// For output, empty trailing rows and columns will not be included.
	//
	// For input, supported value types are: bool, string, and double. Null values will be skipped.
	// To set a cell to an empty value, set the string value to an empty string.
	Values [][]interface{} `json:"values"`
}

ValueRange holds data within a range of the spreadsheet.

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.