Google Sheets API: golang BatchUpdateValuesRequest 谷歌表格 API:golang 批量更新值请求

huangapple go评论83阅读模式
英文:

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但未使用

有两个问题没有解决:

  1. 不清楚如何将字段输入到变量rb中
  2. 我需要使用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:

  1. It's not obvious how to enter the fields into variable rb
  2. 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 快速入门。在我的示例脚本中,该脚本是使用快速入门创建的。

使用此示例脚本的流程如下:

  1. 对于Go 快速入门,请执行第 1 步和第 2 步。
  2. 请将client_secret.json放置在与我的示例脚本相同的目录中。
  3. 复制并粘贴我的示例脚本,并将其创建为新的脚本文件。
  4. 运行脚本。
  5. 当终端上显示在浏览器中转到以下链接,然后输入授权代码:时,请复制 URL 并粘贴到浏览器中。然后,请进行授权并获取代码。
  6. 将代码放入终端。
  7. 当显示完成。时,表示电子表格的更新已完成。

请求正文:

对于Spreadsheets.Values.BatchUpdateBatchUpdateValuesRequest是其中一个参数。在这种情况下,您想要更新的范围、值等都包含在BatchUpdateValuesRequest中。有关此BatchUpdateValuesRequest的详细信息,请参阅godoc。在查看BatchUpdateValuesRequest时,可以看到Data []*ValueRange。在这里,请注意Data[]*ValueRange。还可以在godoc中查看ValueRange。您可以在ValueRange中看到MajorDimensionRangeValues

当上述信息反映到脚本中时,脚本可以修改如下。

示例脚本:

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("完成。")
}

结果:

Google Sheets API: golang BatchUpdateValuesRequest
谷歌表格 API:golang 批量更新值请求

参考资料:

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

  1. For Go Quickstart, please do Step 1 and Step 2.
  2. Please put client_secret.json to the same directory with my sample script.
  3. Copy and paste my sample script, and create it as new script file.
  4. Run the script.
  5. 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.
  6. Put the code to the terminal.
  7. 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 (
&quot;encoding/json&quot;
&quot;fmt&quot;
&quot;io/ioutil&quot;
&quot;log&quot;
&quot;net/http&quot;
&quot;os&quot;
&quot;golang.org/x/net/context&quot;
&quot;golang.org/x/oauth2&quot;
&quot;golang.org/x/oauth2/google&quot;
&quot;google.golang.org/api/sheets/v4&quot;
)
// 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 := &quot;./go-quickstart.json&quot;
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(&quot;state-token&quot;, oauth2.AccessTypeOffline)
fmt.Printf(&quot;Go to the following link in your browser then type the &quot;+
&quot;authorization code: \n%v\n&quot;, authURL)
var code string
if _, err := fmt.Scan(&amp;code); err != nil {
log.Fatalf(&quot;Unable to read authorization code %v&quot;, err)
}
tok, err := config.Exchange(oauth2.NoContext, code)
if err != nil {
log.Fatalf(&quot;Unable to retrieve token from web %v&quot;, 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 := &amp;oauth2.Token{}
err = json.NewDecoder(f).Decode(t)
defer f.Close()
return t, err
}
func saveToken(file string, token *oauth2.Token) {
fmt.Printf(&quot;Saving credential file to: %s\n&quot;, file)
f, err := os.Create(file)
if err != nil {
log.Fatalf(&quot;Unable to cache oauth token: %v&quot;, err)
}
defer f.Close()
json.NewEncoder(f).Encode(token)
}
type body struct {
Data struct {
Range  string     `json:&quot;range&quot;`
Values [][]string `json:&quot;values&quot;`
} `json:&quot;data&quot;`
ValueInputOption string `json:&quot;valueInputOption&quot;`
}
func main() {
ctx := context.Background()
b, err := ioutil.ReadFile(&quot;client_secret.json&quot;)
if err != nil {
log.Fatalf(&quot;Unable to read client secret file: %v&quot;, err)
}
config, err := google.ConfigFromJSON(b, &quot;https://www.googleapis.com/auth/spreadsheets&quot;)
if err != nil {
log.Fatalf(&quot;Unable to parse client secret file to config: %v&quot;, err)
}
client := getClient(ctx, config)
sheetsService, err := sheets.New(client)
if err != nil {
log.Fatalf(&quot;Unable to retrieve Sheets Client %v&quot;, err)
}
spreadsheetId := &quot;### spreadsheet ID ###&quot;
rangeData := &quot;sheet1!A1:B3&quot;
values := [][]interface{}{{&quot;sample_A1&quot;, &quot;sample_B1&quot;}, {&quot;sample_A2&quot;, &quot;sample_B2&quot;}, {&quot;sample_A3&quot;, &quot;sample_A3&quot;}}
rb := &amp;sheets.BatchUpdateValuesRequest{
ValueInputOption: &quot;USER_ENTERED&quot;,
}
rb.Data = append(rb.Data, &amp;sheets.ValueRange{
Range:  rangeData,
Values: values,
})
_, err = sheetsService.Spreadsheets.Values.BatchUpdate(spreadsheetId, rb).Context(ctx).Do()
if err != nil {
log.Fatal(err)
}
fmt.Println(&quot;Done.&quot;)
}

Result :

Google Sheets API: golang BatchUpdateValuesRequest
谷歌表格 API:golang 批量更新值请求

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.

huangapple
  • 本文由 发表于 2017年9月15日 10:03:40
  • 转载请务必保留本文链接:https://go.coder-hub.com/46230624.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定