第 12 屆 iThome 鐵人賽

Software Development

Go繁不及備載系列第 35 篇

# Day35 Golang - Google Sheet 試算表應用 (Spreadsheet)

12th鐵人賽

gjlmotea

團隊You Reverse 你逆

2021-01-19 21:24:02

8540 瀏覽

分享至

Day35 Golang - Google Sheet 試算表應用 (Spreadsheet)

Go Quickstart 官方文件

先照著 官方文件 的入門步驟做
以下快速帶過這文件內總共做了哪些事情：

1. Enable the Google Sheets API

輸入專案的名稱，最後會得到 credientials.json 的檔案，裡面存放
client_id、project_id、client_secret等等，

這些變數都是為了做 開放授權(OAuth) 這事，詳細可以看此篇介紹

Step1

2. 下載套件

不曉得為何我載的時候花了很久時間

$ go get -u google.golang.org/api/sheets/v4
$ go get -u golang.org/x/oauth2/google

Step2

3. 執行quickstart範例程式

https://github.com/googleworkspace/go-samples/blob/master/sheets/quickstart/quickstart.go

執行完畢會出現如下的網址
Step5

點擊進去，點選進階、進入、授權
Step3

得到這一組授權碼
Step4

最後將授權碼貼回，讓程式產生token
Step5

專案底下自動產出的 token ，裡面有 access_token
Token

再執行一次程式，沒意外的話會印出如下的結果

Read

這等於是用你同意的授權(Read權限)去開 這一份表單，讀取範圍並且把其中兩個欄位的值印出來。

到這邊為止，是 官方文件 Go Quickstart 所做的事。

程式碼的部分 (對Value進行操作)

往下看之前，先大略看過 Quickstart 程式碼的流程，
前幾個 func 都是做與 Config, Token 相關的事情，
只有在 main 後半部，將試算表內的數值讀出時才使用到 sheets API 的功能。

sheets.New(client)

        srv, err := sheets.New(client)

文件範例程式碼的 sheets.New() 方法已經被棄用了

New棄用

New方法未來可能會不支援

可以改成以下的方法，透過option.WithHTTPClient 以及 sheets.NewService來達成。

	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

    resp, err := srv.Spreadsheets.Values.Get(SpreadsheetId, readRange).Do()

取得多個範圍的值 BatchGet

    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 詳見文檔

物件 sheets.ValueRange

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

為了將值代入Values中，
需要讓 2D slice string 轉成 2D slice interface，
這邊提供兩個不一樣的方法：

	data1D := []string{"1111", "2222"}
	s1D := make([]interface{}, len(data1D))
	for i, v := range data1D {
		s1D[i] = v
	}

	s2D := [][]interface{}{}
	s2D = append(s2D, s1D)

	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更新回去

	vr := sheets.ValueRange{
		MajorDimension:  "ROWS",
		Values:          s2D,
	}
    
	res, err := Srv.Spreadsheets.Values.Update(SpreadsheetId, "Test!A2:E5", &vr).ValueInputOption("USER_ENTERED").Do()

Update後

一次修改多個範圍 BatchUpdate

	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前

	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)

	cr := sheets.ClearValuesRequest{}
	res, err := Srv.Spreadsheets.Values.Clear(SpreadsheetId, "Test!A2:B2", &cr).Do()

Clear後

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 *")

Google Sheet Query1

=QUERY(C2:C, "select avg(C), max(C)")

Google Sheet Query2

啊，操作太頻繁了

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

更改背景顏色

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

更改背景顏色後
變更顏色後