用Java将数据写入Google Spreadsheet第二部分（Java端）
http://qiita.com/pilot/items/85bae4e5d5e35ffa7019 的后续内容
做过的事情 (zuò guò de shì
2. Java方面以下是在Windows操作系统上的表达方式。
为了将来导入Eclipse中的项目目录，创建一个名为“mytestapp1”的项目目录。
在该目录下创建以下文件
apply plugin: "java"
apply plugin: "application"
apply plugin: "eclipse"
repositories {
mavenCentral()
}
dependencies {
compile "com.google.api-client:google-api-client:1.22.0"
compile "com.google.oauth-client:google-oauth-client-jetty:1.22.0"
compile "com.google.apis:google-api-services-drive:v3-rev49-1.22.0"
compile "com.google.apis:google-api-services-sheets:v4-rev34-1.22.0"
compile "org.slf4j:slf4j-api:1.7.21"
compile "org.slf4j:jul-to-slf4j:1.7.21" // Googleライブラリがjava.util.loggingを使っているため
compile "ch.qos.logback:logback-classic:1.1.7"
}
compileJava {
options.encoding = "UTF-8"
}
sourceCompatibility = "1.8"
targetCompatibility = "1.8"
mainClassName = "mytestapp1.MyTestApp1"
task wrapper(type: Wrapper) {
gradleVersion = "3.1"
}
进入命令提示符，并在当前目录下执行以下操作。
-
- mkdir src\main\java src\main\resources
- gradle eclipse
使用Eclipse导入指定目录（”将现有项目导入工作区”）。
在完成「步骤1」后，将下载的JSON文件以文件名「google_client_secret.json」保存在src/main/resources目录下。
在Eclipse上创建以下新的类和XML文件。
package mytestapp1;
import java.io.IOException;
import java.io.Reader;
import java.security.GeneralSecurityException;
import java.util.Collection;
import java.util.List;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.google.api.client.auth.oauth2.Credential;
import com.google.api.client.auth.oauth2.CredentialRefreshListener;
import com.google.api.client.auth.oauth2.TokenErrorResponse;
import com.google.api.client.auth.oauth2.TokenResponse;
import com.google.api.client.extensions.java6.auth.oauth2.AuthorizationCodeInstalledApp;
import com.google.api.client.extensions.jetty.auth.oauth2.LocalServerReceiver;
import com.google.api.client.googleapis.auth.oauth2.GoogleAuthorizationCodeFlow;
import com.google.api.client.googleapis.auth.oauth2.GoogleAuthorizationCodeFlow.Builder;
import com.google.api.client.googleapis.auth.oauth2.GoogleClientSecrets;
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.api.client.util.store.FileDataStoreFactory;
import com.google.api.services.drive.Drive;
import com.google.api.services.drive.model.File;
import com.google.api.services.sheets.v4.Sheets;
public class GoogleService {
private static final Logger LOGGER = LoggerFactory.getLogger(GoogleService.class);
// Global instance of the JSON factory.
private static final JsonFactory JSON_FACTORY = JacksonFactory.getDefaultInstance();
// Global instance of the HTTP transport.
private static HttpTransport HTTP_TRANSPORT = null;
// Reader for client secret.
private Reader clientSecretReader;
// Collection of authorization scopes
private Collection<String> authorizationScopes;
// Directory to store user credentials
private String credentialStoreDirectory;
// application name
private String applicationName;
/**
* Constructor.
*
* @param clientSecretReader reader for client secret
* @param authorizationScopes collection of authorization scopes
* @param credentialStoreDirectory directory to store user credentials
* @param applicationName application name
*/
public GoogleService(Reader clientSecretReader, Collection<String> authorizationScopes, String credentialStoreDirectory, String applicationName) {
this.clientSecretReader = clientSecretReader;
this.authorizationScopes = authorizationScopes;
this.credentialStoreDirectory = credentialStoreDirectory;
this.applicationName = applicationName;
}
// Credential.
private Credential credential = null;
/**
* Creates an authorized Credential object.
*
* @throws IOException
* @throws GeneralSecurityException
*/
public void authorize() throws IOException, GeneralSecurityException {
if (credential != null) {
return;
}
// Load client secrets.
GoogleClientSecrets clientSecrets = GoogleClientSecrets.load(JSON_FACTORY, clientSecretReader);
// instance of the {@link FileDataStoreFactory}.
FileDataStoreFactory dataStoreFactory = new FileDataStoreFactory(new java.io.File(credentialStoreDirectory));
// ↑ Windowsでは「unable to change permissions～」ログが出力される
if (HTTP_TRANSPORT == null) {
HTTP_TRANSPORT = GoogleNetHttpTransport.newTrustedTransport();
}
// Build flow and trigger user authorization request.
Builder builder = new GoogleAuthorizationCodeFlow.Builder(HTTP_TRANSPORT, JSON_FACTORY, clientSecrets, authorizationScopes);
builder.setDataStoreFactory(dataStoreFactory).setAccessType("offline");
// ↑
// AccessType「offline」でRefreshTokenを得る(AccessTokenのexpire前60秒以後のAPI呼出時に自動refreshが行われるようになる)
builder.addRefreshListener(new CredentialRefreshListener() {
@Override
public void onTokenResponse(Credential credential, TokenResponse tokenResponse) throws IOException {
LOGGER.info("AccessTokenのrefreshが成功しました。(AccessToken=[{}], ExpiresInSeconds={}, RefreshToken=[{}])", credential.getAccessToken(), credential.getExpiresInSeconds(), credential.getRefreshToken());
}
@Override
public void onTokenErrorResponse(Credential credential, TokenErrorResponse tokenErrorResponse) throws IOException {
LOGGER.error("AccessTokenのrefreshが失敗しました。(Error=[{}], ErrorDescription=[{}], ErrorUri=[{}])", tokenErrorResponse.getError(), tokenErrorResponse.getErrorDescription(), tokenErrorResponse.getErrorUri());
}
});
// ↑ AccessTokenのrefresh後のListner
GoogleAuthorizationCodeFlow flow = builder.build();
credential = new AuthorizationCodeInstalledApp(flow, new LocalServerReceiver()).authorize("user");
// ↑ 初回はブラウザがGoogleの許可リクエスト画面を表示する(関連ログも出力される)
// → 「許可」押下でローカルJettyにリダイレクトされ、Credentialがファイルに保存される
// → 以後はCredentialファイルがある限りブラウザは起動しない(自動refreshのおかげ)
// → サーバで実行する場合はローカルPCで作成したCredentialファイルをサーバに配置しておく
// → 何らかのエラーでサーバ上のCredentialファイルが無効になった場合は当時のファイルを再度配置する
LOGGER.info("AccessTokenを取得しました。(AccessToken=[{}], ExpiresInSeconds={}, RefreshToken=[{}])", credential.getAccessToken(), credential.getExpiresInSeconds(), credential.getRefreshToken());
}
/**
* Build and return an authorized Drive client service.
*
* @return an authorized Drive client service
* @throws GeneralSecurityException
* @throws IOException
*/
public Drive getDriveService() throws IOException, GeneralSecurityException {
if (credential == null) {
authorize();
}
return new Drive.Builder(HTTP_TRANSPORT, JSON_FACTORY, credential).setApplicationName(applicationName).build();
}
/**
* Build and return an authorized Sheets API client service.
*
* @return an authorized Sheets API client service
* @throws GeneralSecurityException
* @throws IOException
*/
public Sheets getSheetsService() throws IOException, GeneralSecurityException {
if (credential == null) {
authorize();
}
return new Sheets.Builder(HTTP_TRANSPORT, JSON_FACTORY, credential).setApplicationName(applicationName).build();
}
SheetsWrapper getSheetsWrapperWithWorksheet(String folderName, String spreadsheetName, String worksheetName) throws IOException, GeneralSecurityException {
DriveWrapper driveWrapper = new DriveWrapper(getDriveService());
Sheets sheetsService = getSheetsService();
List<File> folderList = driveWrapper.searchFolder(folderName, DriveWrapper.ROOT_FOLDER_ID);
// ↑ AccessTokenのexpire前60秒以後、ここでAccessTokenのrefreshが実行される
String folderId = null;
if (folderList.size() == 0) {
folderId = driveWrapper.createFolder(folderName, DriveWrapper.ROOT_FOLDER_ID).getId();
LOGGER.info("フォルダ '{}' を作成しました。", folderName);
} else if (folderList.size() == 1) {
folderId = folderList.get(0).getId();
} else {
throw new IOException(String.format("フォルダ '%s' が複数存在しています。", folderName));
}
List<File> spreadsheetList = driveWrapper.searchSpreadsheet(spreadsheetName, folderId);
SheetsWrapper sheetsWrapper = null;
if (spreadsheetList.size() == 0) {
String spreadsheetId = driveWrapper.createSpreadsheet(spreadsheetName, folderId).getId();
sheetsWrapper = new SheetsWrapper(sheetsService, spreadsheetId);
LOGGER.info("スプレッドシート '{}' を作成しました。", spreadsheetName);
sheetsWrapper.renameWorksheet(0, worksheetName);
LOGGER.info("ワークシート '{}' を作成しました。", worksheetName);
} else if (spreadsheetList.size() == 1) {
String spreadsheetId = spreadsheetList.get(0).getId();
sheetsWrapper = new SheetsWrapper(sheetsService, spreadsheetId);
if (sheetsWrapper.getWorksheetProperties(worksheetName) == null) {
sheetsWrapper.addWorksheet(worksheetName);
LOGGER.info("ワークシート '{}' を作成しました。", worksheetName);
}
} else {
throw new IOException(String.format("スプレッドシート '%s' が複数存在しています。", spreadsheetName));
}
return sheetsWrapper;
}
}
package mytestapp1;
import java.io.IOException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.google.api.client.googleapis.json.GoogleJsonResponseException;
import com.google.api.client.googleapis.services.json.AbstractGoogleJsonClientRequest;
public class RequestUtil {
private static final Logger LOGGER = LoggerFactory.getLogger(RequestUtil.class);
private static final int RETRY_COUNT = 10;
private static final int RETRY_INTERVAL_SECONDS = 10;
static <R extends AbstractGoogleJsonClientRequest<T>, T> T executeWithRetry(R request) throws IOException {
for (int i = 0; i < (RETRY_COUNT - 1); i++) {
try {
return request.execute();
} catch (GoogleJsonResponseException e) {
if (e.getStatusCode() == 503) {
LOGGER.warn("Googleからリトライ可能なエラーが返却されました。", e);
LOGGER.warn("{}秒後にリトライを行います。({}/{}回目)", RETRY_INTERVAL_SECONDS * (i + 1), i + 1, RETRY_COUNT);
try {
Thread.sleep(RETRY_INTERVAL_SECONDS * (i + 1) * 1000L);
} catch (InterruptedException e2) {
// do nothing
}
}
}
}
return request.execute();
}
}
package mytestapp1;
import java.io.IOException;
import java.util.Arrays;
import java.util.List;
import com.google.api.services.drive.Drive;
import com.google.api.services.drive.model.File;
public class DriveWrapper {
private Drive driveService;
private static final String QUERY_FORMAT = "name = '%s' and '%s' in parents and mimeType = '%s' and trashed = false";
private static final String MIMETYPE_FOLDER = "application/vnd.google-apps.folder";
private static final String MIMETYPE_SPREADSHEET = "application/vnd.google-apps.spreadsheet";
public static final String ROOT_FOLDER_ID = "root";
public DriveWrapper(Drive driveService) {
this.driveService = driveService;
}
public List<File> searchFile(String name, String parentFolderId, String mimeType) throws IOException {
String query = String.format(QUERY_FORMAT, name, parentFolderId, mimeType);
return RequestUtil.executeWithRetry(driveService.files().list().setQ(query)).getFiles();
}
public List<File> searchFolder(String name, String parentFolderId) throws IOException {
return searchFile(name, parentFolderId, MIMETYPE_FOLDER);
}
public List<File> searchSpreadsheet(String name, String parentFolderId) throws IOException {
return searchFile(name, parentFolderId, MIMETYPE_SPREADSHEET);
}
public File createFile(String name, String parentFolderId, String mimeType) throws IOException {
File metadata = new File().setName(name).setMimeType(mimeType).setParents(Arrays.asList(parentFolderId));
return RequestUtil.executeWithRetry(driveService.files().create(metadata));
}
public File createFolder(String name, String parentFolderId) throws IOException {
return createFile(name, parentFolderId, MIMETYPE_FOLDER);
}
public File createSpreadsheet(String name, String parentFolderId) throws IOException {
return createFile(name, parentFolderId, MIMETYPE_SPREADSHEET);
}
}
package mytestapp1;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import com.google.api.services.sheets.v4.Sheets;
import com.google.api.services.sheets.v4.model.AddSheetRequest;
import com.google.api.services.sheets.v4.model.BatchUpdateSpreadsheetRequest;
import com.google.api.services.sheets.v4.model.GridProperties;
import com.google.api.services.sheets.v4.model.Request;
import com.google.api.services.sheets.v4.model.Sheet;
import com.google.api.services.sheets.v4.model.SheetProperties;
import com.google.api.services.sheets.v4.model.Spreadsheet;
import com.google.api.services.sheets.v4.model.UpdateSheetPropertiesRequest;
import com.google.api.services.sheets.v4.model.ValueRange;
public class SheetsWrapper {
private Sheets sheetsService;
private Spreadsheet spreadsheet;
public SheetsWrapper(Sheets sheetsService, String fileId) throws IOException {
this.sheetsService = sheetsService;
this.spreadsheet = RequestUtil.executeWithRetry(sheetsService.spreadsheets().get(fileId).setIncludeGridData(false));
}
public void renameWorksheet(int index, String newName) throws IOException {
SheetProperties sheetProperties = new SheetProperties().setIndex(index).setTitle(newName);
UpdateSheetPropertiesRequest updateSheetPropertiesRequest = new UpdateSheetPropertiesRequest().setProperties(sheetProperties).setFields("title");
List<Request> requests = new ArrayList<>();
requests.add(new Request().setUpdateSheetProperties(updateSheetPropertiesRequest));
RequestUtil.executeWithRetry(sheetsService.spreadsheets().batchUpdate(spreadsheet.getSpreadsheetId(), new BatchUpdateSpreadsheetRequest().setRequests(requests)));
spreadsheet = RequestUtil.executeWithRetry(sheetsService.spreadsheets().get(spreadsheet.getSpreadsheetId()).setIncludeGridData(false));
// ↑
// rename後のgetSheets().get().getProperties().getTitle()がrename前の値を返す問題の対応
}
// 見つからないときはnullを返す
public SheetProperties getWorksheetProperties(String worksheetName) {
for (int index = 0; index < spreadsheet.getSheets().size(); index++) {
Sheet ws = spreadsheet.getSheets().get(index);
if (ws.getProperties().getTitle().equals(worksheetName)) {
return ws.getProperties();
}
}
return null;
}
public void addWorksheet(String worksheetName) throws IOException {
SheetProperties sheetProperties = new SheetProperties().setTitle(worksheetName);
AddSheetRequest addSheetRequest = new AddSheetRequest().setProperties(sheetProperties);
List<Request> requests = new ArrayList<>();
requests.add(new Request().setAddSheet(addSheetRequest));
RequestUtil.executeWithRetry(sheetsService.spreadsheets().batchUpdate(spreadsheet.getSpreadsheetId(), new BatchUpdateSpreadsheetRequest().setRequests(requests)));
spreadsheet = RequestUtil.executeWithRetry(sheetsService.spreadsheets().get(spreadsheet.getSpreadsheetId()).setIncludeGridData(false));
// ↑ add後のgetSheets()がaddしたシートを返さない問題の対応
}
// 値取得
public List<List<Object>> getValues(String worksheetName, int startColNum, int startRowNum, int endColNum, int endRowNum) throws IOException {
boolean specifyColRow = startColNum > 0 || startRowNum > 0 || endColNum > 0 || endRowNum > 0;
StringBuilder rangeBuf = new StringBuilder();
rangeBuf.append(worksheetName);
rangeBuf.append(specifyColRow ? '!' : "");
rangeBuf.append((startColNum > 0) ? bijectiveBase26(startColNum) : "");
rangeBuf.append((startRowNum > 0) ? startRowNum : "");
rangeBuf.append(specifyColRow ? ':' : "");
rangeBuf.append((endColNum > 0) ? bijectiveBase26(endColNum) : "");
rangeBuf.append((endRowNum > 0) ? endRowNum : "");
return RequestUtil.executeWithRetry(sheetsService.spreadsheets().values().get(spreadsheet.getSpreadsheetId(), rangeBuf.toString())).getValues();
}
// 指定列で値の入っている最後の行の行番号を取得
// (ワークシート名のみ指定すれば「全列で」となるがメモリ節約のため列指定をできるようにしておく)
public int getLastRowNumberWithValue(String worksheetName, int colNum) throws IOException {
List<List<Object>> values = getValues(worksheetName, colNum, 0, colNum, 0);
if (values != null) {
return values.size();
}
return 0;
}
// 値書込
// 指定列・行が現在のワークシートの大きさを超える場合にはワークシートを必要なだけ拡張する
// 指定位置に既存の値があれば上書きする
public void setValues(String worksheetName, int startColNum, int startRowNum, Object[][] values) throws IOException {
List<List<Object>> valueList = new ArrayList<>();
for (Object[] row : values) {
valueList.add(Arrays.asList(row));
}
setValues(worksheetName, startColNum, startRowNum, valueList);
}
public void setValues(String worksheetName, int startColNum, int startRowNum, Object[] values) throws IOException {
setValues(worksheetName, startColNum, startRowNum, new Object[][] { values });
}
public void setValue(String worksheetName, int colNum, int rowNum, Object value) throws IOException {
setValues(worksheetName, colNum, rowNum, new Object[][] { { value } });
}
public void setValues(String worksheetName, int startColNum, int startRowNum, List<List<Object>> valueList) throws IOException {
ValueRange valueRange = new ValueRange().setValues(valueList).setMajorDimension("ROWS");
String range = worksheetName + "!" + bijectiveBase26(startColNum) + startRowNum;
// 開始位置が範囲内ならupdate、範囲外ならappend
// (updateは開始位置が範囲外だとエラー、開始位置さえ範囲内ならその後は必要に応じて列・行を追加してくれる)
// (appendは開始位置が範囲外でも必要に応じて列・行を追加してくれるが、開始位置が空がない場合に上書きしてくれない)
GridProperties gridProperties = getWorksheetProperties(worksheetName).getGridProperties();
if (startColNum <= gridProperties.getColumnCount() && startRowNum <= gridProperties.getRowCount()) {
RequestUtil.executeWithRetry(sheetsService.spreadsheets().values().update(spreadsheet.getSpreadsheetId(), range, valueRange).setValueInputOption("USER_ENTERED"));
} else {
RequestUtil.executeWithRetry(sheetsService.spreadsheets().values().append(spreadsheet.getSpreadsheetId(), range, valueRange).setValueInputOption("USER_ENTERED"));
}
}
// 列番号を列番号文字列に変換する
private String bijectiveBase26(int n) {
// https://gist.github.com/theazureshadow/4a5a032944f1c9bc0f4a より
StringBuilder buf = new StringBuilder();
while (n != 0) {
buf.append((char) ((n - 1) % 26 + 'A'));
n = (n - 1) / 26;
}
return buf.reverse().toString();
}
}
package mytestapp1;
import java.io.IOException;
import java.security.GeneralSecurityException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.slf4j.bridge.SLF4JBridgeHandler;
/**
* Google認証を行い、Credentialファイルを作成する。
*/
public class CreateCredentialFile {
private static final Logger LOGGER = LoggerFactory.getLogger(CreateCredentialFile.class);
/**
* Google認証を行い、Credentialファイルを作成する。<br>
* ブラウザが起動し、Googleの許可リクエスト画面が表示される。
*
* @param args
* @throws IOException
* @throws GeneralSecurityException
*/
public static void main(String[] args) throws IOException, GeneralSecurityException {
SLF4JBridgeHandler.removeHandlersForRootLogger();
SLF4JBridgeHandler.install();
MyTestApp1.getGoogleService().authorize();
LOGGER.info("Credentialファイルを保存しました。");
}
}
package mytestapp1;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.Reader;
import java.security.GeneralSecurityException;
import java.util.Arrays;
import java.util.List;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.slf4j.bridge.SLF4JBridgeHandler;
import com.google.api.services.drive.DriveScopes;
import com.google.api.services.drive.model.File;
import com.google.api.services.sheets.v4.Sheets;
import com.google.api.services.sheets.v4.SheetsScopes;
/**
* Googleスプレッドシートへの書き込みを行う。
*/
public class MyTestApp1 {
private static final Logger LOGGER = LoggerFactory.getLogger(MyTestApp1.class);
// Application name.
private static final String APPLICATION_NAME = "MyTestApp1";
// Return reader for Google client secret.
private static Reader getClientSecretReader() {
return new InputStreamReader(MyTestApp1.class.getResourceAsStream("/google_client_secret.json"));
}
// Google authorization scopes required by this application.
// If modifying these scopes, delete your previously saved credentials.
private static final List<String> AUTHORIZATION_SCOPE_LIST = Arrays.asList(DriveScopes.DRIVE, SheetsScopes.SPREADSHEETS);
// Directory to store Google user credentials for this application.
private static final String CREDENTIAL_STORE_DIRECTORY = System.getProperty("user.home") + "/.google_credentials/" + APPLICATION_NAME;
public static void main(String[] args) throws IOException, GeneralSecurityException {
SLF4JBridgeHandler.removeHandlersForRootLogger();
SLF4JBridgeHandler.install();
final String folderName = "フォルダ1";
final String spreadsheetName = "スプレッドシート1";
final String worksheetName = "ワークシート1";
// Build a new authorized API client service.
GoogleService googleService = getGoogleService();
SheetsWrapper sheetsWrapper = googleService.getSheetsWrapperWithWorksheet(folderName, spreadsheetName, worksheetName);
int lastRowNumberWithValue = sheetsWrapper.getLastRowNumberWithValue(worksheetName, 1);
// 値の入っている最後の行の次の行から書き込み
Object[][] values = { { 1, "A" }, { 2.1D, "B" }, { 2.50E-3, "C" } };
sheetsWrapper.setValues(worksheetName, 1, lastRowNumWithValue + 1, values);
LOGGER.info("書き込みました。");
}
static GoogleService getGoogleService() {
return new GoogleService(getClientSecretReader(), AUTHORIZATION_SCOPE_LIST, CREDENTIAL_STORE_DIRECTORY, APPLICATION_NAME);
}
private static Reader getClientSecretReader() {
return new InputStreamReader(MyTestApp1.class.getResourceAsStream("/google_client_secret.json"));
}
}
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE logback>
<configuration>
<contextListener class="ch.qos.logback.classic.jul.LevelChangePropagator">
<resetJUL>true</resetJUL>
</contextListener>
<appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
<encoder>
<pattern>%d{yyyy-MM-dd HH:mm:ss} [%thread] %-5level %logger{36} - %msg%n</pattern>
</encoder>
</appender>
<root level="INFO">
<appender-ref ref="STDOUT" />
</root>
</configuration>
3. 执行
当执行CreateCredentialFile类时，会启动浏览器并显示以下界面
（最好准备好已登录Google的浏览器）
按下「允许」后，将显示以下屏幕。
当执行MyTestApp1类时，会进行写入操作。
按下「允许」后，将显示以下屏幕。
当执行MyTestApp1类时，会进行写入操作。
