Google Spreadsheets Paper Test By Calling Google’s Spreadsheets API
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… 😓
Read other posts