まず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)
}
上の状態から続けて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)
}
ところが、次のような行が空いたテーブルに同様の処理を行うと、
それぞれが一つのテーブルとして扱われてこのように起点が変わってしまう。
rangeをシート全体からA1に変更すると前半のテーブルに追加される。
さらに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)
}
}
