Joseph Jude

How to read a Google Sheet with Golang

2018.06.27 / code / golang /

Step by step guide to access a public Google Sheet with the Sheet API

Spreadsheets are a key tool in business. You can use it for project management, marketing, accounting, task management, inventory management and so on. You can use a spreadsheet as a hub and automate many of the tasks around it.

Though Excel is a fantastic desktop-based spreadsheet, Google Sheet (G-Sheet) is definitely the popular web-based spreadsheet.

In this tutorial, you will learn how to read a Google sheet with Golang.

Here are some of the related Golang posts in this blog:

The steps to reading G-Sheet in Golang are:

  1. Create a new project in Google Developer Console
  2. Enable access to Google Sheet API
  3. Get a client secret file
  4. Create a Google sheet (We will use an existing one)
  5. Access Google Sheet with Golang code

Create a new project in Google Developer Console

Go to GCP Console. You should see a screen like this.

GCP Console

Every time I login into GCP, there is a minor change in the dashboard and layout. So take these screenshots only as a guide.

Click on ‘Create Project’ button to create a new project. You should see ‘New Project’ screen.

Create New GCP Project

Give a name for your project and click ‘Create’. You should see the dashboard again. Be sure to select the newly created project from the ‘Select a project’ dropdown in the top navigation bar. You should see the project dashboard.

GCP Project Dashboard

Enable access to Google Sheet API

From the project dashboard screen, you can enable any Google API for the project. We will enable the Google Sheet API. Click on ‘View All’ button. It will list all the Google APIs. Scroll through to find the Google Sheet API. Click on it and enable the Google sheet API.

Google Sheet API Screen

Get Client Secret File

In this tutorial, we are going to assume few things:

For this, we will create a service account and download a client secret file. This file will contain the secret keys to access Google services. This file is like your password. Don’t check-in this file into your code repository.

To do this, click on ‘credentials’ menu on the left side.

Project Credentials Page

Select ‘Service Accounts’. Now you can create a service account and download the client file.

Creating service accounts

As soon as you click, ‘Create’ it will download the credentials file. It should have the following format:

{
  "type": "service_account",
  "project_id": "",
  "private_key_id": "",
  "private_key": "",
  "client_email": "",
  "client_id": "",
  "auth_uri": "",
  "token_uri": "",
  "auth_provider_x509_cert_url": "",
  "client_x509_cert_url": ""
}

Store this file as secret.json

Golang Code

You have everything to get coding. Let us look at the code.

You’ll follow this flow:

Initialize using dep init. Then create this main.go.

func checkError(err error) {
	if err != nil {
		panic(err.Error())
	}
}

func main() {
	data, err := ioutil.ReadFile("secret.json")
	checkError(err)
	conf, err := google.JWTConfigFromJSON(data, sheets.SpreadsheetsScope)
	checkError(err)

	client := conf.Client(context.TODO())
	srv, err := sheets.New(client)
	checkError(err)

	spreadsheetID := "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms"
	readRange := "Class Data!A2:E"
	resp, err := srv.Spreadsheets.Values.Get(spreadsheetID, readRange).Do()
	checkError(err)

	if len(resp.Values) == 0 {
		fmt.Println("No data found.")
	} else {
		fmt.Println("Name, Major:")
		for _, row := range resp.Values {
			fmt.Printf("%s, %s\n", row[0], row[4])
		}
	}
}

That’s it. If you execute it, it will print the two columns.


Share this post on

Twitter | | |
Sign up for my weekly newsletter

I will send blog updates to this email. You can unsubscribe at any time using the link in those emails.