Column style ignored using spreadsheets.batchUpdate with AppendCellsRequest

2
votes

As a developer i use this endpoint: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/batchUpdate

And request: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#AppendCellsRequest to insert new row with values.

I have spreadsheet with some column format styles: background-color/font-weight/text-wrap/data-validation.

For example:

The problem, when i use requests like this:

curl --request POST \
 'https://sheets.googleapis.com/v4/spreadsheets/1SoDx8YRyiKF9vKfa_2w0xc7DTNIlQoLC6hBq1SCJEJY:batchUpdate?key=[YOUR_API_KEY]' \
  --header 'Authorization: Bearer [YOUR_ACCESS_TOKEN]' \
  --header 'Accept: application/json' \
  --header 'Content-Type: application/json' \
  --data '{"requests":[{"appendCells":{"sheetId":0,"fields":"*","rows":[{"values":[{"userEnteredValue":{"stringValue":"TEST STRING VALUE long"}},{"userEnteredValue":{"stringValue":"TEST TEXT LONG"}}]}]}}]}' \
  --compressed

I see that text-wrap and data-validation styles are ignored:

enter image description here

Key point here that i use only: 'userEnteredValue' field to provide values.

According to the doc: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/cells#CellData i can use 'userEnteredFormat'/'dataValidation', but "When writing, the new format will be merged with the existing format." I find it quite complicated to obtain and post those style formats once again.

My question - is there any way to just post CellData using AppendCellsRequest as simple as possible and preserve existing column styles?

2
google-sheets-api
I apologize for my poor English skill. Unfortunately, I cannot understand about I see that text-wrap and data-validation styles are ignored: from your image. Can I ask you about the detail of it? - Tanaike
Hi! Updated my description. As you can see validation 'warning' is missed for the first column, and text-wrapping is ignored for both columns - aismagilov
Thank you for replying and adding the information. From your additional information, I proposed a modification point as an answer. Could you please confirm it? If I misunderstood your issue and that was not the result you want, I apologize. - Tanaike

2 Answers

3
votes
  • You want to put the values using the AppendCellsRequest of batchUpdate method in Sheets API.
  • You want to keep the text style and data validation rules of the cells when the AppendCellsRequest is run.
  • You want to put only the text values.
  • You have already been able to get and put values for Google Spreadsheet using Sheets API.

Modification point:

In your case, you use "fields":"*" while you set only the property of userEnteredValue. In this case, the properties except for userEnteredValue are also set. I think that this is the reason of your issue.

In order to avoid this issue, please use userEnteredValue to fields.

Modified request body:

When your request body is modified, it becomes as follows.

--data '{"requests":[{"appendCells":{"sheetId":0,"fields":"*","rows":[{"values":[{"userEnteredValue":{"stringValue":"TEST STRING VALUE long"}},{"userEnteredValue":{"stringValue":"TEST TEXT LONG"}}]}]}}]}'

--data '{"requests":[{"appendCells":{"sheetId":0,"fields":"userEnteredValue","rows":[{"values":[{"userEnteredValue":{"stringValue":"TEST STRING VALUE long"}},{"userEnteredValue":{"stringValue":"TEST TEXT LONG"}}]}]}}]}'
  • "fields":"*" was modified to "fields":"userEnteredValue".

References:

0
votes

Tanaike's answer is correct, but I find the following strangeness.

My CellData looks like (Kotlin):

        fun cellDataDouble(value: Double, doublePattern: String = "#.#"): CellData {
            return CellData().also { cd ->
                cd.userEnteredFormat = CellFormat().setNumberFormat(NumberFormat().also { nf ->
                    nf.type = "Number"
                    nf.pattern = doublePattern
                })
                cd.userEnteredValue = ExtendedValue().setNumberValue(value)
            }
        }

so it seemed logical to me to set fields to "userEnteredValue,userEnteredFormat", but if you do that other formatting in a column is erased. The behaviour is the same as fields="*". This is shown in the test case screenshot below: enter image description here

Line 20 was appended with the two fields specified, and line 21 with only userEnteredValue, which shows the the userEnteredFormat was not applied. The docs suggest formatting should be merged, but it is clear that the font and bold formatting is removed by just specifying the number format.