Last active
August 27, 2020 16:33
-
-
Save kariyayo/b2ba535f74c7bcb723a2ee46585814d8 to your computer and use it in GitHub Desktop.
Google Sheets API v4 と サービスアカウントと Go でスプレッドシートを操作する ref: http://qiita.com/bati11/items/a4cd922149dac07981bc
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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") | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
👍