Well. It’s that time of month again. And this time I’ve been tinkering around with Go by building a small program.

The program in question does a couple things. The intention being to pull data from a Google Sheet that I update with financial information and place that data into an SQLite database file. So first things first. Getting Google Sheets to work.

Google Sheets Authentication

The Google Sheets API, and other Google APIs those that interact with user data, require the use of OAuth2. Now I could have used an already developed Library for it. In fact Google has an official one for Go. Except I prefer to write my own. Especially for personal projects. And given the Sheet’s REST API isn’t to complicated I decided to give it a shot.

And it turns out that I’m by far the first one to want to do this. In fact someone wrote a rather nice article on how to Authenticate simply using cURL.

// client id and secret obtained from Google API Console:
// https://console.developers.google.com/apis/credentials
var clientId string = os.Getenv("ID")
var clientSecret string = os.Getenv("SECERET")
// Redirect URL. Do not need any webserver for now because this will only
// allow us to copy the redirection URL provided by Google
var redirect string = "http://localhost:8080"
// Scope, that means actions you'll be able to make with obtained token
// (this is a space separated list)
var scope string = "https://www.googleapis.com/auth/webmasters.readonly"

var link string = "https://accounts.google.com/o/oauth2/auth?client_id=" + clientId
    + "&redirect_uri=" + redirect + "&scope=" + scope
    + "&response_type=code&access_type=offline"
fmt.Println(link)

// Get the value of CODE. Retrievable from resulting URL that the login link
// redirects to.
var srv http.Server
var code string

http.HandleFunc("/", func(w http.ResponseWriter, r *http.Request) {
    codeResp := r.URL.Query().Get("code")
    if len(codeResp) > 0 {
        code = codeResp

        // Delayed shutdown of HTTP server
        go func() {
            time.Sleep(500 * time.Millisecond)
            srv.Shutdown(context.Background())
        }()
    }
    fmt.Fprintln(w, "You are now logged in.")
})

data := url.Values{}
// code from URL
data.Set("code", code)
data.Set("client_id", clientId)
data.Set("client_secret", clientSecret)
data.Set("redirect_uri", redirect)
data.Set("access_type", "offline")
data.Set("grant_type", "authorization_code")

resp, _ := http.Post("https://accounts.google.com/o/oauth2/token", 
    "application/x-www-form-urlencoded", strings.NewReader(data.Encode()))

test, _ := io.ReadAll(resp.Body)
// Struct that can be contain the unmarshaled JSON
var responseJSON responseBit
/*
{
  "access_token": "",
  "expires_in": 3599,
  "refresh_token": "",
  "scope": "https://www.googleapis.com/auth/webmasters.readonly",
  "token_type": "Bearer"
}

*/

json.Unmarshal(test, &responseJSON)
fmt.Println(resp.Status)

fmt.Println("access token", responseJSON.Access_token)
fmt.Println("refresh token", responseJSON.Refresh_token)

// Example of refreshing TOKEN using refresh Token
refresh := url.Values{}
refresh.Set("client_id", clientId)
refresh.Set("client_secret", clientSecret)
refresh.Set("refresh_token", responseJSON.Refresh_token)
refresh.Set("grant_type", "refresh_token")
resp, _ := http.Post("https://accounts.google.com/o/oauth2/token", 
    "application/x-www-form-urlencoded", strings.NewReader(refresh.Encode()))
test, _ := io.ReadAll(resp.Body)
// Another struct that is very similar to responseBit minus the refresh token
var result refreshInfo
/*
{
  "access_token": "",
  "expires_in": 3599,
  "scope": "https://www.googleapis.com/auth/webmasters.readonly",
  "token_type": "Bearer"
}
*/
json.Unmarshal(test, &result)
fmt.Println("Status: ", resp.Status)
fmt.Println("Access Token: ", result.Access_token)
for t := 0; t < 10; t++ {
    time.Sleep(10 * time.Second)
}

Above was effectively what I started out with. Using the instructions provided in the article I converted it to be useful in Go. And to test that whether the access token I used the following. With the token being placed within a HTTP Header.

req, _ := http.NewRequest(http.MethodGet, 
    "https://www.googleapis.com/oauth2/v3/tokeninfo", nil)
req.Header.Add("Authorization", "Bearer "+access_token)
client := http.Client{}
resp, _ := client.Do(req)
defer resp.Body.Close()
body, _ := io.ReadAll(resp.Body)
fmt.Println(string(body))

And with that the ability to get and use the access token I then needed a way to ensure that the access token maintains. And from doing some research I learned that this is possible with using a Go channel and a Ticker. Which was actually really simple. Then then led to mean learning how to make methods for Go structs. Do I then effectively packaged all of the Google authentication into a single package. With the access_token not being accessible outside of the package. Instead when you need to preform a request with the access token I have provided a method that is effectively a mapping of a basic GET request with the needed header added within it. The method then returning the body of the response or the error in the event something when wrong. The code below effectively being the same as in the code block directly above this paragraph except using the new Methods.

queryResult, err := auth.GetQuery(URL)
if err != nil {
    fmt.Println("PRINT INFO ERROR:", err.Error())
    return
}
fmt.Println("PRINT INFO: \n", string(queryResult))

On top of this to prevent the need to constantly sign in. The program also saves the refresh token in a secure manner and attempt to use that first before requesting the user to log in.

Google Sheets API

Next came the access of the actual Google Sheet. Which first required me to enable the Google Sheet API for the OAuth 2.0 Client IDs I have provided the program. After that I then needed to add another scope. Which is achieved (as need above in a comment) by appending the correct Scope URL. Which is my case is https://www.googleapis.com/auth/spreadsheets.readonly which provides read only access to the Signed in User’s Google Sheets.

From there the REST API is quite well documented with all the requests I’m interested in being GET requests making it even simpler by not requiring formatted payloads. I just needed to create the needed structs that would allow me to interact with the data.

SQLite Database

From there I had the ability to pull data. I had to determine how to store it in the SQLite database file. Now instead of implementing SQLite 3 myself I did use an external library but given the complexity, and the fact I wanted to do this in a month, it was the best option.

Now most of the sheet’s within my Google Sheet have 6 Columns. Those being Date, Balance, Debit, Credit, Title, and Notes. With there being 9 different sheets that I wanted to pull down. So what I created two basic tables.

CREATE TABLE IF NOT EXISTS account(
    account_id integer PRIMARY KEY,
    account_name text NOT NULL
);

CREATE TABLE IF NOT EXISTS trans(
    trans_id integer PRIMARY KEY,
    account integer NOT NULL,
    date TEXT NOT NULL,
    amount integer,
    title text,
    notes text,
    FOREIGN KEY (account)
        REFERENCES account (account_id)
);

One two store sheet names (and eventually other data about that particular sheet but hey that’s a future project) and another to store the actual transactions. Where instead of having three columns containing data on the transaction I have one. Being the change in the account. From there (and by making the assumption all accounts start at 0) I can then find the balance. And if I want to create a table similar to my old sheets I can use the following SQL command

SELECT 
    trans_id, 
    account, 
    date, 
    printf("$%.2f",ROUND((SELECT 
                            SUM(amount) 
                          FROM trans AS t 
                          WHERE t.account = w.account 
                            AND t.trans_id <= w.trans_id
                         ),2)) AS balance,
    (CASE WHEN amount < 0 THEN printf("$%.2f",-1*amount) ELSE '' END) AS debit,
    (CASE WHEN amount >= 0 THEN printf("$%.2f",amount) ELSE '' END) AS credit,
    title,
    notes
FROM trans AS w

Data Loading

From there came the need to load which was done with some simple INSERT statements and some logic that handled determine if the rows in the sheet where to be negative or positive. Which wasn’t to bad

values, _ := sheet.GetValues(sheetName, "A1:F")
transFound := len(values.Values) - 1
count := data.GetAccountTransactionCount(accountID)
if transFound > count {
    var toWrite [][]string
    for rowIndex := count + 1; rowIndex < len(values.Values); rowIndex++ {
        toWrite = append(toWrite, values.Values[rowIndex])
    }
    log.Printf("Writing %d rows from %s to %d\n", len(toWrite), sheetName, accountID)
    data.WriteTransaction(accountID, toWrite)
} else {
    log.Printf("%s up to date with %d\n", sheetName, accountID)
}

The function this was contained in also allowed me to preform the insertions only when there are more rows in the Sheet than corresponding rows within the DB.

But Why?

As for the why I did this. There are two reasons.

  1. I’ve been wanting to make a program that allows me to preform “better” analysis on the data within this Google sheet.
  2. I’m currently using Gotify within my Proxmox Instance. And by default it does not have a way to integrate with Discord Webhooks. And given it’s written in Go and I can expand it using plugins. My next project is to then add that functionality. Which I hope to post about next month.

And that’s really it. Eventually I plan on expanding this. Maybe even creating an interface for it so it can present graphs. But until then it has provided me a place to muck around with Go before having to interact with with the Gotify plugin system. And I must say. I might start using Go for more of my prototyping than Javascript or Python. I like how it feels compared to them. Especially with the fact it feels more like C which I always enjoyed writing. It just takes quite a bit of work to get something beyond basic functionality working. With Go providing a rather impressive standard library.