LoginSignup
72
91

More than 5 years have passed since last update.

@rubytomato@github(渡邉 真)

Google Sheets API (Java)を使用してGoogle スプレッドシートを更新する

Last updated at Posted at 2015-09-08

概要

Google スプレッドシートをJavaアプリケーションから操作するサンプルプログラムです。
想定するのは、例えば「バッチプログラムでデータベースから集計したデータをGoogle Sheets APIを使用してGoogle スプレッドシートへ更新する」などです。

Googleの認証方法にはOAuth2やサービスアカウントなどがありますが、バッチ処理などユーザーが直接介さない場合の認証方法はサービスアカウントを使用する方が良いようです。

環境

下記の環境で動作確認を行いました。

参考

下記のサイトを参考にさせて頂きました。

事前準備

APIの有効化

Drive APIを有効化します。

  • Google Developers Consoleにアクセスします。
  • プロジェクトを登録していないか専用のプロジェクトを用意したい場合は登録します。
  • 左メニューの"APIと認証" -> "API"を選択します。
  • APIライブラリの一覧から -> "Google Apps API" -> "Drvie API"を有効化します。

ss02.png

認証情報の追加

サービスアカウントを追加します。

  • 左メニューの"APIと認証" -> "認証情報"を選択します。
  • "認証情報を追加"ボタンをクリックします。
  • メニューの"サービスアカウント"を選択します。
  • キーのタイプに"P12"を選択します。

ss08.png

ss09.png

サービスアカウントの追加が成功すると、クライアントID、メールアドレス、証明書フィンガープリントが発行され、P12キーファイルのダウンロードが行われます。

ss10.png

使用するライブラリ

google-api-java-client

dependency
<dependency>
  <groupId>com.google.api-client</groupId>
  <artifactId>google-api-client</artifactId>
  <version>1.20.0</version>
</dependency>

gdata-java-client

dependency
<dependency>
  <groupId>com.google.gdata</groupId>
  <artifactId>core</artifactId>
  <version>1.47.1</version>
  <exclusions>
    <exclusion>
      <groupId>com.google.code.findbugs</groupId>
      <artifactId>jsr305</artifactId>
    </exclusion>
  </exclusions>
</dependency>

テストで使用するスプレッドシートを作成

スプレッドシート名: テストスプレッドシート

ss11.png

上記で作成したサービスアカウントでこのスプレッドシートに書き込めるように権限を設定します。
シート右端にある共有ボタンをクリックし、サービスアカウントのメールアドレスを入力し送信ボタンをクリックします。

ss12.png

アプリケーションの開発

プロジェクトの雛形を生成

プロジェクト名: google-ss-example

mavenでアプリケーションの雛形を作成

> mvn archetype:generate -DgroupId=com.example.gss -DartifactId=google-ss-example -DarchetypeArtifactId=maven-archetype-quickstart -DinteractiveMode=false

> cd google-ss-example
> mvn eclipse:eclipse

eclipseにインポート

  • メニューバーの"File" -> "Import..." -> "Maven" -> "Existing Maven Projects"を選択します。
  • プロジェクトのディレクトリを選択し、"Finish"ボタンをクリックします。

pom.xmlの編集

pom.xml
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.example.gss</groupId>
  <artifactId>google-ss-example</artifactId>
  <packaging>jar</packaging>
  <version>1.0-SNAPSHOT</version>
  <name>google-ss-example</name>
  <url>http://maven.apache.org</url>

  <properties>
    <java.version>1.8</java.version>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  </properties>

  <dependencies>

    <dependency>
      <groupId>com.google.api-client</groupId>
      <artifactId>google-api-client</artifactId>
      <version>1.20.0</version>
    </dependency>

    <dependency>
      <groupId>com.google.gdata</groupId>
      <artifactId>core</artifactId>
      <version>1.47.1</version>
      <exclusions>
        <exclusion>
          <groupId>com.google.code.findbugs</groupId>
          <artifactId>jsr305</artifactId>
        </exclusion>
      </exclusions>
    </dependency>

    <dependency>
      <groupId>commons-collections</groupId>
      <artifactId>commons-collections</artifactId>
      <version>3.2.1</version>
    </dependency>
    <dependency>
      <groupId>org.apache.commons</groupId>
      <artifactId>commons-lang3</artifactId>
      <version>3.4</version>
    </dependency>

    <dependency>
      <groupId>org.slf4j</groupId>
      <artifactId>slf4j-api</artifactId>
      <version>1.7.12</version>
    </dependency>
    <dependency>
      <groupId>ch.qos.logback</groupId>
      <artifactId>logback-classic</artifactId>
      <version>1.1.3</version>
    </dependency>

    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>3.8.1</version>
      <scope>test</scope>
    </dependency>
  </dependencies>

  <build>
    <plugins>
      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-compiler-plugin</artifactId>
        <version>3.3</version>
        <configuration>
          <verbose>true</verbose>
          <source>${java.version}</source>
          <target>${java.version}</target>
          <encoding>${project.build.sourceEncoding}</encoding>
        </configuration>
      </plugin>
      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-jar-plugin</artifactId>
        <version>2.6</version>
        <configuration>
          <archive>
            <manifest>
              <mainClass>com.example.gss.App</mainClass>
              <addClasspath>true</addClasspath>
              <addExtensions>false</addExtensions>
              <classpathPrefix></classpathPrefix>
            </manifest>
          </archive>
        </configuration>
      </plugin>
      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-assembly-plugin</artifactId>
        <version>2.5.5</version>
        <configuration>
          <descriptorRefs>
            <descriptorRef>jar-with-dependencies</descriptorRef>
          </descriptorRefs>
        </configuration>
        <executions>
          <execution>
            <id>make-assembly</id>
            <phase>package</phase>
            <goals>
              <goal>single</goal>
            </goals>
          </execution>
        </executions>
      </plugin>
      <plugin>
        <groupId>org.codehaus.mojo</groupId>
        <artifactId>versions-maven-plugin</artifactId>
        <version>2.2</version>
      </plugin>
    </plugins>
  </build>

</project>

resourcesフォルダの作成

src/main/resourcesフォルダを作成しプロジェクトに反映させます。

  • "Build Path" -> "Configure Build Path" -> "Java Buld Path" -> "Source"タブを選択する。
  • "Add Folder"ボタンをクリック -> 作成した"resources"フォルダにチェックを入れる。

P12キーファイル

Google Developers Consoleで認証情報を追加したときにダウンロードしたP12キーファイルをsrc/main/resourcesフォルダにコピーします。

ビルド

この時点で動作検証を兼ねてビルドします。

package
> mvn package

ビルドが成功したら生成したjarファイルを実行します。
コマンドプロンプトに"Hello World!"と表示されれば成功です。

jar
> cd target
> java -jar google-ss-example-1.0-SNAPSHOT.jar
Hello World!

プログラムの実装

スプレッドシートの操作方法はGoogle Sheets APIで説明されているコードを参考にしています。

定数

// アプリケーション名 (任意)
private static final String APPLICATION_NAME = "spreadsheet-application-example";

// アカウント
private static final String ACCOUNT_P12_ID = "<追加したサービスアカウントのメールアドレス>";
private static final File P12FILE = new File("src/main/resources/<P12キーファイル名>");

// 認証スコープ
private static final List<String> SCOPES = Arrays.asList(
  "https://docs.google.com/feeds",
  "https://spreadsheets.google.com/feeds");

// Spreadsheet API URL
private static final String SPREADSHEET_URL = "https://spreadsheets.google.com/feeds/spreadsheets/private/full";

private static final URL SPREADSHEET_FEED_URL;

static {
  try {
    SPREADSHEET_FEED_URL = new URL(SPREADSHEET_URL);
  } catch (MalformedURLException e) {
    throw new RuntimeException(e);
  }
}
  • ACCOUNT_P12_IDに設定する値

サービスアカウントを登録するとクライアントIDとメールアドレスが発行されますが、プログラムで使用するのはメールアドレスのほうです。

認証処理

authorize
private static Credential authorize() throws Exception {
  System.out.println("authorize in");

  HttpTransport httpTransport = GoogleNetHttpTransport.newTrustedTransport();
  JsonFactory jsonFactory = new JacksonFactory();

  GoogleCredential credential = new GoogleCredential.Builder()
    .setTransport(httpTransport)
    .setJsonFactory(jsonFactory)
    .setServiceAccountId(ACCOUNT_P12_ID)
    .setServiceAccountPrivateKeyFromP12File(P12FILE)
    .setServiceAccountScopes(SCOPES)
    .build();

  boolean ret = credential.refreshToken();
  // debug dump
  System.out.println("refreshToken:" + ret);

  // debug dump
  if (credential != null) {
    System.out.println("AccessToken:" + credential.getAccessToken());
  }

  System.out.println("authorize out");

  return credential;
}

サービスの取得

getService
private static SpreadsheetService getService() throws Exception {
  System.out.println("service in");

  SpreadsheetService service = new SpreadsheetService(APPLICATION_NAME);
  service.setProtocolVersion(SpreadsheetService.Versions.V3);

  Credential credential = authorize();
  service.setOAuth2Credentials(credential);

  // debug dump
  System.out.println("Schema: " + service.getSchema().toString());
  System.out.println("Protocol: " + service.getProtocolVersion().getVersionString());
  System.out.println("ServiceVersion: " + service.getServiceVersion());

  System.out.println("service out");

  return service;
}

スプレッドシート一覧

アクセスが許可されているスプレッドシートの一覧を取得します。

findAllSpreadsheets
private static List<SpreadsheetEntry> findAllSpreadsheets(SpreadsheetService service) throws Exception {
  System.out.println("findAllSpreadsheets in");

  SpreadsheetFeed feed = service.getFeed(SPREADSHEET_FEED_URL, SpreadsheetFeed.class);

  List<SpreadsheetEntry> spreadsheets = feed.getEntries();

  // debug dump
  for (SpreadsheetEntry spreadsheet : spreadsheets) {
    System.out.println("title: " + spreadsheet.getTitle().getPlainText());
  }

  System.out.println("findAllSpreadsheets out");
  return spreadsheets;
}

スプレッドシート名で検索

スプレッドシート名を指定して検索します。

findSpreadsheetByName
private static SpreadsheetEntry findSpreadsheetByName(SpreadsheetService service, String spreadsheetName) throws Exception {
  System.out.println("findSpreadsheetByName in");
  SpreadsheetQuery sheetQuery = new SpreadsheetQuery(SPREADSHEET_FEED_URL);
  sheetQuery.setTitleQuery(spreadsheetName);
  SpreadsheetFeed feed = service.query(sheetQuery, SpreadsheetFeed.class);
  SpreadsheetEntry ssEntry = null;
  if (feed.getEntries().size() > 0) {
    ssEntry = feed.getEntries().get(0);
  }
  System.out.println("findSpreadsheetByName out");
  return ssEntry;
}

ワークシート名で検索

findWorksheetByName
private static WorksheetEntry findWorksheetByName(SpreadsheetService service, SpreadsheetEntry ssEntry, String sheetName) throws Exception {
  System.out.println("findWorksheetByName in");
  WorksheetQuery worksheetQuery = new WorksheetQuery(ssEntry.getWorksheetFeedUrl());
  worksheetQuery.setTitleQuery(sheetName);
  WorksheetFeed feed = service.query(worksheetQuery, WorksheetFeed.class);
  WorksheetEntry wsEntry = null;
  if (feed.getEntries().size() > 0){
    wsEntry = feed.getEntries().get(0);
  }
  System.out.println("findWorksheetByName out");
  return wsEntry;
}

ワークシートを追加

addWorksheet
private static WorksheetEntry addWorksheet(SpreadsheetService service, SpreadsheetEntry ssEntry, String sheetName, int colNum, int rowNum) throws Exception {
  System.out.println("addWorksheet in");
  WorksheetEntry wsEntry = new WorksheetEntry();
  wsEntry.setTitle(new PlainTextConstruct(sheetName));
  wsEntry.setColCount(colNum);
  wsEntry.setRowCount(rowNum);
  URL worksheetFeedURL = ssEntry.getWorksheetFeedUrl();
  System.out.println("addWorksheet out");
  return service.insert(worksheetFeedURL, wsEntry);
}

ワークシートを削除

deleteWorksheet
private static void deleteWorksheet(WorksheetEntry wsEntry) throws Exception {
  System.out.println("deleteWorksheet in");
  wsEntry.delete();
  System.out.println("deleteWorksheet out");
}

ワークシートのタイトル行を追加

引数のqueryでタイトル行(1行目)を指定し、その行のセルへタイトル名を書き込みます。

insertHeadRow
private static void insertHeadRow(SpreadsheetService service, WorksheetEntry wsEntry, List<String> header, String query) throws Exception {
  System.out.println("insertHeadRow in");

  URL cellFeedUrl = new URI(wsEntry.getCellFeedUrl().toString() + query).toURL();
  CellFeed cellFeed = service.getFeed(cellFeedUrl, CellFeed.class);

  for (int i=0; i<header.size(); i++) {
    cellFeed.insert(new CellEntry(1, i+1, header.get(i)));
  }

  System.out.println("insertHeadRow out");
}

ワークシートに行を追加

insertDataRow
private static void insertDataRow(SpreadsheetService service, WorksheetEntry wsEntry, Map<String, Object> values) throws Exception {
  System.out.println("insertDataRow in");

  ListEntry dataRow = new ListEntry();

  values.forEach((title,value)->{
    dataRow.getCustomElements().setValueLocal(title, value.toString());
  });

  URL listFeedUrl = wsEntry.getListFeedUrl();
  service.insert(listFeedUrl, dataRow);

  System.out.println("insertDataRow out");
}

ワークシートの行を更新

updateDataRow
private static void updateDataRow(SpreadsheetService service, WorksheetEntry wsEntry, int rowNum, Map<String, Object> values) throws Exception {
  System.out.println("updateDataRow in");

  URL listFeedUrl = wsEntry.getListFeedUrl();
  ListFeed listFeed = service.getFeed(listFeedUrl, ListFeed.class);

  ListEntry row = listFeed.getEntries().get(rowNum);

  values.forEach((title,value)->{
    row.getCustomElements().setValueLocal(title, value.toString());
  });

  row.update();

  System.out.println("updateDataRow out");
}

範囲指定クエリー

クエリーストリングでセルの範囲指定を行うことができます。

makeQuery
private static String makeQuery(int minrow, int maxrow, int mincol, int maxcol) {
  String base = "?min-row=MINROW&max-row=MAXROW&min-col=MINCOL&max-col=MAXCOL";
  return base.replaceAll("MINROW", String.valueOf(minrow))
             .replaceAll("MAXROW", String.valueOf(maxrow))
             .replaceAll("MINCOL", String.valueOf(mincol))
             .replaceAll("MAXCOL", String.valueOf(maxcol));
}

メイン

App.java
package com.example.gss;

import java.io.File;
import java.io.IOException;
import java.net.MalformedURLException;
import java.net.URI;
import java.net.URISyntaxException;
import java.net.URL;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.google.api.client.auth.oauth2.Credential;
import com.google.api.client.googleapis.auth.oauth2.GoogleCredential;
import com.google.api.client.googleapis.javanet.GoogleNetHttpTransport;
import com.google.api.client.http.HttpTransport;
import com.google.api.client.json.JsonFactory;
import com.google.api.client.json.jackson2.JacksonFactory;
import com.google.gdata.client.spreadsheet.SpreadsheetQuery;
import com.google.gdata.client.spreadsheet.SpreadsheetService;
import com.google.gdata.client.spreadsheet.WorksheetQuery;
import com.google.gdata.data.PlainTextConstruct;
import com.google.gdata.data.spreadsheet.CellEntry;
import com.google.gdata.data.spreadsheet.CellFeed;
import com.google.gdata.data.spreadsheet.ListEntry;
import com.google.gdata.data.spreadsheet.ListFeed;
import com.google.gdata.data.spreadsheet.SpreadsheetEntry;
import com.google.gdata.data.spreadsheet.SpreadsheetFeed;
import com.google.gdata.data.spreadsheet.WorksheetEntry;
import com.google.gdata.data.spreadsheet.WorksheetFeed;
import com.google.gdata.util.ServiceException;

public class App {

  public static void main(String[] args) throws Exception {
    System.out.println("main start");

    SpreadsheetService service = getService();

    findAllSpreadsheets(service);

    String ssName = "テストスプレッドシート";

    SpreadsheetEntry ssEntry = findSpreadsheetByName(service, ssName);

    String wsName = "テストシート";

    WorksheetEntry wsEntry = findWorksheetByName(service, ssEntry, wsName);
    if (wsEntry != null) {
      deleteWorksheet(wsEntry);
    }

    WorksheetEntry newWorksheet = addWorksheet(service, ssEntry, wsName, 50, 100);

    // ワークシートのタイトル名
    List<String> header = new ArrayList<>();
    header.add("test1");
    header.add("test2");
    header.add("test3");
    header.add("test4");
    header.add("test5");
    header.add("test6");

    insertHeadRow(service, newWorksheet, header, makeQuery(1,1,1,5));

    // debug dump
    specCell(service, newWorksheet, makeQuery(1,1,1,5));


    // insert
    Map<String, Object> insertValues1 = new HashMap<>();
    insertValues1.put("test1", "2015-09-01");
    insertValues1.put("test2", 1200);
    insertValues1.put("test3", 1300);
    insertValues1.put("test4", 1400);
    insertValues1.put("test5", 1500);
    insertValues1.put("test6", 1600);

    insertDataRow(service, newWorksheet, insertValues1);


    // insert
    Map<String, Object> insertValues2 = new HashMap<>();
    insertValues2.put("test1", "2015-09-02");
    insertValues2.put("test2", 2200);
    insertValues2.put("test3", 2300);
    insertValues2.put("test4", 2400);
    insertValues2.put("test5", 2500);
    insertValues2.put("test6", 2600);

    insertDataRow(service, newWorksheet, insertValues2);


    // update
    Map<String, Object> updateValues = new HashMap<>();
    updateValues.put("test1", "2015-09-01");
    updateValues.put("test2", 1202);
    updateValues.put("test3", 1303);
    updateValues.put("test4", 1404);
    updateValues.put("test5", 1505);
    updateValues.put("test6", 1606);

    updateDataRow(service, newWorksheet, 0, updateValues);

    System.out.println("main end");
  }

  ...他の処理...

}

実行結果

ss13.png

メモ

少しはまった点について

スプレッドシートが見つからない

サービスアカウントを共有ユーザーに追加しないと参照することができません。

行の追加ができない

行のタイトルに_(アンダースコア)が含まれていると行データを追加、更新することができませんでした。

72
91
3

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
72
91