Skip to content

Instantly share code, notes, and snippets.

@kariyayo
Last active August 27, 2020 16:33
Show Gist options
  • Save kariyayo/b2ba535f74c7bcb723a2ee46585814d8 to your computer and use it in GitHub Desktop.
Save kariyayo/b2ba535f74c7bcb723a2ee46585814d8 to your computer and use it in GitHub Desktop.
Google Sheets API v4 と サービスアカウントと Go でスプレッドシートを操作する ref: http://qiita.com/bati11/items/a4cd922149dac07981bc
package main
import (
"errors"
"fmt"
"io/ioutil"
"log"
"net/http"
"golang.org/x/oauth2"
"golang.org/x/oauth2/google"
"google.golang.org/api/sheets/v4"
)
func httpClient(credentialFilePath string) (*http.Client, error) {
data, err := ioutil.ReadFile(credentialFilePath)
if err != nil {
return nil, err
}
conf, err := google.JWTConfigFromJSON(data, "https://www.googleapis.com/auth/spreadsheets")
if err != nil {
return nil, err
}
return conf.Client(oauth2.NoContext), nil
}
func sheetId(s *sheets.Spreadsheet, sheetName string) (int64, error) {
for _, sheet := range s.Sheets {
if sheet.Properties.Title == sheetName {
return sheet.Properties.SheetId, nil
}
}
return 0, errors.New(sheetName + " is not exists.")
}
func insertRow(sheetService *sheets.Service, spreadsheetId string, sheetId int64) (*sheets.BatchUpdateSpreadsheetResponse, error) {
req := sheets.Request{
InsertDimension: &sheets.InsertDimensionRequest{
InheritFromBefore: false,
Range: &sheets.DimensionRange{
Dimension: "ROWS",
StartIndex: 1,
EndIndex: 3,
SheetId: sheetId,
},
},
}
insertRowReq := sheets.BatchUpdateSpreadsheetRequest{
Requests: []*sheets.Request{&req},
}
return sheetService.Spreadsheets.BatchUpdate(spreadsheetId, &insertRowReq).Do()
}
func main() {
spreadsheetId := "スプレッドシートのURLから抽出したID"
credentialFilePath := "秘密鍵のJSONファイルのパス"
sheetName := "シート1"
client, err := httpClient(credentialFilePath)
if err != nil {
log.Fatal(err)
}
sheetService, err := sheets.New(client)
if err != nil {
log.Fatalf("Unable to retrieve Sheets Client %v", err)
}
spreadsheet, err := sheetService.Spreadsheets.Get(spreadsheetId).Do()
if err != nil {
log.Fatalf("Unable to get Spreadsheets. %v", err)
}
sheetId, err := sheetId(spreadsheet, sheetName)
if err != nil {
log.Fatal(err)
}
_, err = insertRow(sheetService, spreadsheetId, sheetId)
if err != nil {
log.Fatalf("Unable to insert row. %v", err)
}
valueRange := &sheets.ValueRange{
MajorDimension: "ROWS",
Values: [][]interface{}{
[]interface{}{"hoge", 1},
[]interface{}{"fuga", 2},
},
}
_, err = sheetService.Spreadsheets.Values.Update(spreadsheetId, sheetName+"!A2:B3", valueRange).ValueInputOption("USER_ENTERED").Do()
if err != nil {
log.Fatalf("Unable to write value. %v", err)
}
fmt.Printf("success!\n")
}
@losnuyh
Copy link

losnuyh commented Aug 27, 2020

👍

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment