golang访问googlesheet写接口

如何访问google sheet、读接口详见 Golang访问Google Sheet

import ( "bytes" "context" "fmt" "golang.org/x/oauth2/google" "google.golang.org/api/option" "google.golang.org/api/sheets/v4" "runtime/debug" ) type UpdateGoogleSheetByRangeReq struct { Credentials []byte SpreadsheetId string SheetName string ExcelItemList interface{} } func UpdateGoogleSheetByRangeSrv(ctx context.Context, req *UpdateGoogleSheetByRangeReq) (wfmErr *wfmerror.WFMError) { defer func() { if p := recover(); p != nil { logger.LogErrorf("UpdateGoogleSheetByRangeReq panic=%v, stack=%v", p, string(debug.Stack())) wfmErr = constant.ErrCommonSystemUnknownErr.NewMessage(fmt.Sprintf("panic=%v", p)) } }() // 解析密钥文件 config, err := google.JWTConfigFromJSON(req.Credentials, sheets.SpreadsheetsScope) if err != nil { return constant.ErrCommonSystemUnknownErr.NewMessage(err.Error()) } // 创建 Google Sheets 服务客户端 client := config.Client(ctx) // 创建 Sheets 服务对象 sheetsService, err := sheets.NewService(ctx, option.WithHTTPClient(client)) if err != nil { return constant.ErrCommonSystemUnknownErr.NewMessage(err.Error()) } data, rErr := readXLSX(req.ExcelItemList) if rErr != nil { return rErr.Mark() } exists, sErr := sheetExists(sheetsService, req.SpreadsheetId, req.SheetName) if sErr != nil { return sErr.Mark() } //不存在就创建，存在清除 if exists { if cErr := clearSheet(sheetsService, req.SpreadsheetId, req.SheetName); cErr != nil { return cErr.Mark() } } else { if cErr := createSheet(sheetsService, req.SpreadsheetId, req.SheetName); cErr != nil { return cErr.Mark() } } // 使用 Sheets 服务对象进行操作 readRange := fmt.Sprintf("%s!A1", req.SheetName) // 通过 Sheets 服务对象获取指定的工作表 _, err = sheetsService.Spreadsheets.Values.Update(req.SpreadsheetId, readRange, &sheets.ValueRange{ Values: data, }).ValueInputOption("RAW").Do() if err != nil { return constant.ErrCommonSystemUnknownErr.NewMessage(err.Error()) } return nil } func readXLSX(ExcelItem interface{}) ([][]interface{}, *wfmerror.WFMError) { sheetName := "sheet1" //将ExcelItem转换为xlsx []byte fileData, _, gErr := excel.GenerateExcelBytes("excel.xlsx", []*dto.ExcelSheetTab{ { SheetName: sheetName, Data: ExcelItem, }, }) if gErr != nil { return nil, gErr.Mark() } f, err := excelize.OpenReader(bytes.NewReader(fileData)) if err != nil { return nil, constant.ErrCommonSystemUnknownErr.NewMessage(err.Error()) } rows, err := f.GetRows(sheetName) // 选择要读取的工作表 if err != nil { return nil, constant.ErrCommonSystemUnknownErr.NewMessage(err.Error()) } data := make([][]interface{}, len(rows)) for i, row := range rows { data[i] = make([]interface{}, len(row)) for j, cell := range row { data[i][j] = cell } } return data, nil } func sheetExists(srv *sheets.Service, spreadsheetId, sheetName string) (bool, *wfmerror.WFMError) { // 获取电子表格的结构 spreadsheet, err := srv.Spreadsheets.Get(spreadsheetId).Do() if err != nil { return false, constant.ErrCommonSystemUnknownErr.NewMessage(err.Error()) } // 检查工作表是否存在 for _, sheet := range spreadsheet.Sheets { if sheet.Properties.Title == sheetName { return true, nil } } return false, nil } func createSheet(srv *sheets.Service, spreadsheetId, sheetName string) *wfmerror.WFMError { // 创建新的工作表 requests := []*sheets.Request{ { AddSheet: &sheets.AddSheetRequest{ Properties: &sheets.SheetProperties{ Title: sheetName, }, }, }, } batchUpdateRequest := &sheets.BatchUpdateSpreadsheetRequest{ Requests: requests, } _, err := srv.Spreadsheets.BatchUpdate(spreadsheetId, batchUpdateRequest).Do() if err != nil { return constant.ErrCommonSystemUnknownErr.NewMessage(err.Error()) } return nil } func clearSheet(srv *sheets.Service, spreadsheetId, sheetName string) *wfmerror.WFMError { // 清空整个工作表的范围 rangeToClear := fmt.Sprintf("%s!A:Z", sheetName) // 假设工作表的范围是 A 到 Z 列 // 创建清空请求 clearValuesRequest := &sheets.ClearValuesRequest{} _, err := srv.Spreadsheets.Values.Clear(spreadsheetId, rangeToClear, clearValuesRequest).Do() if err != nil { return constant.ErrCommonSystemUnknownErr.NewMessage(err.Error()) } return nil }