# Day35 Golang - Google Sheet 試算表應用 (Spreadsheet)
## Go Quickstart 官方文件
先照著 [**官方文件**](https://developers.google.com/sheets/api/quickstart/go) 的入門步驟做
以下快速帶過這文件內總共做了哪些事情：
**1. Enable the Google Sheets API**
輸入專案的名稱，最後會得到 `credientials.json` 的檔案，裡面存放
`client_id`、`project_id`、`client_secret`等等，
這些變數都是為了做 **開放授權(OAuth)** 這事，詳細可以看[**此篇介紹**](https://www.ruanyifeng.com/blog/2019/04/oauth-grant-types.html)
**2. 下載套件**
不曉得為何我載的時候花了很久時間
$ go get -u google.golang.org/api/sheets/v4
$ go get -u golang.org/x/oauth2/google
**3. 執行quickstart範例程式**
https://github.com/googleworkspace/go-samples/blob/master/sheets/quickstart/quickstart.go
執行完畢會出現如下的網址
點擊進去，點選進階、進入、授權
得到這一組授權碼
最後將授權碼貼回，讓程式產生token
專案底下自動產出的 `token` ，裡面有 `access_token`
再執行一次程式，沒意外的話 會印出如下的結果
這等於是用你同意的授權(**Read權限**)去開 [**這一份表單**](https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit)，讀取範圍並且把其中兩個欄位的值印出來。
到這邊為止，是 **官方文件 Go Quickstart** 所做的事。
---
## 程式碼的部分 (對Value進行操作)
往下看之前，先大略看過 Quickstart 程式碼的流程，
前幾個 `func` 都是做與 `Config`, `Token` 相關的事情，
只有在 `main` 後半部，將試算表內的數值讀出時 才使用到 `sheets API` 的功能。
### sheets.New(client)
```go=
srv, err := sheets.New(client)
```
**文件範例程式碼的 sheets.New() 方法已經被棄用了**
New方法未來可能會不支援
可以改成以下的方法，透過`option.WithHTTPClient` 以及 `sheets.NewService`來達成。
```go=
client := getClient(config)
clientOpts := option.WithHTTPClient(client)
srv, err := sheets.NewService(context.Background(), clientOpts)
if err != nil {
log.Fatalf("Unable to retrieve Sheets client: %v", err)
}
```
### 變數名稱
* spreadsheetId: 用來指定哪一份表單的ID。是google sheet網址URI中的一部分
* readRange: 欲讀取哪個表的欄位範圍(Range)。`Class Data!A2:E` 驚嘆號`!`前是**表(Table)的名稱**
### 讀寫權限
在`func ConfigFromJSON`中的`https://www.googleapis.com/auth/spreadsheets.readonly`，
把readonly拿掉就能有讀/寫權限，只不過需要刪除Token再產生一次。詳細值參照文件 https://developers.google.com/sheets/api/guides/authorizing#OAuth2Authorizing
## 讀取表單欄位(Get)
取得一個範圍內的值 **Get**
```go=
resp, err := srv.Spreadsheets.Values.Get(SpreadsheetId, readRange).Do()
```
取得多個範圍的值 **BatchGet**
```go=
resp, err := srv.Spreadsheets.Values.BatchGet(SpreadsheetId).Ranges(readRange1, readRange2).Do()
```
## 更新表單欄位(Update)
把剛剛讀取到的欄位數值套用寫回
res, err := srv.Spreadsheets.Values.Update(SpreadsheetId, readRange, resp).ValueInputOption("RAW").Do()
其中的 `RAW` 可以換成 `USER_ENTERED` [**詳見文檔**](https://developers.google.com/sheets/api/reference/rest/v4/ValueInputOption)
#### 物件 sheets.ValueRange
```go=
vr := sheets.ValueRange{
MajorDimension: "",
Range: "",
Values: nil,
ServerResponse: googleapi.ServerResponse{},
ForceSendFields: nil,
NullFields: nil,
}
```
* MajorDimension: （解析方式的）主要維度
* 填入 `ROWS` 表示以 **列** 方式讀取(先由左至右 再上到下)
以圖為例值為`[[1,2],[3,4]]`
* 填入 `COLUMNS` 表示以 **欄** 方式讀取(先由上到下 再左至右)
以圖為例值為`[[1,3],[2,4]]`
* Range: 範圍，例如 `A2:E5`
* Values: 值。 例如 `[[test1 testtest 12] [test2 testtest 4]]`，型別為`[][]interface{}`
* ServerResponse: 伺服器回傳的東西，如 `HTTPStatusCode:200`、`Header`、`Cache-Control`等等
* ForceSendFields:
* NullFields: Null
為了將值代入**Values**中，
需要讓 2D slice string 轉成 2D slice interface，
這邊提供兩個不一樣的方法：
1.
```go=
data1D := []string{"1111", "2222"}
s1D := make([]interface{}, len(data1D))
for i, v := range data1D {
s1D[i] = v
}
s2D := [][]interface{}{}
s2D = append(s2D, s1D)
```
2.
```go=
data2D := [][]string{{"AAAA", "1222"}, {"CCCC", "9999,999,1234"}}
s2D := make([][]interface{}, len(data2D))
for i, v := range data2D {
for j, x := range v {
if j == 0 {
s2D[i] = make([]interface{}, len(data2D[0]))
}
s2D[i][j] = x
}
}
```
創一個 **Test資料表** 來試試看：
**Update前**
再來將 `data1D` 用Update更新回去
```go=
vr := sheets.ValueRange{
MajorDimension: "ROWS",
Values: s2D,
}
res, err := Srv.Spreadsheets.Values.Update(SpreadsheetId, "Test!A2:E5", &vr).ValueInputOption("USER_ENTERED").Do()
```
**Update後**
一次修改多個範圍 **BatchUpdate**
```go=
var valueRanges = []*sheets.ValueRange{}
for _, ... := range ... {
...
...
valueRanges = append(valueRanges, &valueRange)
}
var batchUpdateRequest = sheets.BatchUpdateValuesRequest{
Data: valueRanges,
ValueInputOption: "USER_ENTERED",
}
_, err := Srv.Spreadsheets.Values.BatchUpdate(spreadsheetId, &batchUpdateRequest).Do()
if err != nil {
log.Println(err)
}
```
## 附加表單欄位(Append)
代入的欄位、用法 與**Update**大同小異，但 **Append** 不會覆寫欄位、而是附加欄位上去
**如果帶入的Range範圍內已有值，則會append到最後一行(Row)。否則會填入Range中。**
**Append前**
```go=
vr := sheets.ValueRange{
MajorDimension: "ROWS",
Values: s2D,
}
res, err := Srv.Spreadsheets.Values.Append(SpreadsheetId, "Test!A2:B2", &vr).ValueInputOption("USER_ENTERED").Do()
// Range也可以只帶 "Test!A2"
```
**Append後**
## 刪除表單欄位(Clear)
```go=
cr := sheets.ClearValuesRequest{}
res, err := Srv.Spreadsheets.Values.Clear(SpreadsheetId, "Test!A2:B2", &cr).Do()
```
**Clear後**
---
### 表單能只讀寫單一個欄位嗎
可以透過Range限制範圍在單一個欄位內，來達成這件事情
### 能做到SQL那樣修改特定欄位嗎
目前不行，
只能透過操作變數來修改指定Range範圍。
https://stackoverflow.com/questions/57735434/how-can-i-use-the-google-sheets-v4-api-to-modify-a-specific-row-of-data-depend
但查詢功能可以透過表單內建函式 **Google Sheet Query** 達成SQL語法的**查詢操作**，只不過沒辦法像SQL那麼便利。
`=QUERY(C2:C5, "select *")`
`=QUERY(C2:C, "select avg(C), max(C)")`
### 啊，操作太頻繁了
> googleapi: Error 429: Quota exceeded for quota metric 'Write requests' and limit 'Write requests per minute per user' of service 'sheets.googleapis.com' for consumer 'project_number:123456789'., rateLimitExceeded
還有限制哩，不能太常對試算表進行操作，
大約限制每分鐘每位使用者只能對試算表進行修改60次，
讀取也有所限制。
這時 BatchGet、BatchUpdate 就可以起到作用拉，可以在程式內用計數器，數量到達一定程度時再傳給試算表的API，讓他一次更新。
## 變更格式 Style 顏色
更改背景顏色
```go=
color := sheets.Color{
Alpha: 0,
Blue: 1,
Green: 1,
Red: 0.5,
// 數值範圍在0~1之間
}
req := sheets.Request{RepeatCell: &sheets.RepeatCellRequest{
Cell: &sheets.CellData{
UserEnteredFormat: &sheets.CellFormat{
BackgroundColor: &color,
},
},
Range: &sheets.GridRange{
// 若指定範圍是 `A1:B2` 則為 (0,0) -> (2,2)
StartColumnIndex: 0,
StartRowIndex: 0,
EndColumnIndex: 2,
EndRowIndex: 2,
SheetId: 2054447095,
// SheetId 由 網址的gid而來 : https://docs.google.com/spreadsheets/d/.../edit#gid=2054447095
},
Fields: "UserEnteredFormat(BackgroundColor)", // 只改背景顏色，其他設定不動
}}
reqs := sheets.BatchUpdateSpreadsheetRequest{
Requests: []*sheets.Request{&req},
}
res, err := Srv.Spreadsheets.BatchUpdate(SpreadsheetId, &reqs).Do()
```
**更改背景顏色後**
花了好久時間才達成這一步，
表單有太多太多物件、設定跟參數了，
更新、排序、尋找、移動、亂數、字型、對齊、顏色
媽呀，族繁不及備載...
https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request
Sign in with Wallet
Connect another wallet