spreadsheets.values.batchUpdate using Golang

15 Sep 2017, 14:14

GoTips / spreadsheet

golang / spreadsheet / SheetsAPI / batchUpdate

Flow :

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 :