JavaからGoogleスプレッドシートを操作する

投稿日: 2019-10-18 2022-10-14
タグ: Cloud

1. 概要

Googleスプレッドシートは、スプレッドシートを保存および操作し、ドキュメント上で他のユーザーと共同作業するための便利な方法を提供します。

自動化された操作を実行する場合など、アプリケーションからこれらのドキュメントにアクセスすると便利な場合があります。この目的のために、Googleは開発者が対話できるGoogleSheetsAPIを提供しています。

この記事では、 APIに接続して、Googleスプレッドシートで操作を実行する方法を見ていきます。

2. Mavenの依存関係

APIに接続してドキュメントを操作するには、 google-api-client 、 google-oauth-client-jetty 、google-api-servicesを追加する必要があります-sheets の依存関係：

<dependency>
    <groupId>com.google.api-client</groupId>
    <artifactId>google-api-client</artifactId>
    <version>1.23.0</version>
</dependency>
<dependency>
    <groupId>com.google.oauth-client</groupId>
    <artifactId>google-oauth-client-jetty</artifactId>
    <version>1.23.0</version>
</dependency>
<dependency>
    <groupId>com.google.apis</groupId>
    <artifactId>google-api-services-sheets</artifactId>
    <version>v4-rev493-1.23.0</version>
</dependency>

3. 承認

Google Sheets APIには、アプリケーションからアクセスする前にOAuth2.0認証が必要です。

まず、OAuthクレデンシャルのセットを取得する必要があります。次に、これをアプリケーションで使用して、承認のリクエストを送信します。

3.1. OAuth2.0クレデンシャルの取得

クレデンシャルを取得するには、 Google Developers Console でプロジェクトを作成してから、プロジェクトのGoogleSheetsAPIを有効にする必要があります。 Googleクイックスタートガイドの最初のステップには、これを行う方法の詳細情報が含まれています。

クレデンシャル情報を含むJSONファイルをダウンロードしたら、の src / main /resourcesディレクトリにあるgoogle-sheets-client-secret.jsonファイルの内容をコピーしましょう。私たちのアプリケーション。

ファイルの内容は次のようになります。

{
  "installed":
    {
      "client_id":"<your_client_id>",
      "project_id":"decisive-octane-187810",
      "auth_uri":"https://accounts.google.com/o/oauth2/auth",
      "token_uri":"https://accounts.google.com/o/oauth2/token",
      "auth_provider_x509_cert_url":"https://www.googleapis.com/oauth2/v1/certs",
      "client_secret":"<your_client_secret>",
      "redirect_uris":["urn:ietf:wg:oauth:2.0:oob","http://localhost"]
    }
}

**3.2. クレデンシャルオブジェクトの取得**

承認が成功すると、GoogleSheetsAPIとのやり取りに使用できるCredentialオブジェクトが返されます。

上記のJSONファイルのコンテンツを読み取り、 GoogleClientSecretsオブジェクトを構築する静的なauthorize（）メソッドを使用してGoogleAuthorizeUtilクラスを作成しましょう。

次に、 GoogleAuthorizationCodeFlow を作成し、承認リクエストを送信します。

public class GoogleAuthorizeUtil {
    public static Credential authorize() throws IOException, GeneralSecurityException {
        
        // build GoogleClientSecrets from JSON file

        List<String> scopes = Arrays.asList(SheetsScopes.SPREADSHEETS);

        // build Credential object

        return credential;
    }
}

この例では、Googleスプレッドシートにアクセスし、メモリ内の DataStoreFactory を使用して受信したクレデンシャルを保存するため、SPREADSHEETSスコープを設定しています。もう1つのオプションは、FileDataStoreFactoryを使用して資格情報をファイルに保存することです。

GoogleAuthorizeUtil cla ssの完全なソースコードについては、GitHubプロジェクトを確認してください。

**4. スプレッドシートサービスインスタンスの構築**

Googleスプレッドシートを操作するには、APIを介して読み取りと書き込みを行うためのクライアントであるaSheetsオブジェクトが必要です。

上記のCredentialオブジェクトを使用してSheets：のインスタンスを取得するSheetsServiceUtilクラスを作成しましょう。

public class SheetsServiceUtil {
    private static final String APPLICATION_NAME = "Google Sheets Example";

    public static Sheets getSheetsService() throws IOException, GeneralSecurityException {
        Credential credential = GoogleAuthorizeUtil.authorize();
        return new Sheets.Builder(
          GoogleNetHttpTransport.newTrustedTransport(), 
          JacksonFactory.getDefaultInstance(), credential)
          .setApplicationName(APPLICATION_NAME)
          .build();
    }
}

次に、APIを使用して実行できる最も一般的な操作のいくつかを見ていきます。

5. シートに値を書く

既存のスプレッドシートを操作するには、そのスプレッドシートのIDを知っている必要があります。これは、そのURLから見つけることができます。

この例では、次の場所にある「Expenses」という公開スプレッドシートを使用します。

https://docs.google.com/spreadsheets/d/1sILuxZUnyl_7-MlNThjt765oWshN3Xs-PPLfqYe4DhI/edit#gid=0

このURLに基づいて、このスプレッドシートのIDを「1sILuxZUnyl_7-MlNThjt765oWshN3Xs-PPLfqYe4DhI」として識別できます。

また、値の読み取りと書き込みには、spreadsheets.valuesコレクションを使用します。

値はValueRangeオブジェクトとして表されます。これは、シートの行または列に対応するJavaオブジェクトのリストのリストです。

SheetsサービスオブジェクトとSPREADSHEET_ID定数を初期化するテストクラスを作成しましょう。

public class GoogleSheetsLiveTest {
    private static Sheets sheetsService;
    private static String SPREADSHEET_ID = // ...

    @BeforeClass
    public static void setup() throws GeneralSecurityException, IOException {
        sheetsService = SheetsServiceUtil.getSheetsService();
    }
}

次に、次の方法で値を書き込むことができます。

単一の範囲への書き込み
複数の範囲への書き込み
テーブルの後にデータを追加する

5.1. 単一範囲への書き込み

シート上の単一の範囲に値を書き込むには、 Spreadsheets（）。values（）。update（）メソッドを使用します。

@Test
public void whenWriteSheet_thenReadSheetOk() throws IOException {
    ValueRange body = new ValueRange()
      .setValues(Arrays.asList(
        Arrays.asList("Expenses January"), 
        Arrays.asList("books", "30"), 
        Arrays.asList("pens", "10"),
        Arrays.asList("Expenses February"), 
        Arrays.asList("clothes", "20"),
        Arrays.asList("shoes", "5")));
    UpdateValuesResponse result = sheetsService.spreadsheets().values()
      .update(SPREADSHEET_ID, "A1", body)
      .setValueInputOption("RAW")
      .execute();
}

ここでは、最初に、2か月間の経費のリストを含む複数の行を持つValueRangeオブジェクトを作成しています。

次に、 update（）メソッドを使用して、「A1」セルから開始して、指定されたIDでスプレッドシートに値を書き込むリクエストを作成します。

リクエストを送信するには、 execute（）メソッドを使用しています。

値セットを行ではなく列と見なしたい場合は、 setMajorDimension（ “COLUMNS”）メソッドを使用できます。

「RAW」入力オプションは、値がそのまま書き込まれ、計算されないことを意味します。

このJUnitテストを実行すると、アプリケーションはシステムのデフォルトブラウザを使用してブラウザウィンドウを開き、ユーザーにログインして、ユーザーに代わってGoogleスプレッドシートを操作する権限をアプリケーションに付与します。

OAuthサービスアカウントをお持ちの場合は、この手動の手順を省略できることに注意してください。

アプリケーションがスプレッドシートを表示または編集できるようにするための要件は、サインインしたユーザーがスプレッドシートへの表示または編集アクセス権を持っていることです。それ以外の場合、リクエストは403エラーになります。この例で使用するスプレッドシートは、パブリック編集アクセスに設定されています。

ここで、スプレッドシートを確認すると、「 A1：B6」の範囲が値セットで更新されていることがわかります。

1つのリクエストで複数の異なる範囲への書き込みに移りましょう。

5.2. 複数の範囲への書き込み

シート上の複数の範囲を更新する場合は、BatchUpdateValuesRequestを使用してパフォーマンスを向上させることができます。

List<ValueRange> data = new ArrayList<>();
data.add(new ValueRange()
  .setRange("D1")
  .setValues(Arrays.asList(
    Arrays.asList("January Total", "=B2+B3"))));
data.add(new ValueRange()
  .setRange("D4")
  .setValues(Arrays.asList(
    Arrays.asList("February Total", "=B5+B6"))));

BatchUpdateValuesRequest batchBody = new BatchUpdateValuesRequest()
  .setValueInputOption("USER_ENTERED")
  .setData(data);

BatchUpdateValuesResponse batchResult = sheetsService.spreadsheets().values()
  .batchUpdate(SPREADSHEET_ID, batchBody)
  .execute();

この例では、最初に ValueRanges、のリストを作成しています。各セルは、月の名前と総費用を表す2つのセルで構成されています。

次に、[RAW]ではなく入力オプション「USER_ENTERED」を使用して BatchUpdateValuesRequest を作成します。これは、セル値が他の2つを加算する式に基づいて計算されることを意味します。セル。

最後に、batchUpdateリクエストを作成して送信します。その結果、範囲「 D1：E1」および「D4：E4」が更新されます。

5.3. テーブルの後にデータを追加する

シートに値を書き込む別の方法は、テーブルの最後に値を追加することです。

このために、 append（）メソッドを使用できます。

ValueRange appendBody = new ValueRange()
  .setValues(Arrays.asList(
    Arrays.asList("Total", "=E1+E4")));
AppendValuesResponse appendResult = sheetsService.spreadsheets().values()
  .append(SPREADSHEET_ID, "A1", appendBody)
  .setValueInputOption("USER_ENTERED")
  .setInsertDataOption("INSERT_ROWS")
  .setIncludeValuesInResponse(true)
  .execute();
        
ValueRange total = appendResult.getUpdates().getUpdatedData();
assertThat(total.getValues().get(0).get(1)).isEqualTo("65");

まず、追加するセル値を含むValueRangeオブジェクトを作成します。

この場合、これには、「E1」と「E2」のセル値を加算して求めた両方の月の合計費用を含むセルが含まれています。

次に、「A1」セルを含むテーブルの後にデータを追加するリクエストを作成しています。

INSERT_ROWS オプションは、データを新しい行に追加し、テーブルの後の既存のデータを置き換えないことを意味します。これは、例が最初の実行で範囲「 A7：B7」を書き込むことを意味します。

以降の実行では、「A1」セルで始まるテーブルが拡張され、「A7：B7」行が含まれるようになるため、新しい行が“に移動します。 A8：B8”行など。

また、設定する必要があります includeValuesInResponse リクエストへの応答を確認する場合は、プロパティをtrueにします。その結果、応答オブジェクトには更新されたデータが含まれます。

6. シートから値を読み取る

シートから値を読み取って、値が正しく書き込まれたことを確認しましょう。

これを行うには、 Spreadsheets（）。values（）。get（）メソッドを使用して単一の範囲を読み取るか、 batchUpdate（）メソッドを使用して複数の範囲を読み取ります。

List<String> ranges = Arrays.asList("E1","E4");
BatchGetValuesResponse readResult = sheetsService.spreadsheets().values()
  .batchGet(SPREADSHEET_ID)
  .setRanges(ranges)
  .execute();
        
ValueRange januaryTotal = readResult.getValueRanges().get(0);
assertThat(januaryTotal.getValues().get(0).get(0))
  .isEqualTo("40");

ValueRange febTotal = readResult.getValueRanges().get(1);
assertThat(febTotal.getValues().get(0).get(0))
  .isEqualTo("25");

ここでは、「E1」と「E4」の範囲を読み取り、前に書き込んだ各月の合計が含まれていることを確認しています。

7. 新しいスプレッドシートの作成

値の読み取りと更新に加えて、 Spreadsheets（）および Spreadsheets（）。sheets（）コレクションを使用してシートまたはスプレッドシート全体を操作することもできます。

新しいスプレッドシートを作成する例を見てみましょう。

@Test
public void test() throws IOException {
    Spreadsheet spreadSheet = new Spreadsheet().setProperties(
      new SpreadsheetProperties().setTitle("My Spreadsheet"));
    Spreadsheet result = sheetsService
      .spreadsheets()
      .create(spreadSheet).execute();
        
    assertThat(result.getSpreadsheetId()).isNotNull();   
}

ここでは、最初に「 My Spreadsheet」というタイトルのSpreadsheet オブジェクトを作成し、次に create（）[を使用してリクエストを作成して送信します。 X165X]およびexecute（）メソッド。

新しいスプレッドシートは非公開になり、サインインしたユーザーのドライブに配置されます。

8. その他の更新操作

他のほとんどの操作は、 Request オブジェクトの形式を取り、それをリストに追加して、BatchUpdateSpreadsheetRequest。を作成するために使用します。

スプレッドシートのタイトルを変更し、セルのセットを1つのシートから別のシートにコピーアンドペーストする2つのリクエストを送信する方法を見てみましょう。

@Test
public void whenUpdateSpreadSheetTitle_thenOk() throws IOException {
    UpdateSpreadsheetPropertiesRequest updateSpreadSheetRequest 
      = new UpdateSpreadsheetPropertiesRequest().setFields("*")
      .setProperties(new SpreadsheetProperties().setTitle("Expenses"));
                
    CopyPasteRequest copyRequest = new CopyPasteRequest()
      .setSource(new GridRange().setSheetId(0)
        .setStartColumnIndex(0).setEndColumnIndex(2)
        .setStartRowIndex(0).setEndRowIndex(1))
      .setDestination(new GridRange().setSheetId(1)
        .setStartColumnIndex(0).setEndColumnIndex(2)
        .setStartRowIndex(0).setEndRowIndex(1))
      .setPasteType("PASTE_VALUES");
        
    List<Request> requests = new ArrayList<>();
    requests.add(new Request()
      .setCopyPaste(copyRequest));
    requests.add(new Request()
      .setUpdateSpreadsheetProperties(updateSpreadSheetRequest));
        
    BatchUpdateSpreadsheetRequest body 
      = new BatchUpdateSpreadsheetRequest().setRequests(requests);
        
    sheetsService.spreadsheets().batchUpdate(SPREADSHEET_ID, body).execute();
}

ここでは、新しいタイトルを指定する UpdateSpreadSheetPropertiesRequest オブジェクト、操作のソースと宛先を含む CopyPasteRequest オブジェクトを作成し、これらのオブジェクトをリストに追加しています。 リクエストの。

次に、両方のリクエストをバッチ更新として実行しています。

他の多くのタイプのリクエストも同様の方法で使用できます。たとえば、 AddSheetRequest を使用してスプレッドシートに新しいシートを作成したり、FindReplaceRequest。を使用して値を変更したりできます。

境界線の変更、フィルターの追加、セルの結合など、他の操作を実行できます。 リクエストタイプの完全なリストは、こちらで入手できます。

9. 結論

この記事では、JavaアプリケーションからGoogle Sheets APIに接続する方法と、GoogleSheetsに保存されているドキュメントを操作するいくつかの例を見てきました。

例の完全なソースコードは、GitHubのにあります。

getdocs

13036