Why Google spreadsheets API stops applying formatting after entering certain amount of data?

Question

I have an app, which manages Google drive structure and updates existing Google sheets. On all of those Sheets documents, I came across strange behaviour. Once I add some amount of data (at around 80-100 entered rows), the spreadsheet stops applying some formatting over data.

App is C# / .NET Standard (4.7.2), using Google.Apis.Sheets.v4 nuget and GoogleSheetsApi-V4 as endpoint.

Application creates and send batch update which contains both data and formatting (styles). Batch update is specific request as it is transactional - if any of the sub-requests will not be applied (e.g. request is not parsed via Google's server), whole request will not be applied (meaning all sub-requests get dropped).

I'm aware of request-count limits and I'm not hitting those. This behaviour occurrs on all tested files (around 50 in total) - first 80-100 rows are formatted without issues, the rest has problems.

Reply from the Google's API (after batch update request) is empty and doesn't report any error.

For the formatting I'm using batchRequest with several requests. I'm including them in the order as they are send and applied:

• UpdateCells with CellData.UserEnteredValue
• MergeCells
• UpdateCells with CellData.UserEnteredFormat
• UpdateBorders
• RepeatCell
• ...

---

Does Google spreadsheets API have some limits for formatting? What might be the cause that formatting is lost at this part? Is there something I should be aware about that might be limiting this behaviour?

---

Screenshot of bad formatting at certain amount of data (e.g. WORD_WRAP, TEXT_ALIGNMENT is not set, but cells are still "bordered" and background color is set for the columns):

Answer 1

I believe your goal as follows.

• From the formatting, which is not applied, is "last request" in the batch update. in your comment.
  • You want to reflect WRAP of wrapStrategy to the column 3 of the rows from 3 to 96 using the method of batchUpdate of Sheets API.

Modification points:

• When I saw your request body, I thought that range might be not correct. For example, if you want to reflect WRAP of wrapStrategy as follows.
  • From 3 to 96 for rows.
  • Column 3.

Please modify range as follows.

Modified request body:

Please modify repeatCell of the last request in your request body as follows, and test it again.

{
  "repeatCell": {
    "cell": {
      "userEnteredFormat": {
        "horizontalAlignment": "CENTER",
        "verticalAlignment": "MIDDLE",
        "wrapStrategy": "WRAP"
      }
    },
    "fields": "userEnteredFormat.wrapStrategy,userEnteredFormat.verticalAlignment,userEnteredFormat.horizontalAlignment",
    "range": {
      "startRowIndex": 3,
      "endRowIndex": 96,
      "startColumnIndex": 3,
      "endColumnIndex": 4,
      "sheetId": ### <--- Please set sheet ID.
    }
  }
}

Note:

• By the way, about 2nd request of mergeCells in your request body, in this case, I think that the situation is not changed. Because the single cell is used. Please be careful this.

References:

• Method: spreadsheets.batchUpdate
• GridRange