19

Trying to write a simple three column table ([][]string) with Go, but can't. The quick start guide is very nice, I now can read sheets, but there no any example of how to write data to a sheet, maybe it is trivial, but not for me it seems. The Golang library for my brains is just too complicated to figure out. And there not a single example I could google...

This C# example very looks close, but I am not sure I clearly understand C#

Community
  • 1
  • 1
MikeKlemin
  • 909
  • 1
  • 8
  • 26
  • 3
    Upvoted for admitting that some things are too complicated for your brain to figure out. Some people seem to think the existence of code you can read is justification for not creating good documentation - I am not one of those people. – Michael Sep 28 '18 at 21:09

3 Answers3

24

Well after some tryouts, there is an answer. Everything is same as in https://developers.google.com/sheets/quickstart/go Just changes in the main function 

func write() {
    ctx := context.Background()
    b, err := ioutil.ReadFile("./Google_Sheets_API_Quickstart/client_secret.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(ctx, config)

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

    spreadsheetId := "YOUR SPREADSHEET ID"

    writeRange := "A1"

    var vr sheets.ValueRange

    myval := []interface{}{"One", "Two", "Three"}
    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)
    }

}
MikeKlemin
  • 909
  • 1
  • 8
  • 26
8

Well if you are looking for Service Account based authentication, then the following worked for me.

Download the client secret file for service account from https://console.developers.google.com

import (
        "fmt"
        "golang.org/x/net/context"
        "google.golang.org/api/option"
        "google.golang.org/api/sheets/v4"
        "log"
    )


const (
    client_secret_path = "./credentials/client_secret.json"
)


func NewSpreadsheetService() (*SpreadsheetService, error) {
    // Service account based oauth2 two legged integration
    ctx := context.Background()
    srv, err := sheets.NewService(ctx, option.WithCredentialsFile(client_secret_path), option.WithScopes(sheets.SpreadsheetsScope))

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

    c := &SpreadsheetService{
        service: srv,
    }

    return c, nil
}


func (s *SpreadsheetService) WriteToSpreadsheet(object *SpreadsheetPushRequest) error {

    var vr sheets.ValueRange
    vr.Values = append(vr.Values, object.Values)

    res, err := s.service.Spreadsheets.Values.Append(object.SpreadsheetId, object.Range, &vr).ValueInputOption("RAW").Do()

    fmt.Println("spreadsheet push ", res)

    if err != nil {
        fmt.Println("Unable to update data to sheet  ", err)
    }

    return err
}   

type SpreadsheetPushRequest struct {
    SpreadsheetId string        `json:"spreadsheet_id"`
    Range         string        `json:"range"`
    Values        []interface{} `json:"values"`
}
Sahil Garg
  • 105
  • 1
  • 8
  • 1
    Excellent approach for those who don't want to deal with interactive OAuth2 permission process, ie you want it to be server-side. Get the key and you're done. The only gotcha here is that the Service Account must be nominated as project owner, and then you share your required sheet with the account's email. The process is described here https://stackoverflow.com/questions/38949318/google-sheets-api-returns-the-caller-does-not-have-permission-when-using-serve – Denis Zubkov Jan 15 '20 at 06:37
0
  1. change the scope in the quickstart example from spreadsheets.readonly to spreadsheets for r/w access
  2. here is the write snippet:
    writeRange := "A1" // or "sheet1:A1" if you have a different sheet
    values := []interface{}{"It worked!"}
    var vr sheets.ValueRange
    vr.Values = append(vr.Values,values
    _, err = srv.Spreadsheets.Values.Update(spreadsheetId,writeRange,&vr).ValueInputOption("RAW").Do()

and that should work:

balu
  • 35
  • 1
  • 4