/
sheets.go
228 lines (192 loc) · 6.26 KB
/
sheets.go
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
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
package sheets
import (
"encoding/json"
"fmt"
"io/ioutil"
"log"
"net/http"
"os"
"github.com/fomiller/scribe/config"
"golang.org/x/net/context"
"golang.org/x/oauth2"
"golang.org/x/oauth2/google"
"google.golang.org/api/sheets/v4"
)
var sheetsSrv *sheets.Service
type TempData struct {
FieldName interface{}
FieldValue interface{}
}
func init() {
b, err := ioutil.ReadFile(fmt.Sprintf("%v\\src\\github.com\\fomiller\\scribe\\sheets\\%v", config.GoPath, config.Scribe.Credentials.Sheets))
if err != nil {
log.Fatalf(`%v
Please navigate to https://console.cloud.google.com/apis/credentials, to create and download credentials for a 0Auth client ID.
Save your new credentials as 'credentials.json' inside the scribe/sheets folder.
Once credentials are saved to the correct location run 'go install' from the applications root directory.
to test your applicaton run 'scribe' in your terminal
`, err)
}
// If modifying these scopes, delete your previously saved token.json.
config, err := google.ConfigFromJSON(b, sheets.SpreadsheetsScope)
if err != nil {
log.Fatalf("Unable to parse client secret file to config: %v", err)
}
client := getClient(config)
sheetsSrv, err = sheets.New(client)
if err != nil {
log.Fatalf("Unable to retrieve Sheets client: %v", err)
}
}
// Retrieve a token, saves the token, then returns the generated client.
func getClient(cfig *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 := fmt.Sprintf("%v\\src\\github.com\\fomiller\\scribe\\sheets\\%v", config.GoPath, "token.json")
tok, err := tokenFromFile(tokFile)
if err != nil {
tok = getTokenFromWeb(cfig)
saveToken(tokFile, tok)
}
return cfig.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)
}
func RunSheets() {
// 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"
resp, err := sheetsSrv.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])
}
}
}
func GetSpreadsheetColumnNames(spreadsheetId string) []string {
// spreadsheetId := "16gT3TQ5BveYpaU56PZkoKfCoQNagb_fRHKd0NAxHmkg"
readRange := "1:1"
var columnNames []string
resp, err := sheetsSrv.Spreadsheets.Values.Get(spreadsheetId, readRange).Do()
if err != nil {
log.Fatalf("Unable to retrueve data from sheet: %v", err)
}
if len(resp.Values) == 0 {
fmt.Println("No data found.")
} else {
for _, row := range resp.Values {
// print each value from the row
for _, cell := range row {
// append cell value to column names
columnNames = append(columnNames, fmt.Sprint(cell))
}
}
}
return columnNames
}
func SheetsRanges() {
spreadsheetId := "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms"
readRange := "Class Data"
resp, err := sheetsSrv.Spreadsheets.Values.Get(spreadsheetId, readRange).Do()
if err != nil {
log.Fatal(err)
}
fmt.Println(len(resp.Values))
fmt.Printf("%v\n", resp.Values[1:])
}
func GetRowData(spreadsheetId string) [][]interface{} {
// spreadsheetId := "16gT3TQ5BveYpaU56PZkoKfCoQNagb_fRHKd0NAxHmkg"
// make a call to the spreadsheet to get sheets inside the spreadsheet
ss, err := sheetsSrv.Spreadsheets.Get(spreadsheetId).Do()
if err != nil {
log.Fatal(err)
}
// set the readRange equal to the first sheet in the spreadsheet
readRange := ss.Sheets[0].Properties.Title
resp, err := sheetsSrv.Spreadsheets.Values.Get(spreadsheetId, readRange).MajorDimension("ROWS").Do()
if err != nil {
log.Fatal(err)
}
return resp.Values
}
func FmtSpreadsheetData(fieldNames []string, rows [][]interface{}) [][]TempData {
newData := TempData{}
// create slice to hold all data for one field
spreadsheetData := [][]TempData{}
rowData := []TempData{}
// add field names
for _, fName := range fieldNames {
newData.FieldName = fName
rowData = append(rowData, newData)
}
// range over all rows
for _, row := range rows[1:] {
// print index and value of each item in slice
for i, v := range row {
rowData[i].FieldValue = v
}
// create new slice the same length of the data slice
appendData := make([]TempData, len(rowData))
// copy over the values for rowData to s
for i, _ := range rowData {
appendData[i] = rowData[i]
}
// append appendData slice to spreadsheetData
spreadsheetData = append(spreadsheetData, appendData)
}
// return the formated spreadsheet data
return spreadsheetData
}
func GetSheets() {
spreadsheetId := "16gT3TQ5BveYpaU56PZkoKfCoQNagb_fRHKd0NAxHmkg"
resp, err := sheetsSrv.Spreadsheets.Get(spreadsheetId).Do()
if err != nil {
log.Fatal(err)
}
for _, v := range resp.Sheets {
fmt.Printf("%v\n", v.Properties.Title)
}
}