dongxi5505 2017-09-15 02:03
浏览 87

Google表格API:golang BatchUpdateValuesRequest

I'm trying to follow the Google Sheets API quickstart here:

(scroll down to "Examples" then click "GO")

This is how I tried to update a spreadsheet:

package main

// ---------------
// 1. If not already done, enable the Google Sheets API
//    and check the quota for your project at
// 2. Install and update the Go dependencies by running `go get -u` in     the
//    project directory.

import (


func main() {
        ctx := context.Background()

        c, err := getClient(ctx)
        if err != nil {

        sheetsService, err := sheets.New(c)
        if err != nil {

        // 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 {

", resp)

func getClient(ctx context.Context) (*http.Client, error) {
        // Authorize using the following scopes:
        //     sheets.DriveScope
        //     sheets.DriveFileScope
        return nil, errors.New("not implemented")


hello.go:43: invalid field name "ValueInputOption" in struct initializer
hello.go:43: invalid field name "data" in struct initializer
hello.go:58: sheets.SpreadsheetsScope evaluated but not used

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: Golang google sheets API V4 - Write/Update example?

Google's API reference - see the ValueInputOption section starting at line 1437:

This article shows how to do a BatchUpdate in Java: Write data to Google Sheet using Google Sheet API V4 - Java Sample Code

  • 写回答

1条回答 默认 最新

  • douhe1864 2017-09-15 05:13

    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 (
    // 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: 
    ", 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
    ", file)
        f, err := os.Create(file)
        if err != nil {
            log.Fatalf("Unable to cache oauth token: %v", err)
        defer f.Close()
    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, "")
        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 {

    Result :

    enter image description here

    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.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?



  • ¥15 有人能看一下我宿舍管理系统的报修功能该怎么改啊?链表那里总是越界
  • ¥15 cs loadimage运行不了,easyx也下了,没有用
  • ¥15 r包runway详细安装教程
  • ¥15 Html中读取Json文件中数据并制作表格
  • ¥15 谁有RH342练习环境
  • ¥15 STM32F407 DMA中断问题
  • ¥15 uniapp连接阿里云无法发布消息和订阅
  • ¥25 麦当劳点餐系统代码纠错
  • ¥15 轮班监督委员会问题。
  • ¥20 关于变压器的具体案例分析