Golang - Google Sheet - 更新一个单元格

Question

我让它读得很好，并且在写操作期间没有提示身份验证。我没有收到任何错误，但它什么也没做。我是 Go 新手，如何记录 _, err = srv.Spreadsheets.Values.Update(spreadsheetId, writeRange, &vr).ValueInputOption("RAW").Do() 的结果

也是一个奖励，知道为什么它不起作用吗？我只想更新一个单元格的值。

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"
)

// Retrieve a token, saves the token, then returns the generated client.
func getClient(config *oauth2.Config) *http.Client {
    // The file token.json stores the user's access and refresh tokens, and is
    // created automatically when the authorization flow completes for the first
    // time.
    tokFile := "token.json"
    tok, err := tokenFromFile(tokFile)
    if err != nil {
        tok = getTokenFromWeb(config)
        saveToken(tokFile, tok)
    }
    return config.Client(context.Background(), tok)
}

// Request a token from the web, then 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 authCode string
    if _, err := fmt.Scan(&authCode); err != nil {
        log.Fatalf("Unable to read authorization code: %v", err)
    }

    tok, err := config.Exchange(context.TODO(), authCode)
    if err != nil {
        log.Fatalf("Unable to retrieve token from web: %v", err)
    }
    return tok
}

// Retrieves a token from a local file.
func tokenFromFile(file string) (*oauth2.Token, error) {
    f, err := os.Open(file)
    if err != nil {
        return nil, err
    }
    defer f.Close()
    tok := &oauth2.Token{}
    err = json.NewDecoder(f).Decode(tok)
    return tok, err
}

// Saves a token to a file path.
func saveToken(path string, token *oauth2.Token) {
    fmt.Printf("Saving credential file to: %s\n", path)
    f, err := os.OpenFile(path, os.O_RDWR|os.O_CREATE|os.O_TRUNC, 0600)
    if err != nil {
        log.Fatalf("Unable to cache oauth token: %v", err)
    }
    defer f.Close()
    json.NewEncoder(f).Encode(token)
}

func main() {
    b, err := ioutil.ReadFile("credentials.json")
    if err != nil {
        log.Fatalf("Unable to read client secret file: %v", err)
    }

    // If modifying these scopes, delete your previously saved token.json.
    config, err := google.ConfigFromJSON(b, "https://www.googleapis.com/auth/spreadsheets.readonly")
    if err != nil {
        log.Fatalf("Unable to parse client secret file to config: %v", err)
    }
    client := getClient(config)

    srv, err := sheets.New(client)
    if err != nil {
        log.Fatalf("Unable to retrieve Sheets client: %v", err)
    }

    // Prints the names and majors of students in a sample spreadsheet:
    // https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
    spreadsheetId := "16eQPQuIw0JBG3lCLdsLEdEfaJO0Hll0GfahdpNGoxhw"
    readRange := "DE Stats!A1:B4"
    resp1, err := srv.Spreadsheets.Values.Get(spreadsheetId, readRange).Do()
    if err != nil {
        log.Fatalf("Unable to retrieve data from sheet: %v", err)
    }

    if len(resp1.Values) == 0 {
        fmt.Println("No data found.")
    } else {
        for _, row := range resp1.Values {
            // Print columns A and E, which correspond to indices 0 and 4.
            fmt.Printf("%s, %s\n", row[0], row[1])
        }
    }

    // The ID of the spreadsheet to update.
    //spreadsheetId := "16eQPQuIw0JBG3lCLdsLEdEfaJO0Hll0GfahdpNGoxhw" // TODO: Update placeholder value.
    //range2 := "DE Stats!B1"
    //values := []interface{{}}{"121212"}}
    //valueInputOption := "USER_ENTERED"
    //rb := &sheets.ValueRange{
    //  MajorDimension: "ROWS",
    //  Values:         values,
    //}
    //resp2, err := srv.Spreadsheets.Values.Update(spreadsheetId, range2, rb).ValueInputOption(valueInputOption).Do()

    // TODO: Change code below to process the `resp` object:
    //fmt.Printf("%#v\n", resp2)

    // Prints the names and majors of students in a sample spreadsheet:
    // https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
    //spreadsheetId := "16eQPQuIw0JBG3lCLdsLEdEfaJO0Hll0GfahdpNGoxhw"
    //readRange := "DE Stats!A1:B4"
    resp3, err := srv.Spreadsheets.Values.Get(spreadsheetId, readRange).Do()
    if err != nil {
        log.Fatalf("Unable to retrieve data from sheet: %v", err)
    }

    if len(resp3.Values) == 0 {
        fmt.Println("No data found.")
    } else {
        for _, row := range resp3.Values {
            // Print columns A and E, which correspond to indices 0 and 4.
            fmt.Printf("%s, %s\n", row[0], row[1])
        }
    }
}
func write() {
    //ctx := context.Background()
    b, err := ioutil.ReadFile("./credentials.json")
    if err != nil {
        log.Fatalf("Unable to read client secret file: %v", err)
    }

    // If modifying these scopes, delete your previously saved credentials
    // at ~/.credentials/sheets.googleapis.com-go-quickstart.json
    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(config)

    srv, err := sheets.New(client)
    if err != nil {
        log.Fatalf("Unable to retrieve Sheets Client %v", err)
    }

    spreadsheetId := "16eQPQuIw0JBG3lCLdsLEdEfaJO0Hll0GfahdpNGoxhw"

    writeRange := "DE Stats!A1:A2"

    var vr sheets.ValueRange

    myval := []interface{}{"One", "blah"}
    vr.Values = append(vr.Values, myval)

    _, err = srv.Spreadsheets.Values.Update(spreadsheetId, writeRange, &vr).ValueInputOption("RAW").Do()
    if err != nil {
        log.Fatalf("Unable to retrieve data from sheet. %v", err)
    }
}

Answer 1

我相信你的目标如下。

您想将值放入工作表 DE Stats 上的单元格“A1:A2”中.

这个答案怎么样？

修改点:

我认为 main() 的脚本和 write()因为获取和放置值是正确的。但需要稍作修改。

在您的脚本中，write() 的功能未使用。 srv.Spreadsheets.Values.Get(spreadsheetId, readRange).Do()正在运行。但如果细胞 DE Stats!A1:B4没有值，No data found.显示。

在您的脚本中，您尝试使用 2 个独立的范围作为一个 credentials.json .在您的情况下，为了使用 https://www.googleapis.com/auth/spreadsheets用于获取和放置值作为范围。

在您的脚本中，来自 writeRange := "DE Stats!A1:A2" ，尝试将值放入单元格“A1:A2”。但是你使用 myval := []interface{}{"One", "blah"} .在这种情况下，会发生错误。所以请修改范围或值。在这个答案中，范围被修改。

当以上几点反射(reflect)到您的脚本时，它变成如下。

修改后的脚本:

请修改 main()如下。然后， 请删除token.json的文件的 tokFile := "token.json"并重新授权范围。这样，新的作用域就会反射(reflect)到访问 token 和刷新 token 上。请注意这一点。

func main() {
    b, err := ioutil.ReadFile("./credentials.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(config)
    srv, err := sheets.New(client)
    if err != nil {
        log.Fatalf("Unable to retrieve Sheets Client %v", err)
    }

    spreadsheetId := "16eQPQuIw0JBG3lCLdsLEdEfaJO0Hll0GfahdpNGoxhw"

    // Put values to "DE Stats!A1:B1".
    writeRange := "DE Stats!A1"
    var vr sheets.ValueRange
    myval := []interface{}{"One", "blah"}
    vr.Values = append(vr.Values, myval)
    _, err = srv.Spreadsheets.Values.Update(spreadsheetId, writeRange, &vr).ValueInputOption("RAW").Do()
    if err != nil {
        log.Fatalf("Unable to retrieve data from sheet. %v", err)
    }

    // Get values from "DE Stats!A1:B4".
    readRange := "DE Stats!A1:B4"
    resp1, err := srv.Spreadsheets.Values.Get(spreadsheetId, readRange).Do()
    if err != nil {
        log.Fatalf("Unable to retrieve data from sheet: %v", err)
    }
    if len(resp1.Values) == 0 {
        fmt.Println("No data found.")
    } else {
        for _, row := range resp1.Values {
            fmt.Printf("%s, %s\n", row[0], row[1])
        }
    }
}

在这个修改后的脚本中，首先，"One", "blah" 的值被放到工作表 DE Stats 上的单元格“A1:B1”中，以及工作表 DE Stats 上单元格“A1:B4”的值被检索。

笔记:

在您的 write() 脚本中，"One", "blah" 的值被放入单元格“A1:B1”。

引用:

Method: spreadsheets.values.update

Method: spreadsheets.values.get