Using Go, when fetching sheet data, the data is arriving with its applied cell formatting
i.e. "$123,456"
while I need the original 123456
.
is there something in the api that can remove formatting? like formatting: false
code:
package main
import (
"log"
"golang.org/x/net/context"
"golang.org/x/oauth2/google"
"gopkg.in/Iwark/spreadsheet.v2"
)
func main() {
service := authenticate()
spreadsheet, err := service.FetchSpreadsheet(spreadsheetID)
checkError(err)
sheet, err := spreadsheet.SheetByIndex(1)
checkError(err)
for _, row := range sheet.Rows {
var csvRow []string
for _, cell := range row {
csvRow = append(csvRow, cell.Value)
}
log.Println(csvRow)
}
}
// function to authenticate on Google
func authenticate() *spreadsheet.Service {
data, err := ioutil.ReadFile("secret.json")
checkError(err)
conf, err := google.JWTConfigFromJSON(data, spreadsheet.Scope)
checkError(err)
client := conf.Client(context.TODO())
service := spreadsheet.NewServiceWithClient(client)
return service
}
func checkError(err error) {
if err != nil {
panic(err.Error())
}
}
$123,456
as 123456
from Google Spreadsheet.$123,456
is shown by the cell format. It's actually the number.gopkg.in/Iwark/spreadsheet.v2
with golang.If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.
gopkg.in/Iwark/spreadsheet.v2
, I noticed that the values are retrieved by the method of spreadsheets.get in Sheets API.spreadsheetId,properties.title,sheets(properties,data.rowData.values(formattedValue,note))
was used as the fields. It seems that the fields is constant.The reason of your issue is that the values are retrieved with formattedValue
. In your case, the values are required to be retrieved with userEnteredValue
.
When you want to achieve your goal using the library of gopkg.in/Iwark/spreadsheet.v2
, in order to reflect above to the library, it is required to modify the script of library.
Please modify the files of gopkg.in/Iwark/spreadsheet.v2
as follows. Of course, please backup the original files in order to back to the original library.
Modify the line 116 as follows.
From:fields := "spreadsheetId,properties.title,sheets(properties,data.rowData.values(formattedValue,note))"
To:
fields := "spreadsheetId,properties.title,sheets(properties,data.rowData.values(formattedValue,note,userEnteredValue))"
Modify the line 52 as follows.
From:Value: cellData.FormattedValue,
To:
Value: strconv.FormatFloat(cellData.UserEnteredValue.NumberValue, 'f', 4, 64),
And add "strconv"
to import
section like below.
import (
"encoding/json"
"strings"
"strconv"
)
Modify the line 8 as follows.
From:// UserEnteredFormat *CellFormat `json:"userEnteredFormat"`
To:
UserEnteredFormat struct {
NumberValue float64 `json:"numberValue"`
} `json:"userEnteredFormat"`
In this case, your script is not required to be modified. After above modification, when you run your script, you can see [123456.0000]
at the console. As an important point, it seems that this library uses the values as the string type. In this modification, I used this. But if you want to use it as other type, please modify the library.
As the other pattern for achieving your goal, how about using google-api-go-client? About this, you can see it at Go Quickstart. When google-api-go-client is used, the sample script becomes as follows. In this case, as a test case, the method of spreadsheets.get was used.
In this sample script, authenticate()
and checkError()
in your script are used by modifying.
package main
import (
"fmt"
"io/ioutil"
"net/http"
"golang.org/x/net/context"
"golang.org/x/oauth2/google"
"google.golang.org/api/sheets/v4"
)
func main() {
c := authenticate()
sheetsService, err := sheets.New(c)
checkError(err)
spreadsheetId := "###" // Please set the Spreadsheet ID.
ranges := []string{"Sheet1"} // Please set the sheet name.
resp, err := sheetsService.Spreadsheets.Get(spreadsheetId).Ranges(ranges...).Fields("sheets.data.rowData.values.userEnteredValue").Do()
checkError(err)
for _, row := range resp.Sheets[0].Data[0].RowData {
for _, col := range row.Values {
fmt.Println(col.UserEnteredValue)
}
}
}
func authenticate() *http.Client {
data, err := ioutil.ReadFile("serviceAccount_20190511.json")
checkError(err)
conf, err := google.JWTConfigFromJSON(data, sheets.SpreadsheetsScope)
checkError(err)
client := conf.Client(context.TODO())
return client
}
func checkError(err error) {
if err != nil {
panic(err.Error())
}
}
When spreadsheets.values.get is used, the script of main()
is as follows.
func main() {
c := authenticate()
sheetsService, err := sheets.New(c)
checkError(err)
spreadsheetId := "###" // Please set the Spreadsheet ID.
sheetName := "Sheet1" // Please set the sheet name.
resp, err := sheetsService.Spreadsheets.Values.Get(spreadsheetId, sheetName).ValueRenderOption("UNFORMATTED_VALUE").Do()
checkError(err)
fmt.Println(resp.Values)
}
UNFORMATTED_VALUE
is used for retrieving the values without the cell format. This has already been answered by alberto vielmaIf I misunderstood your question and this was not the direction you want, I apologize.