Skip to content

Instantly share code, notes, and snippets.

@dickyaryag6

dickyaryag6/gsheet_api.go

Created August 21, 2021 08:23
Show Gist options
  • Save dickyaryag6/8fad65e878139eb04c7b289d72c72cdc to your computer and use it in GitHub Desktop.
Save dickyaryag6/8fad65e878139eb04c7b289d72c72cdc to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gsheet_api.go
package main
import (
"context"
"encoding/json"
"fmt"
"log"
"google.golang.org/api/option"
"google.golang.org/api/sheets/v4"
)
type Key struct {
Type string `json:"type"`
ProjectID string `json:"project_id"`
PrivateKeyID string `json:"private_key_id"`
PrivateKey string `json:"private_key"`
ClientEmail string `json:"client_email"`
ClientID string `json:"client_id"`
AuthURI string `json:"auth_uri"`
TokenURI string `json:"token_uri"`
AuthProvider string `json:"auth_provider_x509_cert_url"`
Client string `json:"client_x509_cert_url"`
}
var (
spreadsheetId string = "17_V0gRP4z4VE5dHT2u5m44QBCIAUD_ov2i"
sheetKey Key = Key{
Type: "service_account",
ProjectID: "sheet-projec",
PrivateKeyID: "xxxxx",
PrivateKey: "-----BEGIN PRIVATE KEY-----\xxxxx=\n-----END PRIVATE KEY-----\n",
ClientEmail: "sheet-service-account@xxxxx.iam.gserviceaccount.com",
ClientID: "xxxxx",
AuthURI: "https://accounts.google.com/o/oauth2/auth",
TokenURI: "https://oauth2.googleapis.com/token",
AuthProvider: "https://www.googleapis.com/oauth2/v1/certs",
Client: "https://www.googleapis.com/robot/v1/metadata/x509/sheet-service-account%40xxxxx.iam.gserviceaccount.com",
}
)
func getSheetConfig() *sheets.Service {
credential, err := json.Marshal(sheetKey)
if err != nil {
log.Fatalf("Failed to get key: %v", err)
}
srv, err := sheets.NewService(context.Background(), option.WithCredentialsJSON(credential))
if err != nil {
log.Fatalf("Unable to retrieve Sheets client: %v", err)
}
return srv
}
func AppendRow(srv *sheets.Service) {
values := &sheets.ValueRange{
Values: [][]interface{}{{
"Peter",
"United States",
"Software Engineer",
23,
"23/07/1998",
"baru",
}},
}
_, err := srv.Spreadsheets.Values.Append(spreadsheetId, "Sheet1!A:F", values).ValueInputOption("USER_ENTERED").Do()
if err != nil {
log.Fatalf("Unable to insert data to sheet: %v", err)
}
}
func Update(srv *sheets.Service) {
values := &sheets.ValueRange{
Values: [][]interface{}{{
"Japan",
"Software Engineer Lead",
}},
}
_, err := srv.Spreadsheets.Values.Update(spreadsheetId, "Sheet1!B2:C2", values).ValueInputOption("USER_ENTERED").Do()
if err != nil {
log.Fatalf("Unable to insert data to sheet: %v", err)
}
}
func ClearRow(srv *sheets.Service) {
cvr := &sheets.ClearValuesRequest{}
_, err := srv.Spreadsheets.Values.Clear(spreadsheetId, "Sheet1!A7:E7", cvr).Do()
if err != nil {
log.Fatalf("Unable to clear data from sheet: %v", err)
}
}
func ClearCell(srv *sheets.Service) {
cvr := &sheets.ClearValuesRequest{}
_, err := srv.Spreadsheets.Values.Clear(spreadsheetId, "Sheet1!B2", cvr).Do()
if err != nil {
log.Fatalf("Unable to clear data from sheet: %v", err)
}
}
func ClearColumn(srv *sheets.Service) {
cvr := sheets.ClearValuesRequest{}
_, err := srv.Spreadsheets.Values.Clear(spreadsheetId, "Sheet1!C2:C", &cvr).Do()
if err != nil {
log.Fatalf("Unable to clear data from sheet: %v", err)
}
}
func GetCellValue(srv *sheets.Service) {
values, err := srv.Spreadsheets.Values.Get(spreadsheetId, "Sheet1!A2:E7").Do()
if err != nil {
log.Fatalf("Unable to Get data from sheet: %v", err)
}
for _, value := range values.Values {
fmt.Println(value)
}
}
func main() {
srv := getSheetConfig()
// // APPEND ROW
AppendRow(srv)
// GET VALUES
GetCellValue(srv)
// // UPDATE CELL
Update(srv)
// // CLEAR ROW
ClearRow(srv)
// // CLEAR CELL VALUE
ClearCell(srv)
// // CLEAR COLUMN
ClearColumn(srv)
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment