How to format a cell with Google Sheets API v4 and Go?

user_162414 0
Viewed 823

I have to paste a date. For what I understood in the docs and SO is that I have to paste the days passed from 30 dec 1899 and format the cell as date.

Been the last hour looking for an example of formatting using the Go API client. Any example?

Thanks!

Edit: my code!

Google libs used:
"google.golang.org/api/option"
"google.golang.org/api/sheets/v4"

Function:

func googleAppend(bookID, sheet, tableCorner string, data []interface{}) {
    srv := connect(key)
    var vr sheets.ValueRange
    vr.Values = append(vr.Values, data)
    _, err := srv.Spreadsheets.Values.Append(bookID,
        sheet+"!"+tableCorner, &vr).ValueInputOption("RAW").Do()
    if err != nil {
        log.Fatalf("Unable to retrieve data from sheet. %v", err)
    }
}

Tested with:

func Test_googleAppend(t *testing.T) {
    sample := []interface{}{"swwewewew", 3, 47120} <-- for what I understand, the destination of 3434343 should be formated to DATE.
    googleAppend("XXX", "SHEET", "A1", sample)
}

Desired result: enter image description here

go google-sheets-api
1 Answers

I believe your goal as follows.

  • You want to set the number format of the cell at the column "C".
  • For example, you want to convert 47120 to 1/2/2029.
  • You want to achieve this using googleapis for Golang.
  • You have already been able to get and put values using Sheets API.

Modification points:

  • In order to change the cell format, it is required to use the method of batchUpdate in Sheets API. In your case, I would like to propose to change the number format using RepeatCellRequest in the batchUpdate method.

When this is reflected to your script, please modidfy as follows.

Modified script:

Before you run the script, please set sheetId.

func googleAppend(bookID, sheet, tableCorner string, data []interface{}) {
    srv := connect(key)
    var vr sheets.ValueRange
    vr.Values = append(vr.Values, data)
    _, err := srv.Spreadsheets.Values.Append(bookID,
        sheet+"!"+tableCorner, &vr).ValueInputOption("RAW").Do()
    if err != nil {
        log.Fatalf("Unable to retrieve data from sheet. %v", err)
    }

    // I added below script.
    sheetId := 12345678  // Please set the sheet ID which is not Spreadsheet ID. Please be careful this.

    repeatCellRequest := &sheets.RepeatCellRequest{
        Fields: "userEnteredFormat.numberFormat",
        Range: &sheets.GridRange{
            SheetId:          int64(sheetId),
            StartRowIndex:    0,
            StartColumnIndex: 2,
            EndColumnIndex:   3,
        },
        Cell: &sheets.CellData{
            UserEnteredFormat: &sheets.CellFormat{
                NumberFormat: &sheets.NumberFormat{
                    Pattern: "m/d/yyyy",
                    Type:    "DATE",
                },
            },
        },
    }
    requestBody := &sheets.BatchUpdateSpreadsheetRequest{
        Requests: []*sheets.Request{&sheets.Request{
            RepeatCell: repeatCellRequest,
        }},
    }
    resp, err := srv.Spreadsheets.BatchUpdate(bookID, requestBody).Do()
    if err != nil {
        log.Fatal(err)
    }
    fmt.Printf("%#v\n", resp)
}
  • In this modification, after the values were put to the Spreadsheet using your script, the cell format of the column "C" is changed using the RepeatCellRequest in the batchUpdate method.

Note:

  • In this case, when you run the script for changing the number format one time, you might not required to be run again. Because when the script is run, the number format of the column "C" is changed. But I'm not sure about your actual situation. So about this, please modify the script for your actual situation.

References:

user_71086530
Related