Search code examples
gogoogle-sheetsgoogle-apigoogle-api-go-client

Google sheets API - download data with no formatting

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())
    }
}
Solution
    • You want to retrieve $123,456 as 123456 from Google Spreadsheet.
    • $123,456 is shown by the cell format. It's actually the number.
    • You want to achieve this using gopkg.in/Iwark/spreadsheet.v2 with golang.
    • You have already been able to get and put values for Google Spreadsheet using the service account with Sheets API.

    If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

    Modification points:

    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.

    Modified script:

    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.

    1. service.go

    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))"

    2. sheet.go

    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"
)

    3. cell_data.go

    Modify the line 8 as follows.

    From: 
    // UserEnteredFormat *CellFormat `json:"userEnteredFormat"`
    To: 
    UserEnteredFormat struct {
    NumberValue float64 `json:"numberValue"`
} `json:"userEnteredFormat"`

    Result:

    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.

    Other pattern:

    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.

    Sample script 1:

    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())
    }
}

    Sample script 2:

    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)
}

    References:

    If I misunderstood your question and this was not the direction you want, I apologize.