GoogleスプレッドシートをSpringBootからデータをインポートして運用改善するお話

こちらの記事は、アソビュー！ Advent Calendar 2024の18日目（B面）です。

はじめに

アソビューでバックエンドエンジニアを担当しているアズマです。今回は、Googleスプレッドシートを使って人力でデータのやりとりをしているのを、 Spring Boot のWebアプリケーションからスプレッドシートのデータを取得して運用改善をする話です。

現在一部の業務で行われている内容

「アソビュー！」では提携していただいている体験事業者・レジャー施設様を、パートナーとお呼びしています。そして弊社ではパートナー様が販売しているチケットやアクティビティの予約を、定期的に月次ないしは一定の日数ごとに売上を集計し、パートナー様へ精算をしています。この売上集計から精算までの業務の大半は販売管理のシステムで自動化をしていますが、チケットの種類や販売経路によっては売上集計のロジックや精算方法も異なるケースがあるため、これらはGoogleスプレッドシートでデータを作成して弊社の経理さんたちへ共有し、そこから経理さんたちは販売管理のシステムへ手動で登録しています。

手動で登録する件数が少なければよいのですが、事業が成長していけば比例してその件数が膨大になっていけば、当然登録漏れやミスがないかを細かくチェックすることもできなくなり、万が一ミスがあればパートナー様へご迷惑をおかけしてしまいます。

そこで今回は、Googleスプレッドシートで共有されるデータを、販売管理のシステム、具体的には Spring BootのWebアプリケーションが直接データを参照してインポートすることで、大量に発生していた手動登録の手間を削減するお話をします。

事前準備

Google Cloudにてプロジェクトを作成します。今回は「sample-spreadsheet-01」としました。

プロジェクト用アカウントを準備する

ダッシュボードのメニューやサービス検索から IAM と管理を選択します。

プロジェクトダッシュボード

IAMのメニューから「サービスアカウント」をクリック、その後画面上部にある「サービスアカウントの作成」をクリックしてAPI用のアカウントを作成します。

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

作成したアカウントを選び、一番右にある「操作」（縦の三点リーダー）をクリックします。

アカウント：鍵の管理

鍵を追加 ＞ 新しい鍵の作成をクリックします

新しい鍵の作成

作成する鍵のタイプを選びます。今回は推奨されているJSONを用います。

鍵タイプの選択

作成が完了すると、JSONファイルがダウンロードされますので保存します。これでサービス用のアカウントと、アクセスするための鍵ファイルが用意できました。

なお、作成したサービスアカウントのメールアドレスは、後程スプレッドシートにて権限を追加するときにも使います。

APIとサービスから、スプレッドシートAPIを有効にする

ダッシュボードに戻り、「APIとサービス」を選択したあと、「」をクリックします。

APIとサービス

ライブラリの一覧と検索ができますので、「Spread」と入力して検索するとGoogle Spread Sheet が表示されますので、これを有効にします。

ライブラリの有効化

参照するスプレッドシートへ、作成したサービスアカウントの権限をつける

スプレッドシートを開き、共有の設定から先ほど作成したサービスアカウントのメールアドレスを追加して、権限を追加します。今回は参照だけとしたので、参照のみを選びます。

権限の追加

以上でスプレッドシートを読み込む準備が完了しました。

Spring Boot 側の準備

Spring Boot から スプレッドシートAPIを実行するための準備

GoogleAPI用のライブラリを追加します。

    implementation group: 'com.google.api-client', name: 'google-api-client', version: '1.32.1'
    implementation group: 'com.google.apis', name: 'google-api-services-sheets', version: 'v4-rev20210629-1.32.1'
    implementation group: 'com.google.apis', name: 'google-api-services-drive', version: 'v3-rev20240509-2.0.0'
    implementation group: 'com.google.auth', name: 'google-auth-library-oauth2-http', version: '1.30.0'

サービスアカウントの作成後、ダウンロードしたアカウントのJSONファイルをアプリケーションのresourcesへ配置し、これを読み込みます。 アプリケーションの設定 application.yml に以下の記述を追加します。

google:
  spreadsheet:
    keyfile: 'sample-spreadsheet-01-5bee48ff8f60.json'

これを読み込むクラスは、以下です。

import lombok.Getter;
import lombok.Setter;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;

@Component
@Getter
@Setter
@ConfigurationProperties(prefix = "google.spreadsheet")
public class SpreadSheetProperties {
    private String keyfile;
}

スプレッドシートへアクセスするためのクラス

スプレッドシートへアクセスするための設定は整いましたので、実際にGoogleCloudへアクセスするクラスを取得し、スプレッドシートAPIのインスタンスを取得します。 今回は以下のServiceクラスを用意しました。

import com.google.api.client.googleapis.javanet.GoogleNetHttpTransport;
import com.google.api.client.json.gson.GsonFactory;
import com.google.api.services.sheets.v4.Sheets;
import com.google.api.services.sheets.v4.SheetsScopes;
import com.google.api.services.sheets.v4.model.CellData;
import com.google.api.services.sheets.v4.model.ExtendedValue;
import com.google.api.services.sheets.v4.model.GridData;
import com.google.api.services.sheets.v4.model.RowData;
import com.google.api.services.sheets.v4.model.Spreadsheet;
import com.google.auth.http.HttpCredentialsAdapter;
import com.google.auth.oauth2.ServiceAccountCredentials;
import lombok.AllArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.github.apz.sample.spreadsheet.SpreadSheetProperties;
import org.springframework.core.io.ClassPathResource;
import org.springframework.stereotype.Service;

import java.io.IOException;
import java.security.GeneralSecurityException;
import java.util.List;

@Service
@AllArgsConstructor
@Slf4j
public class SpreadSheetReadService {

    SpreadSheetProperties properties;

    public Sheets.Spreadsheets getSpreadSheetInstance() throws IOException, GeneralSecurityException {
        Sheets sheets = new Sheets.Builder(
                GoogleNetHttpTransport.newTrustedTransport(),
                GsonFactory.getDefaultInstance(),
                new HttpCredentialsAdapter(
                        ServiceAccountCredentials
                                .fromStream(
                                        new ClassPathResource(properties.getKeyfile()).getInputStream()    // (1) Springの「クラスパス内のファイルから読み込む」を利用
                                )
                                .createScoped(List.of(SheetsScopes.SPREADSHEETS))     // (2) GoogleAPIから、スプレッドシートのアクセスを指定する
                )
        ).build();

        return sheets.spreadsheets();
    }
}

(1) SpreadSheetProperties にて定義した、JSONファイルのパスを取得します。src/main/resources はSpring Bootのクラスパスに含まれているので、ClassPathResourcesクラスから InputStream を取得します。 (2) 今回はスプレッドシートへのアクセスを要求します。ServiceAccontCredentialsクラスはGoogleCloudのサービスを複数指定可能です。

アクセスに成功できれば、このメソッドが返しているSheets.Spreadsheets インスタンスからスプレッドシートのデータを取得できます。

データを取り出す

スプレッドシートからデータを取り出すには、以下を設定します。

(1) スプレッドシートID (2) 範囲（シート＋セルの範囲：複数指定ができます） (3) 取得する内容の種類

実装は以下です。

    public Spreadsheet getSheet(Sheets.Spreadsheets spreadSheets, String sheetId, List<String> ranges) throws IOException {
        String fields = "sheets.data.rowData.values(effectiveValue,userEnteredValue)";
        Spreadsheet spreadsheet = 
                spreadSheets.get(sheetId) // (1) スプレッドシートのID(スプレッドシートのURLに含まれます)
                .setRanges(ranges)    // (2) 範囲
                .setFields(fields)    // (3) 取得する内容の種類
                .execute();
        return spreadsheet;
    }

番号	内容	指定例
(1)	スプレッドシートのID	1iyxSMVTG9JsYxHhoyjV__xgf_Is39gpDYJysIb1ba70
(2)	範囲	List.of("シート1!A2:D5")
(3)	取得する内容の種類	sheets.data.rowData.values(effectiveValue,userEnteredValue)

スプレッドシートのID

GoogleスプレッドシートのURLにある https://docs.google.com/spreadsheets/d/1iyxSMVTG9JsYxHhoyjV__xgf_Is39gpDYJysIb1ba70 の d より後ろにあるのがスプレッドシートのIDです。

範囲

データを取得する範囲です。スプレッドシートの関数で範囲指定するときと同じ記述で指定します。複数の範囲を指定できるよう範囲をjava.util.Listインタフェースで定義できます。

取得する内容の種類

スプレッドシートから取得するセルの内容を指定します。単純にセルの値を取るだけでなく、表示フォーマットで変換したあとの値や、実際に入力していた値も取得できます。

実際にセルの値を取得する

以下のかんたんなスプレッドシートを用意しました。このA2からD5の値を取得してみましょう。

データを取り出すスプレッドシート

先ほど作成した Serviceクラスに以下のメソッドを追加します。

@Service
@AllArgsConstructor
public class SpreadSheetReadService { 
...(中略)...

    public Spreadsheet getSheet(Sheets.Spreadsheets spreadSheets, String sheetId, List<String> ranges) throws IOException {
        String fields = "sheets.data.rowData.values(effectiveValue,userEnteredValue,formattedValue)";
        Spreadsheet spreadsheet = spreadSheets.get(sheetId)
                .setRanges(ranges)
                .setFields(fields)
                .execute();
        return spreadsheet;
    }
}

このメソッドを呼び出す実装は、例えば以下のようになります。

@Component
@AllArgsConstructor
public class SampleRead {
    SpreadSheetReadService service;

    public void readSample() {
        Sheets.Spreadsheets spreadSheets = service.getSpreadSheetInstance();
        Sheets.SpreadSheet spreadSheet = service.getSheet(spreadSheets, "1iyxSMVTG9JsYxHhoyjV__xgf_Is39gpDYJysIb1ba70", List.of("シート1!A2:D5"));

        spreadsheet.getSheets().forEach(sheet -> {

            List<GridData> gridDataList = sheet.getData();
            gridDataList.forEach(gridData -> {
                List<RowData> rowDataList = gridData.getRowData();

                // 行データの取得。行データは複数のセルを持つ
                rowDataList.forEach(rowData -> {
                    List<CellData> cellDataList = rowData.getValues();

                    // セルの取得
                    cellDataList.forEach(cellData -> {
                        ExtendedValue userEnteredValue = cellData.getUserEnteredValue();    // 入力値
                        String string = userEnteredValue.getStringValue();  // 文字列型の場合
                        Double number = userEnteredValue.getNumberValue();  // 数値型の場合

                        ExtendedValue effectiveValue = cellData.getEffectiveValue();    // 変換後の値
                        log.info(userEnteredValue.toString());
                    });
                });
            });
        });
    }
}

セルの値を取得するときの留意点として、セルの型が数値型か文字列型かで値の取得が変わります。セルが数値型のときに、getStringValue() の値はnullになり、その逆も然りで、文字列型のときは getNumberValue() はnullになります。

ExtendedValueのtoString() の値は JSONで得られ、型と値の両方が取得できます。例えば以下です。

{"stringValue":"s-9022"}

{"numberValue":3800.0}

この結果を、実際にインポートするクラスへ格納することでスプレッドシートのデータをデータベースへ格納することや、または別のデータに変換して画面へ出力することもできるでしょう。

実際の運用シーンへの適用とこれから

実際に運用しているスプレッドシートは数十枚にもおよびますが、スプレッドシートなどの表計算アプリケーションは自由に項目を増減できる反面、変更したときの影響範囲が見えないためシステムへの影響も見えません。 アプリケーションで実装することで柔軟性は落ちるようにも見えますが、しかしそれはシステムの堅牢さにもつながります。 定常運用しているデータであれば、なおのこと人の手による不安定な要素を消しておくことで安定確実なシステムへとなるでしょう。

さいごに

アソビューでは「生きるに、遊びを。」をミッションに、一緒に働くメンバーを募集しています！ エンジニアとして様々なキャリアチャレンジの機会が用意されています。 ご興味がありましたらお気軽にご応募いただければと思います！

www.asoview.co.jp