えんじにあメモ

試してみた技術とか、たまに家電ネタ

GoでGoogleスプレッドシートを操作する

go

SheetsAPI を利用してGoからスプレッドシートの操作（参照・更新）を行う

CLIで動かすためサービスアカウントを利用して操作を行う

実行環境はMac、Goの利用バージョンは1.13

プロジェクトの作成

Google Cloud Platform からプロジェクトを作成しておく
（すでに作成済みのプロジェクトを利用する場合はこの手順は不要）

今回はテスト用に「MyTestProject」という名前で作成しておいた

SheetsAPIの有効化

作成したプロジェクトからSheetsAPIを利用できるように有効化を行う

「＋APIとサービスを有効化」をクリック

f:id:shosfs:20191205182248p:plain
APIとサービスを有効化

検索フォームに「sheets」などと入力すればGoogle Sheets APIが出てくるので選択する

f:id:shosfs:20191205182422p:plain
Google Sheets API を選択

「有効にする」を選択

f:id:shosfs:20191205182609p:plain
Google Sheets API を有効化

サービスアカウントの作成

サイドメニューの「認証情報」を選択

f:id:shosfs:20191205183117p:plain
認証情報

「認証情報を作成」から「サービスアカウントキー」を選択

f:id:shosfs:20191205183205p:plain
サービスアカウントキーを選択

サービスアカウントは「新しいサービスアカウント」を選択し、サービスアカウント名を入力する
今回はテスト用に「sheets-test-account」とした

f:id:shosfs:20191205183805p:plain
サービスアカウントキーの作成

作成後、秘密鍵を含む認証情報のJSONファイルがDLされるので保管しておく（ロジック部分で使うよ）

スプレッドシートの設定

操作を行う対象のスプレッドシートを用意する

作成後、サービスアカウントからの操作を受け付けるための設定を行う

まず、サービスアカウントのメールアドレスをコピっておく
「認証情報」から「サービスアカウントの管理」をクリック

f:id:shosfs:20191205184943p:plain
サービスアカウントの管理

作成したサービスアカウントのメールアドレスをコピー

f:id:shosfs:20191205185834p:plain
サービスアカウントのメールアドレスをコピー

対象のスプレッドシートを開き、右上の「共有」からコピったメールアドレスを入力し、「送信」を選択

f:id:shosfs:20191205190220p:plain
作成したサービスアカウントと共有

これでSheetsAPI経由で操作する事前準備が整ったので、Goでの操作プログラムを書いていく（長い）

Goでのスプレッドシート操作

今回テスト用に使うスプレッドシートは以下のようなやつ

f:id:shosfs:20191205200121p:plain
テスト用スプレッドシート
B列に更新日時を入れていて、今回のロジックではここの日時の更新を行う

作ったロジックは↓

github.com

取得した情報の出力と、B列の時刻を現在時刻に更新する

実行方法

実行する際にはサービスアカウントを作成した際にDLした認証情報のJSONファイルをsecret.jsonという名前でmain.goと同じディレクトリに置いておく
（gitなどにpushしないこと）

実行時にスプレッドシートIDを環境変数として渡すようにしていて、スプレッドシートのURLの以下の部分を指定する

https://docs.google.com/spreadsheets/d/<スプレッドシートID>/

実行

$ SHEET_ID=<スプレッドシートID> go run main.go

ロジック詳細

参照には

srv.Spreadsheets.Values.Get(spreadsheetID, readRange).Do()

更新には

srv.Spreadsheets.Values.Update(spreadsheetID, updateRange, &vr).ValueInputOption("RAW").Do()

を利用している

それぞれ第2引数に指定するreadRange、updateRangeは文字列で、
GoDoc や、公式のサンプル が参考になる

!の前にはシート名を指定しているが、日本版？のスプレッドシートで作成すると「シート1」のようになるため、
指定する際は日本語でシート1!A:Bのようにしてあげる必要がある（今回は事前に「Sheet1」に変えておいた）

参照時の戻り値および、更新の際に指定する値（&vr）は、ValueRange型で、
ValueRange.Valuesは[][]interface{}となっている

A列も一緒に合わせて更新する場合は以下のようになる（A列の後ろに"-test"を追加）

        var vr sheets.ValueRange
        now := time.Now().Format("2006/01/02 15:04:05")
        for i := 0; i < len(resp.Values); i++ {
-               vr.Values = append(vr.Values, []interface{}{now})
+               vr.Values = append(vr.Values, []interface{}{fmt.Sprintf("%s-test", resp.Values[i][0]), now})
        }
-       updateRange := "Sheet1!B1:B"
+       updateRange := "Sheet1!A1:B"
        if _, err = srv.Spreadsheets.Values.Update(spreadsheetID, updateRange, &vr).ValueInputOption("RAW").Do(); err != nil {
                log.Fatal(err)
        }

まとめ

事前準備がちょっとめんどくさいけど、ロジックは割とシンプル

参考