So I have made one post about doing paper test by using google app script. For a fully automated paper testing, you can use Google’s Spreadsheets API to track your paper test result. How to do it?

1. Connect to Google’s Spreadsheets API

There is a quickstart documentation. And I have posted mine:

package main

import (
	"time"
	"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(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)
}

// 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)
}

2. okie dokie now, let’s create a function to call Google’s Spreadsheets API

func callGoogle(r string, value [][]interface{} ) {

	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 token.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)
		}
		
        c:= getClient(ctx, config)
        sheetsService, err := sheets.New(c)
        if err != nil {
                log.Fatal(err)
        }

        // The ID of the spreadsheet to update.
        spreadsheetId := "***********"  // Put your spreadsheetId here.

        // The A1 notation of the values to update.
	//	range2 := "sheet1!A1:D1" // TODO: Update placeholder value.
	//	values := [][]interface{}{{"current_price", "buy", "sell", "P/L"}}

		range1 := r // TODO: Update placeholder value.
		values1 := value

  
        // How the input data should be interpreted.
        valueInputOption := "USER_ENTERED" // TODO: Update placeholder value.

        rb := &sheets.ValueRange{
                // TODO: Add desired fields of the request body. All existing fields
				// will be replaced.
				Range:  range1,
				Values: values1,
				
        }

        resp, err := sheetsService.Spreadsheets.Values.Update(spreadsheetId, range1, rb).ValueInputOption(valueInputOption).Context(ctx).Do()
        if err != nil {
                log.Fatal(err)
        }

        fmt.Sprintf("%#v\n", resp)
}

3. finally we are here! Let’s actually do something.

How about let’s update the btc spot price? Yes, I know I can just use the Class UrlFetchApp instead of creating my own function…But why not 🤓🤓🤓?

type jsonData struct {
		Data struct {
			Base     string `json:"base"`
			Currency string `json:"currency"`
			Amount   string `json:"amount"`
		} `json:"data"`
	}

func btcSpot()(string){
	e:= "BTC-USD"
	url := fmt.Sprintf("https://api.coinbase.com/v2/prices/" + e + "/spot")
	req, err := http.NewRequest("GET", url, nil)
	if err != nil {
		log.Fatal(err)
	}

	req.Header.Set("Authorization", "Bearer **************")

	resp, err := http.DefaultClient.Do(req)
	if err != nil {
		// handle err
		log.Fatal(err)
	}
	defer resp.Body.Close()

	body, readErr := ioutil.ReadAll(resp.Body)
	if readErr != nil {
		log.Fatal(readErr)
	}

	var record jsonData
	if jsonErr := json.Unmarshal(body, &record); jsonErr != nil {
		log.Println(jsonErr)
	}

	return record.Data.Amount

}

func updateBTC(t time.Time){
	v := btcSpot()
	values := [][]interface{}{{v}}
	callGoogle("sheet1!A2:A2", values)
}

And you can even schedule it yourself 😃

func doEvery(d time.Duration, f func(time.Time)) {
	for x := range time.Tick(d) {
		f(x)
	}
}

func main() {
	 doEvery(5*time.Second, updateBTC)
}

4. However…

Soon I hit my google spreadsheet API limits… It would be so much easier if I just wrote a local csv file and started a http server to refresh the page every 5 second… 😓

Buy me a coffee