Golang - how to connect to google spreadsheet?

Credentials

You need to set up Google Credentials for your app. This part is a tad annoying. Mostly due to the UX of the Google Cloud Plateform. Let’s hope this will once be fixed.

Since it may be changing, I suggest you check the docs on Google. At the time of writing of this short tutorial, we need to:

  1. Go to Google Cloud Console
  2. Create Project if needed.
  3. Go to API & Services
  4. Enable Google Sheets API
  5. OAuth Consent Screen - Set it up
  6. Add yourself to the test users
  7. Create Credentials “OAuth 2.0 Client IDs” (TV & Limited Input)
  8. Save the json file and store it into your project

The first time around you will have to go to a the web interface, it will tell you the app is unverified, proceed anyway (which should be working if you have added yourself to the Test users authorized for the OAuth Consent Screen)

The code

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

// 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)
}

Connecting with the Google spreadsheet

Once we have the authentification working, we can start connecting to the google spreadsheet.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20


b, err := ioutil.ReadFile("credentials.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.ConfigFromJSON(b,
	"https://www.googleapis.com/auth/spreadsheets.readonly")
	if err != nil {
		log.Fatalf("Unable to parse client secret file to config: %v",
	}
client := getClient(config)

ctx := context.Background()
srv, err := sheets.NewService(ctx, option.WithHTTPClient(client))

if err != nil {
	log.Fatalf("Unable to retrieve Sheets client: %v", err)
}

Once we have our connection to the google spreadsheeting, then we can start working with the data

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

// Prints the names and majors of students in a sample spreadsheet:
// https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
spreadsheetId := "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms"
readRange := "Class Data!A2:E" // Sheet Name!RANGE:RANGE
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])
	}
}

That’s it.