So we have a pipeline, but all the code is just stubbed out. Let's start filling in the blanks by reading the spreadsheet.
In doing this, I'm not going to make any pretense to "understand" what is in the spreadsheet. I am just going to convert each tab into a list of rows, each of which is a map of column name (taken from row 1) to an any value (the value in the cell of row K in the corresponding column). The final result, passed down the pipeline, is a map of tab name to a list of such rows.
Not that I've followed it as such, but there is a "quickstart" provided by Google.
Authentication
The first step is to make sure that we can authenticate the application to use Google Sheets. In the past, I've used OAuth to do this, but today I see that "API Key" is an option, so let's try that.First, you have to go to the Google Cloud Welcome Page (all this has changed since I last did it, of course). In the top left corner, there's a thing which tells you which project you are working in, so click that and select 'New Project'.
From there, it's possible to select "APIs & Services" and then "Credentials" (look everywhere for all these things; I don't know who designed this UI, but it wasn't a UX professional). There is a "create credentials" button which has the option to create an API Key. Because it insisted, I also went through the "define branding" setup page, but I'm not sure whether that's needed.
You then need to go to the "API LIbrary" and find the "Google Sheets API" and enable it.
This API key goes in our configuration file, which now looks something like this (as I said, I'm not going to share all my files, because, well, it's kind of private).
{And we can update Configuration to reflect the fact that this is there:
"Spreadsheet": "<id>",
"APIKey": "<key>",
"Output": "<a local file>"
}
package config
type Configuration struct {
APIKey string
Spreadsheet string
Output string
}
GNUCASH_CONNECT_SHEETS:accounts/internal/gnucash/config/config.go
In order to use Google Sheets with Go, we need to get the appropriate library:$ go get google.golang.org/api/sheets/v4which loads in a lot more packages as well, and updates go.mod and creates go.sum.
As we start to write the code to connect to sheets, I realize that I had originally said I would pass the whole Configuration around, but ended up just passing fields. So I went back and changed that. Now we can start writing the code to connect to the sheets:
package sheets
import (
"context"
"fmt"
"github.com/gmmapowell/ignorance/accounts/internal/gnucash/config"
"google.golang.org/api/option"
sheets "google.golang.org/api/sheets/v4"
)
func ReadSpreadsheet(conf *config.Configuration, rcvr Receiver) {
ctx := context.Background()
sheetsService, err := sheets.NewService(ctx, option.WithAPIKey(conf.APIKey))
if err != nil {
panic(err)
}
call := sheetsService.Spreadsheets.Get(conf.Spreadsheet)
fmt.Printf("%v\n", call)
}
GNUCASH_CONNECT_SHEETS:accounts/internal/gnucash/sheets/reader.go
Very good.For what it's worth, the reference page for the Sheets library is at https://pkg.go.dev/google.golang.org/api/sheets/v4.
Troubleshooting
I always have problems with Google APIs.It turns out that all I have done here is to create a "call" which still needs to be executed in order to actually connect to the Sheets API. This requires a .Do() to be put on the end. When I do that, it tells me that I don't have permission.
I think the deal is, that while the API key allows me to connect to the API, it doesn't allow me to access my spreadsheet. This is because, of course, while I see them as both "mine", Google Cloud doesn't. OK, let's add an OAuth API key, and copy it down onto the filesystem and provide that in the Configuration.
When I do that, it tells me that it won't allow two forms of authentication. OK, let's go back and look at the sample and try and copy that code "more closely".
There are, of course, a number of things I don't like about that code, including the manual copying-and-pasting into the browser (which I replaced by using the package github.com/pkg/browser and the manual copying-and-pasting of the response (which took a bit more work since I had to implement a browser). This also required adding some more configuration options. But at the end of the day I was able to get it to work and to recover the slice of sheets and print out the Title of each one.
This has changed sufficiently that I'm just going to present it all again. Here's the configuration:
package config
import "fmt"
type Configuration struct {
APIKey string
OAuth string
Token string
RedirectPort int
Spreadsheet string
Output string
}
func (c *Configuration) RedirectURI() string {
return fmt.Sprintf("http://localhost:%d/redirect_uri", c.RedirectPort)
}
func (c *Configuration) ListenOn() string {
return fmt.Sprintf(":%d", c.RedirectPort)
}
GNUCASH_AUTHENTICATE:accounts/internal/gnucash/config/config.go
And then all of the code to set up servers, read tokens, connect, burble burble:package sheets
import (
"context"
"encoding/json"
"errors"
"fmt"
"net/http"
"os"
"github.com/gmmapowell/ignorance/accounts/internal/gnucash/config"
"github.com/pkg/browser"
"golang.org/x/oauth2"
"golang.org/x/oauth2/google"
"google.golang.org/api/option"
sheets "google.golang.org/api/sheets/v4"
)
func ReadSpreadsheet(conf *config.Configuration, rcvr Receiver) {
ctx := context.Background()
client, err := getClient(conf)
if err != nil {
panic(err)
}
sheetsService, err := sheets.NewService(ctx, option.WithHTTPClient(client))
if err != nil {
panic(err)
}
svc, err := sheetsService.Spreadsheets.Get(conf.Spreadsheet).Do()
if err != nil {
panic(err)
}
for _, id := range svc.Sheets {
fmt.Println(id.Properties.Title)
}
}
func getClient(conf *config.Configuration) (*http.Client, error) {
jc, err := readCredentials(conf)
if err != nil {
return nil, err
}
tok, err := getToken(conf, jc)
if err != nil {
return nil, err
}
return jc.Client(context.Background(), tok), nil
}
func readCredentials(conf *config.Configuration) (*oauth2.Config, error) {
b, err := os.ReadFile(conf.OAuth)
if err != nil {
return nil, fmt.Errorf("unable to read client secret file: %v", err)
}
c, err := google.ConfigFromJSON(b, "https://www.googleapis.com/auth/spreadsheets.readonly")
if err != nil {
return nil, fmt.Errorf("unable to parse client secret file to config: %v", err)
}
c.RedirectURL = conf.RedirectURI()
return c, nil
}
func getToken(conf *config.Configuration, jc *oauth2.Config) (*oauth2.Token, error) {
tok, err := tokenFromFile(conf.Token)
if err == nil {
return tok, nil
}
tok, err = tokenFromWeb(conf, jc)
if err != nil {
return nil, err
}
err = saveToken(conf, tok)
if err != nil {
return nil, err
}
return tok, nil
}
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
}
func tokenFromWeb(conf *config.Configuration, jc *oauth2.Config) (*oauth2.Token, error) {
ch := make(chan string)
ws := launchWebServer(conf, ch)
authURL := jc.AuthCodeURL("state-token", oauth2.AccessTypeOffline)
browser.OpenURL(authURL)
defer ws.Shutdown(context.Background())
authCode := <-ch
tok, err := jc.Exchange(context.TODO(), authCode)
if err != nil {
return nil, fmt.Errorf("unable to retrieve token from web: %v", err)
}
return tok, nil
}
func saveToken(conf *config.Configuration, tok *oauth2.Token) error {
fmt.Printf("Saving credential file to: %s\n", conf.Token)
f, err := os.OpenFile(conf.Token, os.O_RDWR|os.O_CREATE|os.O_TRUNC, 0600)
if err != nil {
return fmt.Errorf("unable to cache oauth token: %v", err)
}
defer f.Close()
json.NewEncoder(f).Encode(tok)
return nil
}
func launchWebServer(conf *config.Configuration, ch chan string) *http.Server {
handlers := http.NewServeMux()
redirectUri := &RedirectHandler{channel: ch}
handlers.Handle("/redirect_uri", redirectUri)
addr := conf.ListenOn()
server := &http.Server{Addr: addr, Handler: handlers}
go func() {
err := server.ListenAndServe()
if err != nil && !errors.Is(err, http.ErrServerClosed) {
fmt.Printf("error starting server: %s\n", err)
}
}()
return server
}
type RedirectHandler struct {
channel chan string
}
func (r *RedirectHandler) ServeHTTP(resp http.ResponseWriter, req *http.Request) {
code := req.URL.Query()["code"]
r.channel <- code[0]
}
GNUCASH_AUTHENTICATE:accounts/internal/gnucash/sheets/reader.go
That's long enough that I think it deserves some explanation, but I'm not really sure what to say. Hopefully the function names and structure give most of the clarity that is required. I think the only real alternative is to go back and rewrite Google's example and documentation for them and I'm not really interested in doing that. The point being that I don't really feel I have written any of this code, I just scrabbled things together from Google and made it work. In the ordinary course of events I would shunt this off into a library - it's exactly the same code regardless of which Google Sheet you're reading and what you're doing with it.Reading the Sheets
Now we can move on to actually reading and parsing the data in the sheets, which is why we came here in the first place. We can see that we have the sheets and that each one has a Title. Now we need to recover the values in each sheet and slice and dice it into something sensible (i.e. a list of rows, where each row is a map of the header to the value).The way in which the Google APIs work always seems "backwards" to me, because you keep having to go back to the service and ask for this or that, rather than it returning more "active" objects. Anyway, with a little bit of cribbing and a little bit of googling, I managed to get this to work out:
func ReadSpreadsheet(conf *config.Configuration, rcvr Receiver) {
ctx := context.Background()
client, err := getClient(conf)
if err != nil {
panic(err)
}
sheetsService, err := sheets.NewService(ctx, option.WithHTTPClient(client))
if err != nil {
panic(err)
}
info, err := sheetsService.Spreadsheets.Get(conf.Spreadsheet).Do()
if err != nil {
panic(err)
}
var tabs []Tab
for _, sheet := range info.Sheets {
tab := gatherTabData(sheetsService, conf.Spreadsheet, sheet.Properties.Title, sheet.Properties.GridProperties.RowCount, sheet.Properties.GridProperties.ColumnCount)
tabs = append(tabs, tab)
}
rcvr.DeliverSheet(tabs)
}
func gatherTabData(svc *sheets.Service, sheetId, title string, rc, cc int64) Tab {
cells, err := svc.Spreadsheets.Values.Get(sheetId, fmt.Sprintf("%s!R1C1:R%dC%d", title, rc, cc)).Do()
if err != nil {
panic(err)
}
heads := make(map[int]string)
var tab Tab
for i, r := range cells.Values {
if i == 0 {
for j, c := range r {
heads[j] = c.(string)
}
} else {
row := Row{Columns: make(map[string]any)}
for j, c := range r {
if heads[j] != "" {
row.Columns[heads[j]] = c
}
}
tab.Rows = append(tab.Rows, row)
}
}
return tab
}
GNUCASH_READ_SPREADSHEET:accounts/internal/gnucash/sheets/reader.go
I noticed during my googling that other people seemed to have similar frustrations and had gone as far as to write a wrapper around the sheets API to make it work more sensibly. Anyway, I hope what I ended up writing was clear enough. If not, get in touch and I'll try and address your concerns.
No comments:
Post a Comment