BatchUpdate

Gists

This is a sample script for directly using the request body of the string JSON for Google APIs with googleapis of golang.

At googleapis for golang, when Google API is used, it is required to create the request body like this sample script. I have several contacts for creating about such request body. I thought that such script might be a bit difficult for users. I thought that when the string JSON object is directly used for this, it might be useful. So I would like to introduce about this. When this was useful for your situation, I’m glad.

Gists

This is a sample script for setting the number format of cells on Google Spreadsheet using batchUpdate in Sheets API with golang. In this case, googleapis for golang is used. The script of the authorization can be seen at the official document.

Sample script

In this script, the number format of the column “A” is changed to yyyy-mm-dd hh:mm:ss. And, please include https://www.googleapis.com/auth/spreadsheets to the scopes.

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: 0,
        EndColumnIndex:   1,
    },
    Cell: &sheets.CellData{
        UserEnteredFormat: &sheets.CellFormat{
            NumberFormat: &sheets.NumberFormat{
                Pattern: "yyyy-mm-dd hh:mm:ss",
                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)

References

• Method: spreadsheets.batchUpdate
• RepeatCellRequest
• How to format a cell with Google Sheets API v4 and Go?
  • I answered above sample script at this thread.

Gists

In the case appending values to cell by inserting rows, when sheets.spreadsheets.values.append is used, the values are appended to the next empty row of the last row. If you want to append values to between cells with values by inserting row, you can achieve it using sheets.spreadsheets.batchUpdate.

When you use this, please use your access token.

Endpoint :

POST https://sheets.googleapis.com/v4/spreadsheets/### spreadsheet ID ###:batchUpdate

Request body :

In this request body, it appends the data of “sample1, sample2, sample3” to “A1:A3” of the sheetId of “1234567890”. Before appends the data, it supposes that there are some values at “A1:A3”.

Gists

Flow :

In my sample script, the script was made using the Quickstart. The flow to use this sample script is as follows.

1. For Go Quickstart, please do Step 1 and Step 2.
2. Please put client_secret.json to the same directory with my sample script.
3. Copy and paste my sample script, and create it as new script file.
4. Run the script.
5. When Go to the following link in your browser then type the authorization code: is shown on your terminal, please copy the URL and paste to your browser. And then, please authorize and get code.
6. Put the code to the terminal.
7. When Done. is displayed, it means that the update of spreadsheet is done.

Request body :

For Spreadsheets.Values.BatchUpdate, BatchUpdateValuesRequest is required as one of parameters. In this case, the range, values and so on that you want to update are included in BatchUpdateValuesRequest. The detail information of this BatchUpdateValuesRequest can be seen at godoc. When it sees BatchUpdateValuesRequest, Data []*ValueRange can be seen. Here, please be carefull that Data is []*ValueRange. Also ValueRange can be seen at godoc. You can see MajorDimension, Range and Values in ValueRange.