At work I'm mostly working with Google Sheets there is a lot of manual work that goes into this, this script basically takes data from a MySQL DB(List of articles and other things) and dumps them to an Excel Sheet which can then be used by people on floor to access these links quickly.
I’m a beginner in Golang, so there might be a lot of bad practices in the scripts, the interface part had me confused and the Guide on Google wasn't helping me so here I am writing this article, Hope this helps.
Prerequisites
- Golang
- Google Service account/ Auth Flow
- Golang Packages
- Golang MySQL (Click here)
- Google Sheet API packages (Click here)
First we will look at the Imports needed.
package main
import (
"encoding/json"
"fmt"
"io/ioutil"
"log"
"net/http"
"os"
"database/sql"
_ "github.com/go-sql-driver/mysql"
"time"
"golang.org/x/net/context"
"golang.org/x/oauth2"
"golang.org/x/oauth2/google"
"google.golang.org/api/sheets/v4"
)
These imports were needed for the data I was using. Now for the next part these methods are used when you follow the google auth flow
// Retrieve a token, saves the token, then returns the generated client.
func getClient(config *oauth2.Config) *http.Client {
// The file token.json stores the user's access and refresh tokens, and is
// created automatically when the authorization flow completes for the first
// time.
tokFile := "token.json"
tok, err := tokenFromFile(tokFile)
if err != nil {
tok = getTokenFromWeb(config)
saveToken(tokFile, tok)
}
return config.Client(context.Background(), 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)
}
And finally the main function, I've added comments to make it easier to understand.
func main() {
// Use your credentials here.
db, err := sql.Open("mysql", "mysqlUser:mySQLpw@tcp(127.0.0.1:3306)/db_name")
db.SetConnMaxLifetime(time.Minute * 3)
db.SetMaxOpenConns(10)
db.SetMaxIdleConns(10)
if err != nil {
panic(err.Error())
}
defer db.Close()
// Fetch the Query from the DB
res,err := db.Query("select id,title,date(updated_at) from articles where status='Published' order by updated_at desc;")
if err !=nil {
panic(err.Error())
}
// Creating an Interface
rows := make([][]interface{},0)
// Adding the first line of the sheet
rows = append(rows,[]interface{}{"Last Updated","Title","Article Link"})
// Then we loop over the Result Data
for res.Next() {
// Declare the necessary data
var (
id, title,date string
)
err = res.Scan(&id,&title,&date)
if err != nil {
panic(err.Error()) // proper error handling instead of panic in your app
}
// Specific to My Use case I had to build a link
link := fmt.Sprintf("http://<domain>/article/%s", id)
// Append the data to add to the sheet
rows=append(rows,[]interface{}{date,title,link})
}
// I had to use a Service Account to build my app
b, err := ioutil.ReadFile("service-account-creds.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.JWTConfigFromJSON(b, "https://www.googleapis.com/auth/spreadsheets")
if err != nil {
log.Fatalf("Unable to parse client secret file to config: %v", err)
}
client := config.Client(oauth2.NoContext)
spreadsheetId := "<spreadsheetID>"
//readRange := "Sheet1!A2:B5"
srv, err := sheets.New(client)
if err != nil {
log.Fatalf("Unable to retrieve Sheets client: %v", err)
}
// Modify this to your Needs
rangeData := "sheet1!A1:E"
rb := &sheets.BatchUpdateValuesRequest{
ValueInputOption: "USER_ENTERED",
}
rb.Data = append(rb.Data, &sheets.ValueRange{
Range: rangeData,
Values: rows,
})
// Do a batch update at once
_, err = srv.Spreadsheets.Values.BatchUpdate(spreadsheetId, rb).Do()
if err != nil {
log.Fatal(err)
}
}
The complete gist can be found here
Thank you for reading.
Top comments (4)
In case you are getting this error :
Error 403: The caller does not have permission, forbidden
exit status 1
Make sure that the the link of the spreadsheet that you are giving, there should be permission to edit on that link.
Worked for me by following this tutorial: prudentdevs.club/gsheets-go
Please take a look at the picture below:
My code was:
You just saved my day maaan! Thank you!
Heya, glad it helped you :) makes me happy