GoでGoogleSpreadsheet出力する

15152 ワード
GoogleDrive Go GoogleDriveAPI GoogleSpreadSheet GoogleSheetsAPI Go テキストリンク

フューチャー Advent Calendar 12日目 です。
昨日は @mirai_taiyaki さんによる PG BATTLE 2020参戦記 ～チーム結成法、注意点、作戦など～ でした。

はじめに

例えば業務系のシステムなどたくさんのデータをバックエンドで処理したのち、手元で更に加工をしたいといった用途が考えられます。
このとき大きな選択肢としては「CSV形式などによるダウンロード」となりますが、これを実施した場合、ローカルに何らかのバッチ処理を作ることが前提となります。
また、ユーザー数によっては繰り返しデータが出力されることになり、DBへのアクセス数も増えます。
それくらいであれば、一つのバッチ処理で共通してアクセスできるクラウド上にダウンロードする相当のCSVなりのデータをアップロードすることを考えます。
その際たる候補として上がるのがGoogle Driveです。複数のユーザーでファイルの共有が行え、かつ各種APIが提供されたことによって外部システムとの連携も比較的用意となっています。
今回は従来ローカルへのダウンロードを行っていたデータをGoogle Drive上の特定のフォルダ以下のSpreadsheetに出力するシステムを構築してみます。

構築するシステム

  • ファイルは特定のフォルダ以下に順番に作っていくものとします
  • ファイルは年月の単位で新規につくるものとします
  • シートは日付単位で切り替えるものとします
  • シートは人間が編集することは前提とせず、システムでのみ更新することとします
  • ファイル・シートはルールに則ってシステムが自動生成して増やしていくものとします
testFolder
├─sample_202010.spreadsheet
│  ├─sheet名:20201001
│  ├─sheet名:20201002
│  ︙
│  └─sheet名:20201031
├─sample_202011.spreadsheet
│  ├─sheet名:20201101
│  ├─sheet名:20201102
│  ︙
│  └─sheet名:20201130
└─sample_202012.spreadsheet
   └─sheet名:20201201

システム構成

最終的な構成として今回バックエンドとしてはAWSを想定します。
NW的に繋がっていればオンプレのシステムでも問題有りませんが、構成のイメージがつきやすいのと、途中で特定のDBに対してIDを取得しにいくものを挟むため、このような構成としています。
今回Goでコードを実装しているのは、図の中のLambdaです。
また、合わせてフローチャートも記載します。

実際のコードにも必要になりますが、Google Drive上のファイルはIDで管理されています。
そのため同じ場所に同名のファイルやフォルダを複数作ることができます。
たとえば、フォルダのIDは下記のようにURLから確認できます。

ソースコード

今回利用するのは Google Drive API v3とGoogle Sheets API v4です。
Spreadsheetにデータを出力するだけであればGoogle Sheets API v4だけで良いですが、対象ファイルを新しく作る場合はGoogle Drive API v3が必要となります。
今回は上記２つのAPIのsampleを組み合わせ、コードを記載しました。
実際のコードは、こちらから御覧ください。

最終的にGoogleSpreadsheetを出力するには [][]interface の形にして関数に与えてあげます。
この2次元のセルがそのままspreadsheetに書かれます。
今回はかんたんのため、A1セルを起点にセルをすべて上書きする形で記載しています。

handler.go
// 前略: 前半でclsという[][]string型に必要なデータを整形して詰めています
    v := [][]interface{}{}
    vv := []interface{}{}
    for _, c := range cls {
        vv = []interface{}{}
        for _, cc := range c {
            vv = append(vv, cc)
        }
        v = append(v, vv)
    }
    // シート1のA1セルを起点にして書き込む
    writeRange := fmt.Sprintf("%s!A1", sheetName)
    valueRange := &sheets.ValueRange{
        Values: v,
    }
    _, err = spreadsheetService.Spreadsheets.Values.Update(targetSpreadsheetID, writeRange, valueRange).ValueInputOption("USER_ENTERED").Do()
    if err != nil {
        return "", fmt.Errorf("unable to retrieve data from sheet. %v", err)
    }
// 以下略

今回はセルの場所と2次元配列の場所をわかりやすくするためのデータしか入れていないですが、適切にデータを収集・編集することができれば、GoogleDrive上に帳票などを出力することも可能になります。

実際の動作

リポジトリに記載したように適切にDriveのIDを取得して環境変数に設定してあげれば、下記のように動作します。

$ make test
export DYNAMO_ENDPOINT=http://localhost:4566;\
        export DYNAMO_TABLE_EXPORT=local_export;\
        export GOOGLE_EXPORT_ROOT_DIR="今回作成したtestFolderのID";\
        go test -race -v ./
{"level":"debug","time":"2020-12-12T16:46:04+09:00","message":"unset env variable: GOOGLE_CLIENT_SECRET"}
{"level":"debug","time":"2020-12-12T16:46:04+09:00","message":"unset env variable: GOOGLE_DRIVE_ACCESS_TOKEN"}
{"level":"debug","time":"2020-12-12T16:46:04+09:00","message":"unset env variable: GOOGLE_SPREADSHEET_ACCESS_TOKEN"}
{"level":"info","time":"2020-12-12T16:46:04+09:00","message":"DYNAMO_ENDPOINT is set. http://localhost:4566"}
=== RUN   TestExportSpreadsheet
=== RUN   TestExportSpreadsheet/[正常系]_データ出力
    handler_test.go:42: [INFO] command: aws dynamodb --profile local --endpoint-url http://localhost:4566 create-table --cli-input-json file://./testdata/local_export.json
{"level":"info","time":"2020-12-12T16:46:09+09:00","message":"url = 実際に出力されたspreadsheetのurl"}
    handler_test.go:88: [INFO] command: aws dynamodb --profile local --endpoint-url http://localhost:4566 delete-table --table local_export
--- PASS: TestExportSpreadsheet (6.58s)
    --- PASS: TestExportSpreadsheet/[正常系]_データ出力 (6.58s)
PASS
ok      github.com/montblanc18/google-spreadsheet-exporter      6.916s

実際にフォルダにもファイルが出力されています。


プログラムでは対象となるシートがなければ新しく作るようにしています。
最初にスプレッドシートを作った段階で「シート1」が作られ、それに追加で「202008」を作っているため、このように動作しています。
なお、先に説明しましたように Google Drive内のファイルはIDで管理されるため同じファイルを複数つくれる ので、先の go clean -testcache && make test を連打すると次のようにファイルがたくさんできます。

これを避けるために、一度作成したGoogle SpreadsheetのIDをDynamoDBに保存しておく形にしています。
今回実装したテストの範囲では逐次localstack内のテーブルを消してしまうので go clean -testcache && make test のたびにファイルが新規作成されますが、DynamoDB内のテーブルを維持し続ければ、一つのファイルに繰り返し出力されます。

最後に

Google Drive および Spreadsheet 連携によって、クラウドにそのままデータが連携できるようになりました。
このままGASに連携したり、あるいはその他のスクリプトを実行したりすることもできます。
もちろん、このまま帳票を作り込んであげることもできます。
また、オンプレのシステムからクラウドに対してデータを打ち上げても良いでしょう。

ただし、ここでの注意点としてはデータの整形は手ずから行う必要があることです。
加えて、Google Spreadsheet中に SUM や IF を始めとした関数をセルの参照を使って行いたい場合、列をアルファベット表示にするため、今回は省きましたが次のような関数を挟むような処理が必要です。

func Num2Capital(ctx context.Context, colNum int) string {
    cap := []string{"A", "B", "C", "D", "E", "F", "G", "H", "I", "J",
        "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T",
        "U", "V", "W", "X", "Y", "Z"}
    r := len(cap)
    if colNum >= r {
        return fmt.Sprintf("%s%s", Num2Capital(ctx, (colNum-colNum%r)/r-1), cap[colNum%r])
    } else {
        return cap[colNum%r]
    }
}

また、Google Spreadsheetへの出力がシステムによるものしかなければよいですが、ユーザーが同時に操作する場合は、上書きしないように注意するなど必要になります。
Google DriveおよびSpreadsheetへの出力機能は便利ではありますが、作り込もうとするとかなり作り込めてしまうので、適宜見極めながら実装していただければと思います。