Java程式教學甘仔店: Google API 使用 Google Sheets 使用雲端Google試算表使用 JAVA實作

2018年1月11日星期四

Google API 使用 Google Sheets 使用雲端Google試算表使用 JAVA實作

本篇介紹使用Google API ，來讀取在Google雲端試算表。

使用Java來說明，本專案實作前你需要：
1、Java 1.7
2、Google 帳號
3、要有網路瀏覽器

一、註冊Google Sheets API 及申請 Google Developers Console 憑證

https://console.developers.google.com/flows/enableapi?apiid=sheets.googleapis.com

圖1 按下繼續

圖2 按下前往「憑證」

圖3 按下取消

圖4 點選憑證頁中的 OAuth同意畫面

圖5 輸入電子郵件地址及產品名稱 -> 儲存

圖6 前往「憑證」 -> 建立憑證

圖7 選擇 OAuth 用戶端ID

圖8 選擇其它 -> 輸入名稱 -> 建立

圖9 下載憑證 JSON檔

圖10 憑證JSON檔 client_secret.json

二、進入進入雲端硬碟，建立測試Google試算表資料

圖11 進入雲端硬碟 ->

圖12 新增資料夾 (googleapi) -> 新增Google試算表

圖13 進入Google試算表 -> 輸入名稱 (google test) -> Sheets名稱 (Class Data)

三、建立 Java 程式專案

圖14 建立 Java 程式專案

圖15 需要的Java jar檔 (使用 Maven )

 <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>

程式參考: Java Quickstart
https://developers.google.com/sheets/api/quickstart/java

  package com.googletest;

import com.google.api.client.auth.oauth2.Credential;
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.GoogleClientSecrets;
import com.google.api.client.googleapis.javanet.GoogleNetHttpTransport;
import com.google.api.client.http.HttpTransport;
import com.google.api.client.json.jackson2.JacksonFactory;
import com.google.api.client.json.JsonFactory;
import com.google.api.client.util.store.FileDataStoreFactory;
import com.google.api.services.sheets.v4.SheetsScopes;
import com.google.api.services.sheets.v4.model.*;
import com.google.api.services.sheets.v4.Sheets;

import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.Arrays;
import java.util.List;

/**
 * https://developers.google.com/sheets/api/quickstart/java
 * 1. 建立 Google API Console 及註冊  https://console.developers.google.com/flows/enableapi?apiid=sheets.googleapis.com
 * 2. 新增憑證
 *
 */
public class Quickstart {
 /** Application name. */
 private static final String APPLICATION_NAME = "googletapi";//

 /** Directory to store user credentials for this application. (暫存 保存的憑據) */
 private static final java.io.File DATA_STORE_DIR = new java.io.File(System.getProperty("user.home"), ".credentials/sheets.googleapis.com-java-quickstart");

 /** Global instance of the {@link FileDataStoreFactory}. */
 private static FileDataStoreFactory DATA_STORE_FACTORY;

 /** 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;

 /**
  * Global instance of the scopes required by this quickstart.
  *
  * If modifying these scopes, delete your previously saved credentials at
  * ~/.credentials/sheets.googleapis.com-java-quickstart
  */
 private static final List SCOPES = Arrays.asList(SheetsScopes.SPREADSHEETS_READONLY);

 static {
  try {
   HTTP_TRANSPORT = GoogleNetHttpTransport.newTrustedTransport();
   DATA_STORE_FACTORY = new FileDataStoreFactory(DATA_STORE_DIR);
  } catch (Throwable t) {
   t.printStackTrace();
   System.exit(1);
  }
 }

 /**
  * Creates an authorized Credential object.
  * 
  * @return an authorized Credential object.
  * @throws IOException
  */
 public static Credential authorize() throws IOException {
  // Load client secrets.
  InputStream in = Quickstart.class.getResourceAsStream("/client_secret.json");
  GoogleClientSecrets clientSecrets = GoogleClientSecrets.load(JSON_FACTORY, new InputStreamReader(in));

  // Build flow and trigger user authorization request.
  GoogleAuthorizationCodeFlow flow = new GoogleAuthorizationCodeFlow.Builder(HTTP_TRANSPORT, JSON_FACTORY, clientSecrets, SCOPES).setDataStoreFactory(DATA_STORE_FACTORY).setAccessType("offline").build();
  Credential credential = new AuthorizationCodeInstalledApp(flow, new LocalServerReceiver()).authorize("user");
  System.out.println("Credentials saved to " + DATA_STORE_DIR.getAbsolutePath());
  return credential;
 }

 /**
  * Build and return an authorized Sheets API client service.
  * 
  * @return an authorized Sheets API client service
  * @throws IOException
  */
 public static Sheets getSheetsService() throws IOException {
  Credential credential = authorize();
  return new Sheets.Builder(HTTP_TRANSPORT, JSON_FACTORY, credential).setApplicationName(APPLICATION_NAME).build();
 }

 public static void main(String[] args) throws IOException {
  // Build a new authorized API client service.
  Sheets service = getSheetsService();

  //https://docs.google.com/spreadsheets/d/1WjVH1cVE1niaxSZ43i4rOwyp1EG4XbTQk4FXGsgT0yo/edit#gid=0
  String spreadsheetId = "1WjVH1cVE1niaxSZ43i4rOwyp1EG4XbTQk4FXGsgT0yo";
  String range = "Class Data!A2:E";
  ValueRange response = service.spreadsheets().values().get(spreadsheetId, range).execute();
  List> values = response.getValues();
  if (values == null || values.size() == 0) {
   System.out.println("No data found.");
  } else {
   System.out.println("Name, Major");
   for (List row : values) {
    // Print columns A and E, which correspond to indices 0 and 4.
    System.out.printf("%s, %s\n", row.get(0), row.get(4));
   }
  }
 }

}

修改的地方有：

圖16 修改憑證JSON檔的檔名