Sheets API: Using the sheets pkg for Go

README.md

A simple main.go that creates a sheets service to append data to a spreadsheet.

Description

This main.go was created using the sheets package for Golang. For more information about the Google Sheets API, you can check out the official documentation.

Authentication

This application uses a service account to authenticate with the Google Sheets API because I found it easier to use. However, if you don't want to use a service account, you can use OAuth2.0 instead, but you will need to change the code to use the OAuth2.0 flow. You can find more information about this in the Google Sheets API quickstart.

In both cases, you will need to enable the Google Sheets API in your project (see the quickstart guide for more information) and share the spreadsheet with the service account email address or the email address of the account you want to use.

Concepts

A list of common terms used in the Sheets API such as spreadsheetId and sheetId can be found here.

Results

Here is an example of the expected result:

Just in case, the "INSERT_ROWS" option gives us the advantage of keeping the checkbox when appending new rows.

main.go

package main

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

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

const (
	// The A1 notation of a range to search for a logical table of data.
	// Values will be appended after the last row of the table.
	// https://developers.google.com/sheets/api/guides/concepts#cell
	range_ = "MySheet!A1"

	// How input data should be interpreted.
	// https://developers.google.com/sheets/api/reference/rest/v4/ValueInputOption
	valueInputOption = "RAW"

	// How input data should be inserted.
	// https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/append#InsertDataOption
	//
	// INSERT_ROWS is useful when you want to keep the format of the previous row.
	//
	// For instance:
	//
	// - if the previous row is bold, then the new rows appended will be bold as well.
	//
	// - if you manually add a cell with a checkbox, the next rows appended will have a checkbox as well.
	//
	// INSERT_ROWS option allows to have manual columns with checkbox, dates, and so on, and they will be
	// immediately available for the next rows appended.
	insertDataOption = "INSERT_ROWS"
)

var (
	// The ID of the spreadsheet to update.
	spreadsheetID = os.Getenv("SPREADSHEET_ID")

	// service account credentials
	credentials = os.Getenv("CREDENTIALS")
)

func main() {
	ctx := context.Background()
	svc, err := sheets.NewService(ctx, option.WithCredentialsFile(credentials))
	if err != nil {
		log.Fatalf("Unable to create sheets service: %v", err)
	}

	// data to append
	// https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values#ValueRange
	valueRange := &sheets.ValueRange{
		//Range:          range_, // it can be omitted when appending values (see documentation for more info)
		//MajorDimension: "ROWS", // "ROWS" is the default when appending values
		Values: [][]interface{}{
			//{
			//	"Name",
			//	"Age",
			//	"State",
			//	"Phone",
			//	"Email",
			//	"Date",
			//},
			{
				"John",
				30,
				"New York",
				123456789,
				"[email protected]",
				time.Now().UTC(),
			},
			{
				"Jane",
				25,
				"California",
				987654321,
				"[email protected]",
				time.Now().UTC(),
			},
		},
	}

	// Append the above values to the spreadsheet
	// https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/append
	resp, err := svc.Spreadsheets.Values.Append(spreadsheetID, range_, valueRange).
		ValueInputOption(valueInputOption).
		InsertDataOption(insertDataOption).
		Context(ctx).Do()
	if err != nil {
		log.Fatalf("Unable to append data to sheet: %v", err)
	}

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