---
title: 'Google Sheet Api (1) quick-start'
disqus: hackmd
---
###### tags: `Sheet API` `Google Sheets`
Google Sheet Api quick-start(1)
===

[TOC]

## 筆記目的

完成串接Google Sheet APi 的串接部分，具體來說可以執行官網上的
**SheetsQuickstart.java**[範例](https://developers.google.com/sheets/api/quickstart/java#step_2_set_up_the_sample)

本筆記參考自:

* [Google Sheet Api java設定筆記](http://pclevin.blogspot.com/2018/01/google-api-google-sheets-google-java.html)

## 思考方向

說穿了，目的只有一個，就是透過程式去拿到google sheet的欄位值，
為此有兩種拿到的方式，

1. 透過 client library
2. 透過 http request 拿到你想要的值(可以透過postman)

[官網](https://developers.google.com/sheets/api/guides/authorizing#APIKey)上也是有提到這兩種方法分別有對應的**credentials**，分別是

1. OAuth 2.0(需要下載json檔案裏面有client ID and client secret)
2. API keys

## 透過 http request 拿到google sheet的資料

### 1. 透過 GCP 拿到 API keys
我先介紹第二種方法，因為比較簡單~

打開GCP平台的**Credentials page**，進去GCP後這樣點


到達憑證後



圖片其實已經提示你該怎麼去傳送params了

### 2. googlesheet

使用和GCP同一個google帳號去新增一個googlesheet在你的雲端空間中，觀察你生成的googlesheet的url，你會發現

https://docs.google.com/spreadsheets/d/XXXXXXXXXXXXXXXXXXX/edit#gid=0


中間那一大串XXX就是那張sheet的ID，請你把它複製下來
並且記住那張工作表的表名，最好把它改成英文的比較適合

### 3. postman

接下來參考[官網](https://developers.google.com/sheets/api/samples/reading#read_a_single_range)的作法，使用get request搭配Api key來獲取資源

https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId/values/Sheet1!A1:D5

> 請把spreadsheetId替換成你複製的url片段
> 請把Sheet1替換成你的表名
> 請把A1:D替換成你的範圍

然後打開postman，將網址直接貼到postman中，記得還要把**API keys**放入value裡面，前面的key放入key


## 透過 client library 拿到資料

### 專案架構


### 程式碼部分

pom.xml

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

完整pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.5.3</version>
		<relativePath /> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.example</groupId>
	<artifactId>googleSheetApi</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>googleSheetApi</name>
	<description>Demo project for Spring Boot</description>
	<properties>
		<java.version>11</java.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
		<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>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

</project>

SheetQuickstart

package com.example.demo;


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.javanet.NetHttpTransport;
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.sheets.v4.Sheets;
import com.google.api.services.sheets.v4.SheetsScopes;
import com.google.api.services.sheets.v4.model.ValueRange;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.security.GeneralSecurityException;
import java.util.Collections;
import java.util.List;

public class SheetsQuickstart {
    private static final String APPLICATION_NAME = "Google Sheets API Java Quickstart";
    private static final JsonFactory JSON_FACTORY = JacksonFactory.getDefaultInstance();
    private static final String TOKENS_DIRECTORY_PATH = "tokens";

    /**
     * Global instance of the scopes required by this quickstart.
     * If modifying these scopes, delete your previously saved tokens/ folder.
     */
    private static final List<String> SCOPES = Collections.singletonList(SheetsScopes.SPREADSHEETS_READONLY);
    private static final String CREDENTIALS_FILE_PATH = "/credentials.json";

    /**
     * Creates an authorized Credential object.
     * @param HTTP_TRANSPORT The network HTTP Transport.
     * @return An authorized Credential object.
     * @throws IOException If the credentials.json file cannot be found.
     */
    private static Credential getCredentials(final NetHttpTransport HTTP_TRANSPORT) throws IOException {
        // Load client secrets.
        InputStream in = SheetsQuickstart.class.getResourceAsStream(CREDENTIALS_FILE_PATH);
        if (in == null) {
            throw new FileNotFoundException("Resource not found: " + CREDENTIALS_FILE_PATH);
        }
        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(new FileDataStoreFactory(new java.io.File(TOKENS_DIRECTORY_PATH)))
                .setAccessType("offline")
                .build();
        LocalServerReceiver receiver = new LocalServerReceiver.Builder().setPort(8888).build();
        return new AuthorizationCodeInstalledApp(flow, receiver).authorize("user");
    }

    /**
     * Prints the names and majors of students in a sample spreadsheet:
     * https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
     */
    public static void main(String args[]) throws IOException, GeneralSecurityException {
        // Build a new authorized API client service.
        final NetHttpTransport HTTP_TRANSPORT = GoogleNetHttpTransport.newTrustedTransport();
        final String spreadsheetId = "XXXXXXXXXXXXXXXXXXXXXXXXX";
        final String range = "Class Data!A2:B";
        Sheets service = new Sheets.Builder(HTTP_TRANSPORT, JSON_FACTORY, getCredentials(HTTP_TRANSPORT))
                .setApplicationName(APPLICATION_NAME)
                .build();
        ValueRange response = service.spreadsheets().values()
                .get(spreadsheetId, range)
                .execute();
        List<List<Object>> values = response.getValues();
        if (values == null || values.isEmpty()) {
            System.out.println("No data found.");
        } else {
            System.out.println("Name, score");
            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(1));
            }
        }
    }
}

重點其實是要把下載下來的json檔案改名叫作credentials.json，然後放入src/main/resources裡面

 private static final String CREDENTIALS_FILE_PATH = "/credentials.json";

這一行要改成你自己的sheetID

final String spreadsheetId = "yoursheetid"; //mike example

放入自己的表名還有欄位名稱

final String range = "Class Data!A2:B";

非常重要的，請不要挑選出錯誤的行數，不然會報錯

         // Print columns A and B, which correspond to indices 0 and 1.
                System.out.printf("%s, %s\n", row.get(0), row.get(1));

## bug 解法

Could not add the data with error: com.google.api.client.googleapis.json.GoogleJsonResponseException: 403 Forbidden
{
  "code" : 403,
  "errors" : [ {
    "domain" : "global",
    "message" : "Insufficient Permission",
    "reason" : "insufficientPermissions"
  } ],
  "message" : "Request had insufficient authentication scopes.",
  "status" : "PERMISSION_DENIED"
}

移除

[stackoverflow](https://stackoverflow.com/questions/32476746/com-google-api-client-googleapis-json-googlejsonresponseexception-403-forbidden)