Day35 Golang - Google Sheet 试算表应用 (Spreadsheet)

Go Quickstart 官方文件

先照着 官方文件 的入门步骤做
以下快速带过这文件内总共做了哪些事情：

1. Enable the Google Sheets API

输入专案的名称，最後会得到 credientials.json 的档案，里面存放
client_id、project_id、client_secret等等，

这些变数都是为了做 开放授权(OAuth) 这事，详细可以看此篇介绍

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权限)去开 这一份表单，读取范围并且把其中两个栏位的值印出来。

到这边为止，是 官方文件 Go Quickstart 所做的事。

程序码的部分 (对Value进行操作)

往下看之前，先大略看过 Quickstart 程序码的流程，
前几个 func 都是做与 Config, Token 相关的事情，
只有在 main 後半部，将试算表内的数值读出时 才使用到 sheets API 的功能。

sheets.New(client)

        srv, err := sheets.New(client)

文件范例程序码的 sheets.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後

表单能只读写单一个栏位吗

可以透过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 颜色

更改背景颜色

	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