CRUD Golang API using Google Sheets Spreadsheet API

In this guide, you will create a complete CRUD CMS using Golang Google Sheets. You will learn how to create a Golang Google Sheets RESTful API to manage Spreadsheets. In summary, you will:

• Create and connect a Golang API with Google Sheets API.
• Create a Google Spreadsheet CMS and use Google Sheets as your database.
• How to read Google Sheets Data using Golang.
• Learn how to add new data cells using Golang to Google Sheets.
• Use Golang to Update and Delete Google Sheets Data.

Let’s Dive in and get your Golang API Google Sheets CMS ready

Prerequisites to creating a Golang Google Sheets API

To create your Golang Google Sheets API, ensure the following:

• You have a Google and Google Cloud Platform accounts
• Golang installed on your computer
• Basic knowledge of working with Golang and Google Sheets will be excellent.

Creating Golang Google Sheets API Access Credentials

Google Sheets uses Google Cloud Platform API. This means you need access to your Google Cloud console, create Google Sheet access credentials that Golang will use, and have write and read access to your Spreadsheet. Here are the steps you need to archive that

Open Google Cloud Console in a browser, ensuring you are logged in to your Google account, and create a New Project as follows:

Now Go ahead and select the project you just created

You must Enable API and Service so Golang can access Google Sheets API. Navigate to API and Services

Then Click Enable API and Service:

Select your API Library. In this case, search for google sheets API as follows:

This will allow you to create and modify spreadsheet data in Google Sheets. So, select Google Sheets API as follows:

In the next screen, you should be able to Enable Read and Write Google Sheets data as follows:

Google will set you to Google Sheets API ready. To access it with Golang, you can navigate to the following and select Service Accounts:

You are creating the credentials Golang will use to access the Google Sheets API. Once you navigate to Manage service accounts, you need to create a service account since you don’t have one:

Provide your Service account details and Click Done.

To access the created credentials, navigate to Manage keys.

Under keys, navigate to Add Key and select Create new key and ensure the key is of type JSON and Create

This step should automatically download your credentials in a JSON file. Save this file as key.json and ensure you add it to the same directory you’re hosting your Golang app.

Creating your Spreadsheet

Next, ensure you have your Spreadsheet file ready. You can create one in your Google Docs

You can have the content of your choice here. For Golang to access this spreadsheet, you need to add access to the Google Sheet API as follows:

Open your key.json file and copy its client_email address:

You will use this email as the email the spreadsheet will use to grant Editor permission.

On your Spreadsheet doc, navigate to Share and add the copied address, ensuring the Editor access is selected, and click Send:

Connection Google Sheet API to Golang

Now that your Google Sheet API is ready let’s jump in and create the Golang app.

In your project directory, run the following command and initialize a Go app:

This should be the exact location where you saved your key.json file.

go mod init google-sheets-api

Download the following Go packages:

go get golang.org/x/oauth2

go get google.golang.org/api

Create a main.go file and add a connection to your Google Sheet API as follows:

• Add your import modules:

import (
    "context"
    "fmt"
    "io/ioutil"
    "log"
    "net/http"

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

• Add Spreadsheet and Google Sheet API credentials to Golang as follows:

const (
    spreadsheetID = "1mFKHlvAFAlWlHSXKUPkZ8EPYKUnA6PkpMUNbNZ0joOA"
    readRange = "Sheet1!A:C"
    credentials = "key.json"
)

In this case

1. spreadsheetID should be the id of your spreadsheet. If your doc URL is https://docs.google.com/spreadsheets/d/your_id/edit#gid=0, you should copy your_id as spreadsheetID.

2. readRange should be the range of the cell row you want to read. In this case, the rows run from A to C, and thus Sheet1!A:C
3. The credentials should ready your key.json file you downloaded earlier.

• Create the following variable so Go can access the Google Sheet methods:

var sheetsService *sheets.Service

Finally, create a main function and load the Google Sheets API credentials from your JSON file as follows:

func main() {
    // Load the Google Sheets API credentials from your JSON file.
    creds, err := ioutil.ReadFile(credentials)
    if err != nil {
        log.Fatalf("Unable to read credentials file: %v", err)
    }

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

    client := config.Client(context.Background())
    sheetsService, err = sheets.NewService(context.Background(), option.WithHTTPClient(client))
    if err != nil {
        log.Fatalf("Unable to create Google Sheets service: %v", err)
    }

    // Start the HTTP server.
    port := ":8080"
    fmt.Printf("Server is listening on port %s...\n", port)
    http.ListenAndServe(port, nil)
}

If all your steps are correct up to this point, Go should be able to run successfully and establish connections to Google Sheet API.

Reading data from Google Sheet API using Golang

Now that you can access your spreadsheet using Google Sheet API let’s use Golang to read the data you have.

On your main.go file and the following:

func ReadData(w http.ResponseWriter, r *http.Request) {
    resp, err := sheetsService.Spreadsheets.Values.Get(spreadsheetID, readRange).Context(r.Context()).Do()
    if err != nil {
        http.Error(w, err.Error(), http.StatusInternalServerError)
        return
    }

    data, _ := json.Marshal(resp.Values)
    w.Header().Set("Content-Type", "application/json")
    w.Write(data)
}

This example will send a request to Google Sheet API using a Get method. In this case, Get will return a range of values saved in your spreadsheet specified in the spreadsheet ID and range, So:

• range: The range’s A1 notation or R1C1 notation to retrieve values from.
• spreadsheetId: is the ID of the spreadsheet to retrieve data from.

To run your ReadData method, add the HandleFunc and its route function inside your main function, just above before your HTTP server, as follows.

func main() {
<....Rest of the code....>
    // Read data here
    http.HandleFunc("/read", ReadData)
    
    // Start the HTTP server.
    port := ":8080"
    fmt.Printf("Server is listening on port %s...\n", port)
    http.ListenAndServe(port, nil)
}

Go ahead and run the following command to start your Go server:

go run main.go

Go to Postman on any API testing tool and send a GET request to http://localhost:8080/read, and this should read your Spreadsheet data as follows:

This works. You have the same Spreadsheet data that you can use. You can go further and customize the response if you want a complete JSON object:

func ReadData(w http.ResponseWriter, r *http.Request) {
    resp, err := sheetsService.Spreadsheets.Values.Get(spreadsheetID, readRange).Context(r.Context()).Do()
    if err != nil {
        http.Error(w, err.Error(), http.StatusInternalServerError)
        return
    }

    response := struct {
        Data [][]interface{} `json:"data"`
    }{
        Data: resp.Values,
    }

    data, _ := json.Marshal(response)
    w.Header().Set("Content-Type", "application/json")
    w.Write(data)
}

Adding Spreadsheet data using Golang and Google Sheets API

Just like any API, you should be able to send a POST request and add new data with your Go API as follows:

func CreateData(w http.ResponseWriter, r *http.Request) {
    // Parse request body to get data to be added.
    type RequestData struct {
        Values [][]interface{} `json:"data"`
    }

    var requestData RequestData

    err := json.NewDecoder(r.Body).Decode(&requestData)
    if err != nil {
        http.Error(w, err.Error(), http.StatusBadRequest)
        return
    }

    values := sheets.ValueRange{Values: requestData.Values}
    _, err = sheetsService.Spreadsheets.Values.Append(spreadsheetID, readRange, &values).ValueInputOption("RAW").Do()
    if err != nil {
        http.Error(w, err.Error(), http.StatusInternalServerError)
        return
    }

    w.WriteHeader(http.StatusCreated)
}

In this case:

• You will send JSON data that Golang and Google Spreadsheet API will convert and add to your Spreadsheet cells. Go will Decode and Unmarshal the JSON-encoded value from its input and stores it in the data.
• ValueInputOption tells Go how these data inputs should be interpreted.
• Do will finally execute the sheets.spreadsheets.values.append call and save the response headers to the Spreadsheet.

To test this, add a new HTTP handler in your main:

    http.HandleFunc("/read", ReadData)
    http.HandleFunc("/create", CreateData)

Rerun your application and use the POST method to send a Post request to http://localhost:8080/create as follows:

In this case, your JSON input should be as follows:

{
  "data": [
    ["New Value 1", "New Value 2", "New Value 3"],
    ["New Title", "New Content", "New Author"]
  ]
}

Once you hit SEND, go ahead and refresh your Spreadsheet doc and confirm these changes:

Updating Spreadsheet Data

If you want to update your Spreadsheet in the Google Sheet, this will differ from a typical API.

Normally, you would send a PUT request. However, using the Google Sheet API, you will choose POST, which takes the row number as the unique identifier of the row to be updated with the new data.

Below is your Golang update method:

// Update Data
func UpdateData(w http.ResponseWriter, r *http.Request) {
    var requestData struct {
        RowToUpdate int           `json:"row_to_update"`
        NewData []interface{} `json:"new_data"`
    }

    err := json.NewDecoder(r.Body).Decode(&requestData)
    if err != nil {
        http.Error(w, err.Error(), http.StatusBadRequest)
        return
    }

    // Update data in the Google Sheets using the Google Sheets API.
    rangeToUpdate := fmt.Sprintf("Sheet1!A%d:C%d", requestData.RowToUpdate, requestData.RowToUpdate)
    values := sheets.ValueRange{Values: [][]interface{}{requestData.NewData}}

    _, err = sheetsService.Spreadsheets.Values.Update(spreadsheetID, rangeToUpdate, &values).
        ValueInputOption("RAW").
        Context(r.Context()).
        Do()

    if err != nil {
        http.Error(w, err.Error(), http.StatusInternalServerError)
        return
    }

    w.WriteHeader(http.StatusOK)
}

The request body will have the cell and the new data you want to update. The Update method will select a range of a spreadsheet you want to update with the RowToUpdate that accepts JSON payload with the new data.

Now add your update HTTP handler and use the spreadsheets.Values.Update method from the Google Sheets API.

http.HandleFunc("/read", ReadData)
http.HandleFunc("/create", CreateData)
http.HandleFunc("/update", UpdateData)

Rerun you Golang App and send a POST request to http://localhost:8080/update as follows:

In this case, you must add row_to_update and the row you want to update and new_data as the new values that you want this row to have:

{
  "row_to_update": 3,      // Specify the row number to update
  "new_data": ["Updated Title", "Updated Content", "Updated Author"]  // New data
}

Refresh your doc and check if this POST request successfully updates your data:

Deleting Spreadsheet data using Golang and Google Sheet API

Just like update, you will use the POST request as follows:

// Delete Data
func DeleteData(w http.ResponseWriter, r *http.Request) {
    var rowsToDelete []int

    err := json.NewDecoder(r.Body).Decode(&rowsToDelete)
    if err != nil {
        http.Error(w, err.Error(), http.StatusBadRequest)
        return
    }

    // Delete data from the Google Sheet using the Google Sheets API.
    var requests []*sheets.Request

    for _, row := range rowsToDelete {
        // Define a clear request for each row to be deleted.
        requests = append(requests, &sheets.Request{
            DeleteDimension: &sheets.DeleteDimensionRequest{
                Range: &sheets.DimensionRange{
                    SheetId: 0, // You might need to adjust the sheet ID.
                    Dimension: "ROWS",
                    StartIndex: int64(row - 1), // Google Sheets indexes start from 0.
                    EndIndex: int64(row),
                },
            },
        })
    }

    // Execute the batch update to delete rows.
    batchUpdateRequest := &sheets.BatchUpdateSpreadsheetRequest{Requests: requests}
    _, err = sheetsService.Spreadsheets.BatchUpdate(spreadsheetID, batchUpdateRequest).Context(r.Context()).Do()

    if err != nil {
        http.Error(w, err.Error(), http.StatusInternalServerError)
        return
    }

    w.WriteHeader(http.StatusOK)
}

Now add your HTTP handler methods:

http.HandleFunc("/read", ReadData)
http.HandleFunc("/create", CreateData)
http.HandleFunc("/update", UpdateData)
http.HandleFunc("/delete", DeleteData)

Once you rerun your app, use the http://localhost:8080/delete URL. In this example, your POST request contains an array of row numbers to delete as follows:

The sent request should be reflected in your Spreadsheet.

Conclusion

Along this guide, you learned:

• How to create and connect a Golang API with Google Sheets API
• How to create a Google Spreadsheet CMS and use Google Sheets as your database
• How to read Google Sheets Data using Golang
• Add new data cells using Golang to Google Sheets
• Use Golang to Update and Delete Google Sheets Data

Catch the whole code sample on GitHub

I hope you found this guide useful.

Further Reading

• Complete Guide to Pnpm and Docker with TypeScript & Monorepo
• Docker Slim With Minimal Reduce Docker Image Size Strategies
• TypeORM OneToMany and ManyToOne with Nest.js and PostgreSQL
• Docker - Connect Postgres dpage pgAdmin4 With Docker Compose