英文:
Google Sheets API: golang BatchUpdateValuesRequest
问题
我正在尝试按照Google Sheets API快速入门指南进行操作:
https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/batchUpdate
(向下滚动到“示例”,然后点击“GO”)
这是我尝试更新电子表格的方式:
package main
// 在运行之前:
// ---------------
// 1. 如果尚未完成,请启用Google Sheets API
// 并在以下网址检查项目的配额
// https://console.developers.google.com/apis/api/sheets
// 2. 通过在项目目录中运行`go get -u`来安装和更新Go依赖项。
import (
"errors"
"fmt"
"log"
"net/http"
"golang.org/x/net/context"
"google.golang.org/api/sheets/v4"
)
func main() {
ctx := context.Background()
c, err := getClient(ctx)
if err != nil {
log.Fatal(err)
}
sheetsService, err := sheets.New(c)
if err != nil {
log.Fatal(err)
}
// 要更新的电子表格的ID。
spreadsheetId := "1diQ943LGMDNkbCRGG4VqgKZdzyanCtT--V8o7r6kCR0"
var jsonPayloadVar []string
monthVar := "Apr"
thisCellVar := "A26"
thisLinkVar := "http://test.url"
jsonRackNumberVar := "\"RACKNUM01\""
jsonPayloadVar = append(jsonPayloadVar, fmt.Sprintf("(\"range\": \"%v!%v\", \"values\": [[\"%v,%v)\"])", monthVar, thisCellVar, thisLinkVar, jsonRackNumberVar))
rb := &sheets.BatchUpdateValuesRequest{"ValueInputOption": "USER_ENTERED", "data": jsonPayloadVar}
resp, err := sheetsService.Spreadsheets.Values.BatchUpdate(spreadsheetId, rb).Context(ctx).Do()
if err != nil {
log.Fatal(err)
}
fmt.Printf("%#v\n", resp)
}
func getClient(ctx context.Context) (*http.Client, error) {
// https://developers.google.com/sheets/quickstart/go#step_3_set_up_the_sample
//
// 使用以下范围进行授权:
// sheets.DriveScope
// sheets.DriveFileScope
sheets.SpreadsheetsScope
return nil, errors.New("not implemented")
}
输出:
hello.go:43: 结构初始化器中的字段名“ValueInputOption”无效
hello.go:43: 结构初始化器中的字段名“data”无效
hello.go:58: 评估了sheets.SpreadsheetsScope但未使用
有两个问题没有解决:
- 不清楚如何将字段输入到变量rb中
- 我需要使用sheets.SpreadsheetsScope
有人可以提供一个能够执行BatchUpdate的工作示例吗?
参考资料:
这篇文章展示了如何进行非BatchUpdate的更新:https://stackoverflow.com/questions/39691100/golang-google-sheets-api-v4-write-update-example
Google的API参考 - 请参阅从第1437行开始的ValueInputOption部分:https://github.com/google/google-api-go-client/blob/master/sheets/v4/sheets-gen.go
这篇文章展示了如何在Java中执行BatchUpdate:https://stackoverflow.com/questions/38107237/write-data-to-google-sheet-using-google-sheet-api-v4-java-sample-code
英文:
I'm trying to follow the Google Sheets API quickstart here:
https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/batchUpdate
(scroll down to "Examples" then click "GO")
This is how I tried to update a spreadsheet:
package main
// BEFORE RUNNING:
// ---------------
// 1. If not already done, enable the Google Sheets API
// and check the quota for your project at
// https://console.developers.google.com/apis/api/sheets
// 2. Install and update the Go dependencies by running `go get -u` in the
// project directory.
import (
"errors"
"fmt"
"log"
"net/http"
"golang.org/x/net/context"
"google.golang.org/api/sheets/v4"
)
func main() {
ctx := context.Background()
c, err := getClient(ctx)
if err != nil {
log.Fatal(err)
}
sheetsService, err := sheets.New(c)
if err != nil {
log.Fatal(err)
}
// The ID of the spreadsheet to update.
spreadsheetId := "1diQ943LGMDNkbCRGG4VqgKZdzyanCtT--V8o7r6kCR0"
var jsonPayloadVar []string
monthVar := "Apr"
thisCellVar := "A26"
thisLinkVar := "http://test.url"
jsonRackNumberVar := "\"RACKNUM01\""
jsonPayloadVar = append(jsonPayloadVar, fmt.Sprintf("(\"range\": \"%v!%v\", \"values\": [[\"%v,%v)\"]]),", monthVar, thisCellVar, thisLinkVar, jsonRackNumberVar))
rb := &sheets.BatchUpdateValuesRequest{"ValueInputOption": "USER_ENTERED", "data": jsonPayloadVar}
resp, err := sheetsService.Spreadsheets.Values.BatchUpdate(spreadsheetId, rb).Context(ctx).Do()
if err != nil {
log.Fatal(err)
}
fmt.Printf("%#v\n", resp)
}
func getClient(ctx context.Context) (*http.Client, error) {
// https://developers.google.com/sheets/quickstart/go#step_3_set_up_the_sample
//
// Authorize using the following scopes:
// sheets.DriveScope
// sheets.DriveFileScope
sheets.SpreadsheetsScope
return nil, errors.New("not implemented")
}
Output:
hello.go:43: invalid field name "ValueInputOption" in struct initializer<br>
hello.go:43: invalid field name "data" in struct initializer<br>
hello.go:58: sheets.SpreadsheetsScope evaluated but not used<br>
There are 2 things that aren't working:
- It's not obvious how to enter the fields into variable rb
- I need to use sheets.SpreadsheetsScope
Can anyone provide a working example that does a BatchUpdate?
References:
This article shows how to do an update that is not a BatchUpdate: https://stackoverflow.com/questions/39691100/golang-google-sheets-api-v4-write-update-example
Google's API reference - see the ValueInputOption section starting at line 1437: https://github.com/google/google-api-go-client/blob/master/sheets/v4/sheets-gen.go
This article shows how to do a BatchUpdate in Java: https://stackoverflow.com/questions/38107237/write-data-to-google-sheet-using-google-sheet-api-v4-java-sample-code
答案1
得分: 4
以下是翻译好的内容:
以下是一个简单的用于更新电子表格上的表格的示例脚本。如果您想进行各种更新,请修改它。有关 spreadsheets.values.batchUpdate 的参数详细信息,请参阅此处。
流程:
首先,为了使用您问题中的链接,请使用Go 快速入门。在我的示例脚本中,该脚本是使用快速入门创建的。
使用此示例脚本的流程如下:
- 对于Go 快速入门,请执行第 1 步和第 2 步。
- 请将
client_secret.json
放置在与我的示例脚本相同的目录中。 - 复制并粘贴我的示例脚本,并将其创建为新的脚本文件。
- 运行脚本。
- 当终端上显示
在浏览器中转到以下链接,然后输入授权代码:
时,请复制 URL 并粘贴到浏览器中。然后,请进行授权并获取代码。 - 将代码放入终端。
- 当显示
完成。
时,表示电子表格的更新已完成。
请求正文:
对于Spreadsheets.Values.BatchUpdate
,BatchUpdateValuesRequest
是其中一个参数。在这种情况下,您想要更新的范围、值等都包含在BatchUpdateValuesRequest
中。有关此BatchUpdateValuesRequest
的详细信息,请参阅godoc。在查看BatchUpdateValuesRequest
时,可以看到Data []*ValueRange
。在这里,请注意Data
是[]*ValueRange
。还可以在godoc中查看ValueRange
。您可以在ValueRange
中看到MajorDimension
、Range
和Values
。
当上述信息反映到脚本中时,脚本可以修改如下。
示例脚本:
package main
import (
"encoding/json"
"fmt"
"io/ioutil"
"log"
"net/http"
"os"
"golang.org/x/net/context"
"golang.org/x/oauth2"
"golang.org/x/oauth2/google"
"google.golang.org/api/sheets/v4"
)
// getClient 使用上下文和配置来检索令牌,然后生成客户端。它返回生成的客户端。
func getClient(ctx context.Context, config *oauth2.Config) *http.Client {
cacheFile := "./go-quickstart.json"
tok, err := tokenFromFile(cacheFile)
if err != nil {
tok = getTokenFromWeb(config)
saveToken(cacheFile, tok)
}
return config.Client(ctx, tok)
}
// getTokenFromWeb 使用配置请求令牌。它返回检索到的令牌。
func getTokenFromWeb(config *oauth2.Config) *oauth2.Token {
authURL := config.AuthCodeURL("state-token", oauth2.AccessTypeOffline)
fmt.Printf("在浏览器中转到以下链接,然后输入授权代码:\n%v\n", authURL)
var code string
if _, err := fmt.Scan(&code); err != nil {
log.Fatalf("无法读取授权代码 %v", err)
}
tok, err := config.Exchange(oauth2.NoContext, code)
if err != nil {
log.Fatalf("无法从网络检索令牌 %v", err)
}
return tok
}
// tokenFromFile 从给定的文件路径检索令牌。它返回检索到的令牌和遇到的任何读取错误。
func tokenFromFile(file string) (*oauth2.Token, error) {
f, err := os.Open(file)
if err != nil {
return nil, err
}
t := &oauth2.Token{}
err = json.NewDecoder(f).Decode(t)
defer f.Close()
return t, err
}
func saveToken(file string, token *oauth2.Token) {
fmt.Printf("将凭据文件保存到:%s\n", file)
f, err := os.Create(file)
if err != nil {
log.Fatalf("无法缓存 OAuth 令牌:%v", err)
}
defer f.Close()
json.NewEncoder(f).Encode(token)
}
type body struct {
Data struct {
Range string `json:"range"`
Values [][]string `json:"values"`
} `json:"data"`
ValueInputOption string `json:"valueInputOption"`
}
func main() {
ctx := context.Background()
b, err := ioutil.ReadFile("client_secret.json")
if err != nil {
log.Fatalf("无法读取客户端密钥文件:%v", err)
}
config, err := google.ConfigFromJSON(b, "https://www.googleapis.com/auth/spreadsheets")
if err != nil {
log.Fatalf("无法将客户端密钥文件解析为配置:%v", err)
}
client := getClient(ctx, config)
sheetsService, err := sheets.New(client)
if err != nil {
log.Fatalf("无法检索 Sheets 客户端:%v", err)
}
spreadsheetId := "### 电子表格 ID ###"
rangeData := "sheet1!A1:B3"
values := [][]interface{}{{"sample_A1", "sample_B1"}, {"sample_A2", "sample_B2"}, {"sample_A3", "sample_A3"}}
rb := &sheets.BatchUpdateValuesRequest{
ValueInputOption: "USER_ENTERED",
}
rb.Data = append(rb.Data, &sheets.ValueRange{
Range: rangeData,
Values: values,
})
_, err = sheetsService.Spreadsheets.Values.BatchUpdate(spreadsheetId, rb).Context(ctx).Do()
if err != nil {
log.Fatal(err)
}
fmt.Println("完成。")
}
结果:
参考资料:
spreadsheets.values.batchUpdate
的详细信息请参阅此处。- Go 快速入门的详细信息请参阅此处。
BatchUpdateValuesRequest
的详细信息请参阅此处。ValueRange
的详细信息请参阅此处。
如果我误解了您的问题,我很抱歉。
英文:
How about the following sample script? This is a simple sample script for updating sheet on Spreadsheet. So if you want to do various update, please modify it. The detail of parameters for spreadsheets.values.batchUpdate is here.
Flow :
At first, in ordet to use the link in your question, please use Go Quickstart. In my sample script, the script was made using the Quickstart.
The flow to use this sample script is as follows.
- For Go Quickstart, please do Step 1 and Step 2.
- Please put
client_secret.json
to the same directory with my sample script. - Copy and paste my sample script, and create it as new script file.
- Run the script.
- When
Go to the following link in your browser then type the authorization code:
is shown on your terminal, please copy the URL and paste to your browser. And then, please authorize and get code. - Put the code to the terminal.
- When
Done.
is displayed, it means that the update of spreadsheet is done.
Request body :
For Spreadsheets.Values.BatchUpdate
, BatchUpdateValuesRequest
is required as one of parameters. In this case, the range, values and so on that you want to update are included in BatchUpdateValuesRequest
. The detail information of this BatchUpdateValuesRequest
can be seen at godoc. When it sees BatchUpdateValuesRequest
, Data []*ValueRange
can be seen. Here, please be carefull that Data
is []*ValueRange
. Also ValueRange
can be seen at godoc. You can see MajorDimension
, Range
and Values
in ValueRange
.
When above infomation is reflected to the script, the script can be modified as follows.
Sample script :
package main
import (
"encoding/json"
"fmt"
"io/ioutil"
"log"
"net/http"
"os"
"golang.org/x/net/context"
"golang.org/x/oauth2"
"golang.org/x/oauth2/google"
"google.golang.org/api/sheets/v4"
)
// getClient uses a Context and Config to retrieve a Token
// then generate a Client. It returns the generated Client.
func getClient(ctx context.Context, config *oauth2.Config) *http.Client {
cacheFile := "./go-quickstart.json"
tok, err := tokenFromFile(cacheFile)
if err != nil {
tok = getTokenFromWeb(config)
saveToken(cacheFile, tok)
}
return config.Client(ctx, tok)
}
// getTokenFromWeb uses Config to request a Token.
// It returns the retrieved Token.
func getTokenFromWeb(config *oauth2.Config) *oauth2.Token {
authURL := config.AuthCodeURL("state-token", oauth2.AccessTypeOffline)
fmt.Printf("Go to the following link in your browser then type the "+
"authorization code: \n%v\n", authURL)
var code string
if _, err := fmt.Scan(&code); err != nil {
log.Fatalf("Unable to read authorization code %v", err)
}
tok, err := config.Exchange(oauth2.NoContext, code)
if err != nil {
log.Fatalf("Unable to retrieve token from web %v", err)
}
return tok
}
// tokenFromFile retrieves a Token from a given file path.
// It returns the retrieved Token and any read error encountered.
func tokenFromFile(file string) (*oauth2.Token, error) {
f, err := os.Open(file)
if err != nil {
return nil, err
}
t := &oauth2.Token{}
err = json.NewDecoder(f).Decode(t)
defer f.Close()
return t, err
}
func saveToken(file string, token *oauth2.Token) {
fmt.Printf("Saving credential file to: %s\n", file)
f, err := os.Create(file)
if err != nil {
log.Fatalf("Unable to cache oauth token: %v", err)
}
defer f.Close()
json.NewEncoder(f).Encode(token)
}
type body struct {
Data struct {
Range string `json:"range"`
Values [][]string `json:"values"`
} `json:"data"`
ValueInputOption string `json:"valueInputOption"`
}
func main() {
ctx := context.Background()
b, err := ioutil.ReadFile("client_secret.json")
if err != nil {
log.Fatalf("Unable to read client secret file: %v", err)
}
config, err := google.ConfigFromJSON(b, "https://www.googleapis.com/auth/spreadsheets")
if err != nil {
log.Fatalf("Unable to parse client secret file to config: %v", err)
}
client := getClient(ctx, config)
sheetsService, err := sheets.New(client)
if err != nil {
log.Fatalf("Unable to retrieve Sheets Client %v", err)
}
spreadsheetId := "### spreadsheet ID ###"
rangeData := "sheet1!A1:B3"
values := [][]interface{}{{"sample_A1", "sample_B1"}, {"sample_A2", "sample_B2"}, {"sample_A3", "sample_A3"}}
rb := &sheets.BatchUpdateValuesRequest{
ValueInputOption: "USER_ENTERED",
}
rb.Data = append(rb.Data, &sheets.ValueRange{
Range: rangeData,
Values: values,
})
_, err = sheetsService.Spreadsheets.Values.BatchUpdate(spreadsheetId, rb).Context(ctx).Do()
if err != nil {
log.Fatal(err)
}
fmt.Println("Done.")
}
Result :
References :
- The detail infomation of
spreadsheets.values.batchUpdate
is here. - The detail infomation of Go Quickstart is here.
- The detail infomation of
BatchUpdateValuesRequest
is here. - The detail infomation of
ValueRange
is here.
If I misunderstand your question, I'm sorry.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论