GoのSheets API v4クライアントでSpreadsheetを読み書きする

gcpgolang

まずGCPのコンソールでプロジェクトを作成するか選んでGoogle Sheets APIを有効にする。 料金はかからないが、デフォルトのQuotaが、100秒ごとに100リクエスト/ユーザー、500リクエスト/プロジェクトとなっている。

OAuth 2.0で得たユーザーの権限でAPIを呼ぶこともできるが、今回はサービスアカウントを用いる。ロールは付与する必要はなく、対象のSheetの共有先に追加すればよい。 JWTの署名に用いる秘密鍵をダウンロードしておく。

OpenID ConnectのIDトークンの内容と検証 - sambaiz-net

全体のコードはGitHubにある。

クライアントの準備

サービスアカウントでアクセストークンを取得するにはJWTを認可サーバーに送る必要があるが、JWTをアクセストークンの代わりに用いることができるのでそうしている。

SpreadsheetIDはURLから得られる。

package main

import (
	"context"
	"fmt"
	"io/ioutil"
	"os"

	"golang.org/x/oauth2/google"
	"google.golang.org/api/sheets/v4"
)

type SheetClient struct {
	srv           *sheets.Service
	spreadsheetID string
}

func NewSheetClient(ctx context.Context, spreadsheetID string) (*SheetClient, error) {
	b, err := ioutil.ReadFile("secret.json")
	if err != nil {
		return nil, err
	}
    // read & write permission
	jwt, err := google.JWTConfigFromJSON(b, "https://www.googleapis.com/auth/spreadsheets")
	if err != nil {
		return nil, err
	}
	srv, err := sheets.New(jwt.Client(ctx))
	if err != nil {
		return nil, err
	}
	return &SheetClient{
		srv:           srv,
		spreadsheetID: spreadsheetID,
	}, nil
}

読み込み

式で参照する時と同じ記法でセルや範囲を参照できる。

func (s *SheetClient) Get(range_ string) ([][]interface{}, error) {
	resp, err := s.srv.Spreadsheets.Values.Get(s.spreadsheetID, range_).Do()
	if err != nil {
		return nil, err
	}
	return resp.Values, nil
}

func main() {
	ctx := context.Background()
	client, err := NewSheetClient(ctx, os.Getenv("SPREAD_SHEET_ID"))
	if err != nil {
		panic(err)
	}
	values, err := client.Get("'シート1'!A1:C1")
	for _, row := range values {
		fmt.Println(row)
	}
}

書き込み

ValueInputOptionパラメータで入力をそのままのstringとして扱うRAWか、式や日付として解釈するUSER_ENTEREDを渡す必要がある。

func (s *SheetClient) Update(range_ string, values [][]interface{}) error {
	_, err := s.srv.Spreadsheets.Values.Update(s.spreadsheetID, range_, &sheets.ValueRange{
		Values: values,
	}).ValueInputOption("USER_ENTERED").Do()
	if err != nil {
		return err
	}
	return nil
}

func (s *SheetClient) Append(values [][]interface{}) error {
	_, err := s.srv.Spreadsheets.Values.Append(s.spreadsheetID, "シート1", &sheets.ValueRange{
		Values: values,
	}).ValueInputOption("USER_ENTERED").InsertDataOption("INSERT_ROWS").Do()
	if err != nil {
		return err
	}
	return nil
}

Updateの方はセルを指定した場合そこを起点として書き込み、範囲を指定した場合、入力に対して足りない場合はエラーとなる。

if err := client.Update("A1", [][]interface{}{
  {
    "aaa",
    "bbb",
  },
  {
    "ccc",
    "ddd",
  },
}); err != nil {
  panic(err)
}
if err := client.Update("A1:A2", [][]interface{}{
  {
    "xxx",
  },
  {
    "yyy",
  },
}); err != nil {
  panic(err)
}

Update後

上の状態から続けてAppendすると順当にその次の行から書き込まれる。

if err := client.Append([][]interface{}{
  {
    "1",
  },
}); err != nil {
  panic(err)
}
if err := client.Append([][]interface{}{
  {
    "=A3+1",
    "=A3+2",
    "=A3+3",
  },
}); err != nil {
  panic(err)
}

Append後

ところが、次のような行が空いたテーブルに同様の処理を行うと、

行が空いたテーブルのAppend前

それぞれが一つのテーブルとして扱われてこのように起点が変わってしまう。

行が空いたテーブルのAppend後

rangeをシート全体からA1に変更すると前半のテーブルに追加される。

シートからA1に変更

さらにInsertDataOptionOVERWRITEにすると新しい行が作成されずに既存の隙間に埋まる。

InsertDataOptionをOVERWRITEに変更

書式変更

Spreadsheets.BatchUpdateでセルの書式変更など値の変更以外もできる。

func (s *SheetClient) Format(range_ *sheets.GridRange, format *sheets.CellFormat) error {
	_, err := s.srv.Spreadsheets.BatchUpdate(s.spreadsheetID, &sheets.BatchUpdateSpreadsheetRequest{
		Requests: []*sheets.Request{
			{
				RepeatCell: &sheets.RepeatCellRequest{
					Fields: "userEnteredFormat(backgroundColor)",
					Range:  range_,
					Cell: &sheets.CellData{
						UserEnteredFormat: format,
					},
				},
			},
		},
	}).Do()
	if err != nil {
		return err
	}
	return nil
}

func (s *SheetClient) SheetID(sheetName string) (int64, error) {
	resp, err := s.srv.Spreadsheets.Get(s.spreadsheetID).Do()
	if err != nil {
		return 0, err
	}
	for _, sheet := range resp.Sheets {
		if sheet.Properties.Title == sheetName {
			return sheet.Properties.SheetId, nil
		}
	}
	return 0, fmt.Errorf("sheetName %s is not found", sheetName)
}


func main() {
	ctx := context.Background()
	client, err := NewSheetClient(ctx, os.Getenv("SPREAD_SHEET_ID"))
	if err != nil {
		panic(err)
	}
	sheetID, err := client.SheetID("シート1")
	if err != nil {
		panic(err)
	}
	if err := client.Format(&sheets.GridRange{
		SheetId:          sheetID,
		StartColumnIndex: 1,
		StartRowIndex:    2,
		EndColumnIndex:   3,
		EndRowIndex:      4,
	}, &sheets.CellFormat{
		BackgroundColor: &sheets.Color{
			Red: 1.0,
		},
	}); err != nil {
		panic(err)
	}
}

セルの背景色を変更

DataValidationを設定してShowCustomUi: trueにするとドロップダウンリストが出せる。

func (s *SheetClient) List(range_ *sheets.GridRange, values []string) error {
	conditionValues := make([]*sheets.ConditionValue, 0, len(values))
	for _, value := range values {
		conditionValues = append(conditionValues, &sheets.ConditionValue{
			UserEnteredValue: value,
		})
	}
	_, err := s.srv.Spreadsheets.BatchUpdate(s.spreadsheetID, &sheets.BatchUpdateSpreadsheetRequest{
		Requests: []*sheets.Request{
			{
				RepeatCell: &sheets.RepeatCellRequest{
					Fields: "dataValidation",
					Range:  range_,
					Cell: &sheets.CellData{
						DataValidation: &sheets.DataValidationRule{
							Condition: &sheets.BooleanCondition{
								Type:   "ONE_OF_LIST",
								Values: conditionValues,
							},
							ShowCustomUi: true,
							Strict:       true,
						},
					},
				},
			},
		},
	}).Do()
	if err != nil {
		return err
	}
	return nil
}

func main() {
	ctx := context.Background()
	client, err := NewSheetClient(ctx, os.Getenv("SPREAD_SHEET_ID"))
	if err != nil {
		panic(err)
	}
	sheetID, err := client.SheetID("シート1")
	if err != nil {
		panic(err)
	}
	if err := client.List(&sheets.GridRange{
		SheetId:          sheetID,
		StartColumnIndex: 1,
		StartRowIndex:    2,
		EndColumnIndex:   3,
		EndRowIndex:      4,
	}, []string{"○", "×"}); err != nil {
		panic(err)
	}
}

ドロップダウンリスト