More than 5 years have passed since last update.

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

Last updated at 2015-09-29Posted at 2015-09-08

概要

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

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

環境

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

Windows7 (64bit)
Java 1.8.0_60
[google-api-java-client] (https://github.com/google/google-api-java-client) 1.20.0
[gdata-java-client] (https://github.com/google/gdata-java-client) 1.47.1
JavaMail 1.4.5

参考

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

[The Google Sheets API] (https://developers.google.com/google-apps/spreadsheets/)
[GData APIでGoogleスプレッドシートを参照するには (1/3)] (http://www.atmarkit.co.jp/ait/articles/1001/15/news121.html)
[Google API 用のアクセストークンをサービスアカウントで取得 - Google API Client Library for Java] (http://fits.hatenablog.com/entry/2015/06/08/223006)
[Google Spreadsheet with a service account] (http://stackoverflow.com/questions/31287911/google-spreadsheet-with-a-service-account)

事前準備

APIの有効化

Drive APIを有効化します。

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

認証情報の追加

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

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

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

使用するライブラリ

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>

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

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

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

アプリケーションの開発

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

プロジェクト名: 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] (https://developers.google.com/google-apps/spreadsheets/)で説明されているコードを参考にしています。

定数

// アプリケーション名 (任意)
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");
  }

  ...他の処理...

}

実行結果

メモ

少しはまった点について

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

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

行の追加ができない

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

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up