Updating Google Sheets using Golang.

#go #googlesheetapi #api #automation

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)

Akhil Raj • Jun 2 '22

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.

silvioprog • Aug 13 '22

Worked for me by following this tutorial: prudentdevs.club/gsheets-go

Please take a look at the picture below:

My code was:

package main

import (
    "context"
    "fmt"
    "log"
    "os"

    "golang.org/x/oauth2/google"
    "google.golang.org/api/option"
    "google.golang.org/api/sheets/v4"
)

func main() {
    data, err := os.ReadFile("secret.json")
    if err != nil {
        log.Fatalf("Unable to read client secret file: %v", err)
    }

    conf, err := google.JWTConfigFromJSON(data, sheets.SpreadsheetsScope)
    if err != nil {
        log.Fatalf("Unable to parse JWT to config: %v", err)
    }

    ctx := context.TODO()

    client := conf.Client(ctx)
    srv, err := sheets.NewService(ctx, option.WithHTTPClient(client))
    if err != nil {
        log.Fatalf("Unable to retrieve Sheets client: %v", err)
    }

    // Prints the names and majors of students in a sample spreadsheet:
    spreadsheetId := "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms"
    readRange := "Class Data!A2:E"
    resp, err := srv.Spreadsheets.Values.Get(spreadsheetId, readRange).Do()
    if err != nil {
        log.Fatalf("Unable to retrieve data from sheet: %v", err)
    }

    if len(resp.Values) == 0 {
        fmt.Println("No data found.")
    } else {
        fmt.Println("Name, Major:")
        for _, row := range resp.Values {
            // Print columns A and E, which correspond to indices 0 and 4.
            fmt.Printf("%s, %s\n", row[0], row[4])
        }
    }
}