Bulk AddRows not returning information on failed items

dmillerw

Per the documentation, a POST call to /sheets/{id}/rows with 'allowPartialSuccess=true' is supposed to return an array of failed items containing information on why that particular item failed, and the array index. I'm not seeing this output though, rather just an array of standard error objects with no useful information.

Am I missing something here?

When sending this POST request (column 5280637475835780 is set to only allow values of 'A')

[
{
"cells": [
{
"columnId": 777037848465284,
"value": "0"
},
{
"columnId": 5280637475835780,
"value": "A"
}
],
"toBottom": true
},
{
"cells": [
{
"columnId": 777037848465284,
"value": "1"
},
{
"columnId": 5280637475835780,
"value": "B"
}
],
"toBottom": true
},
{
"cells": [
{
"columnId": 777037848465284,
"value": "2"
},
{
"columnId": 5280637475835780,
"value": "A"
}
],
"toBottom": true
},
{
"cells": [
{
"columnId": 777037848465284,
"value": "3"
},
{
"columnId": 5280637475835780,
"value": "B"
}
],
"toBottom": true
},
{
"cells": [
{
"columnId": 777037848465284,
"value": "4"
},
{
"columnId": 5280637475835780,
"value": "A"
}
],
"toBottom": true
},
{
"cells": [
{
"columnId": 777037848465284,
"value": "5"
},
{
"columnId": 5280637475835780,
"value": "B"
}
],
"toBottom": true
},
{
"cells": [
{
"columnId": 777037848465284,
"value": "6"
},
{
"columnId": 5280637475835780,
"value": "A"
}
],
"toBottom": true
},
{
"cells": [
{
"columnId": 777037848465284,
"value": "7"
},
{
"columnId": 5280637475835780,
"value": "B"
}
],
"toBottom": true
},
{
"cells": [
{
"columnId": 777037848465284,
"value": "8"
},
{
"columnId": 5280637475835780,
"value": "A"
}
],
"toBottom": true
},
{
"cells": [
{
"columnId": 777037848465284,
"value": "9"
},
{
"columnId": 5280637475835780,
"value": "B"
}
],
"toBottom": true
},
{
"cells": [
{
"columnId": 777037848465284,
"value": "10"
},
{
"columnId": 5280637475835780,
"value": "A"
}
],
"toBottom": true
},
{
"cells": [
{
"columnId": 777037848465284,
"value": "11"
},
{
"columnId": 5280637475835780,
"value": "B"
}
],
"toBottom": true
},
{
"cells": [
{
"columnId": 777037848465284,
"value": "12"
},
{
"columnId": 5280637475835780,
"value": "A"
}
],
"toBottom": true
},
{
"cells": [
{
"columnId": 777037848465284,
"value": "13"
},
{
"columnId": 5280637475835780,
"value": "B"
}
],
"toBottom": true
},
{
"cells": [
{
"columnId": 777037848465284,
"value": "14"
},
{
"columnId": 5280637475835780,
"value": "A"
}
],
"toBottom": true
},
{
"cells": [
{
"columnId": 777037848465284,
"value": "15"
},
{
"columnId": 5280637475835780,
"value": "B"
}
],
"toBottom": true
},
{
"cells": [
{
"columnId": 777037848465284,
"value": "16"
},
{
"columnId": 5280637475835780,
"value": "A"
}
],
"toBottom": true
},
{
"cells": [
{
"columnId": 777037848465284,
"value": "17"
},
{
"columnId": 5280637475835780,
"value": "B"
}
],
"toBottom": true
},
{
"cells": [
{
"columnId": 777037848465284,
"value": "18"
},
{
"columnId": 5280637475835780,
"value": "A"
}
],
"toBottom": true
},
{
"cells": [
{
"columnId": 777037848465284,
"value": "19"
},
{
"columnId": 5280637475835780,
"value": "B"
}
],
"toBottom": true
},
{
"cells": [
{
"columnId": 777037848465284,
"value": "20"
},
{
"columnId": 5280637475835780,
"value": "A"
}
],
"toBottom": true
},
{
"cells": [
{
"columnId": 777037848465284,
"value": "21"
},
{
"columnId": 5280637475835780,
"value": "B"
}
],
"toBottom": true
},
{
"cells": [
{
"columnId": 777037848465284,
"value": "22"
},
{
"columnId": 5280637475835780,
"value": "A"
}
],
"toBottom": true
},
{
"cells": [
{
"columnId": 777037848465284,
"value": "23"
},
{
"columnId": 5280637475835780,
"value": "B"
}
],
"toBottom": true
},
{
"cells": [
{
"columnId": 777037848465284,
"value": "24"
},
{
"columnId": 5280637475835780,
"value": "A"
}
],
"toBottom": true
}
]

I"m just getting this output, but I would expect to see more information on what failed and where it originated, per the docs

{
"message": "PARTIAL_SUCCESS",
"resultCode": 3,
"version": 61,
"failedItems": [
{
"error": {
"refId": "aeaa74c6-f9c5-46e5-8d8a-2c93c0018e41",
"errorCode": 5536,
"message": "The value for cell in column {0}, did not conform to the strict requirements for type {1}."
}
},
{
"error": {
"refId": "d2eb6a88-9bee-4ddb-81b8-d3265bc4df7b",
"errorCode": 5536,
"message": "The value for cell in column {0}, did not conform to the strict requirements for type {1}."
}
},
{
"error": {
"refId": "368cc5c7-4beb-46de-8928-8420ce0c1b74",
"errorCode": 5536,
"message": "The value for cell in column {0}, did not conform to the strict requirements for type {1}."
}
},
{
"error": {
"refId": "1e4e79eb-0074-47bc-b2c8-801cdb20974c",
"errorCode": 5536,
"message": "The value for cell in column {0}, did not conform to the strict requirements for type {1}."
}
},
{
"error": {
"refId": "174546d7-a1b5-472e-8d7e-edd7cdec1790",
"errorCode": 5536,
"message": "The value for cell in column {0}, did not conform to the strict requirements for type {1}."
}
},
{
"error": {
"refId": "2e853ce4-a238-4bb4-b83f-32a171dfb70e",
"errorCode": 5536,
"message": "The value for cell in column {0}, did not conform to the strict requirements for type {1}."
}
},
{
"error": {
"refId": "a04cc6a7-abda-4926-ad66-774fe2faa39b",
"errorCode": 5536,
"message": "The value for cell in column {0}, did not conform to the strict requirements for type {1}."
}
},
{
"error": {
"refId": "7d58fbdc-b892-48ed-9704-bb982e3bbe57",
"errorCode": 5536,
"message": "The value for cell in column {0}, did not conform to the strict requirements for type {1}."
}
},
{
"error": {
"refId": "a84e2960-0ae0-4707-9539-5f377bfe89f6",
"errorCode": 5536,
"message": "The value for cell in column {0}, did not conform to the strict requirements for type {1}."
}
},
{
"error": {
"refId": "7b1465b2-afa1-486a-90fc-cc86fe6ce9c8",
"errorCode": 5536,
"message": "The value for cell in column {0}, did not conform to the strict requirements for type {1}."
}
},
{
"error": {
"refId": "2134177b-ed53-456d-a677-88217dd807c9",
"errorCode": 5536,
"message": "The value for cell in column {0}, did not conform to the strict requirements for type {1}."
}
},
{
"error": {
"refId": "ea6fdc7d-9de5-438c-bb8e-df7c84a58b72",
"errorCode": 5536,
"message": "The value for cell in column {0}, did not conform to the strict requirements for type {1}."
}
}
],
"result": [
{
"id": 1139640289464196,
"sheetId": 3710457038720900,
"rowNumber": 53,
"siblingId": 7457445645389700,
"expanded": true,
"locked": false,
"lockedForUser": false,
"createdAt": "2025-03-13T07:48:44Z",
"modifiedAt": "2025-03-13T07:48:44Z",
"cells": [
{
"columnId": 777037848465284,
"displayValue": "0",
"value": "0"
},
{
"columnId": 5280637475835780,
"displayValue": "A",
"value": "A"
},
{
"columnId": 3028837662150532
},
{
"columnId": 7532437289521028
},
{
"columnId": 6406537382678404
}
]
},
{
"id": 3391440103149444,
"sheetId": 3710457038720900,
"rowNumber": 54,
"siblingId": 1139640289464196,
"expanded": true,
"locked": false,
"lockedForUser": false,
"createdAt": "2025-03-13T07:48:44Z",
"modifiedAt": "2025-03-13T07:48:44Z",
"cells": [
{
"columnId": 777037848465284,
"displayValue": "2",
"value": "2"
},
{
"columnId": 5280637475835780,
"displayValue": "A",
"value": "A"
},
{
"columnId": 3028837662150532
},
{
"columnId": 7532437289521028
},
{
"columnId": 6406537382678404
}
]
},
{
"id": 576690336042884,
"sheetId": 3710457038720900,
"rowNumber": 55,
"siblingId": 3391440103149444,
"expanded": true,
"locked": false,
"lockedForUser": false,
"createdAt": "2025-03-13T07:48:44Z",
"modifiedAt": "2025-03-13T07:48:44Z",
"cells": [
{
"columnId": 777037848465284,
"displayValue": "4",
"value": "4"
},
{
"columnId": 5280637475835780,
"displayValue": "A",
"value": "A"
},
{
"columnId": 3028837662150532
},
{
"columnId": 7532437289521028
},
{
"columnId": 6406537382678404
}
]
},
{
"id": 2828490149728132,
"sheetId": 3710457038720900,
"rowNumber": 56,
"siblingId": 576690336042884,
"expanded": true,
"locked": false,
"lockedForUser": false,
"createdAt": "2025-03-13T07:48:44Z",
"modifiedAt": "2025-03-13T07:48:44Z",
"cells": [
{
"columnId": 777037848465284,
"displayValue": "6",
"value": "6"
},
{
"columnId": 5280637475835780,
"displayValue": "A",
"value": "A"
},
{
"columnId": 3028837662150532
},
{
"columnId": 7532437289521028
},
{
"columnId": 6406537382678404
}
]
},
{
"id": 1702590242885508,
"sheetId": 3710457038720900,
"rowNumber": 57,
"siblingId": 2828490149728132,
"expanded": true,
"locked": false,
"lockedForUser": false,
"createdAt": "2025-03-13T07:48:44Z",
"modifiedAt": "2025-03-13T07:48:44Z",
"cells": [
{
"columnId": 777037848465284,
"displayValue": "8",
"value": "8"
},
{
"columnId": 5280637475835780,
"displayValue": "A",
"value": "A"
},
{
"columnId": 3028837662150532
},
{
"columnId": 7532437289521028
},
{
"columnId": 6406537382678404
}
]
},
{
"id": 3954390056570756,
"sheetId": 3710457038720900,
"rowNumber": 58,
"siblingId": 1702590242885508,
"expanded": true,
"locked": false,
"lockedForUser": false,
"createdAt": "2025-03-13T07:48:44Z",
"modifiedAt": "2025-03-13T07:48:44Z",
"cells": [
{
"columnId": 777037848465284,
"displayValue": "10",
"value": "10"
},
{
"columnId": 5280637475835780,
"displayValue": "A",
"value": "A"
},
{
"columnId": 3028837662150532
},
{
"columnId": 7532437289521028
},
{
"columnId": 6406537382678404
}
]
},
{
"id": 295215359332228,
"sheetId": 3710457038720900,
"rowNumber": 59,
"siblingId": 3954390056570756,
"expanded": true,
"locked": false,
"lockedForUser": false,
"createdAt": "2025-03-13T07:48:44Z",
"modifiedAt": "2025-03-13T07:48:44Z",
"cells": [
{
"columnId": 777037848465284,
"displayValue": "12",
"value": "12"
},
{
"columnId": 5280637475835780,
"displayValue": "A",
"value": "A"
},
{
"columnId": 3028837662150532
},
{
"columnId": 7532437289521028
},
{
"columnId": 6406537382678404
}
]
},
{
"id": 2547015173017476,
"sheetId": 3710457038720900,
"rowNumber": 60,
"siblingId": 295215359332228,
"expanded": true,
"locked": false,
"lockedForUser": false,
"createdAt": "2025-03-13T07:48:44Z",
"modifiedAt": "2025-03-13T07:48:44Z",
"cells": [
{
"columnId": 777037848465284,
"displayValue": "14",
"value": "14"
},
{
"columnId": 5280637475835780,
"displayValue": "A",
"value": "A"
},
{
"columnId": 3028837662150532
},
{
"columnId": 7532437289521028
},
{
"columnId": 6406537382678404
}
]
},
{
"id": 1421115266174852,
"sheetId": 3710457038720900,
"rowNumber": 61,
"siblingId": 2547015173017476,
"expanded": true,
"locked": false,
"lockedForUser": false,
"createdAt": "2025-03-13T07:48:44Z",
"modifiedAt": "2025-03-13T07:48:44Z",
"cells": [
{
"columnId": 777037848465284,
"displayValue": "16",
"value": "16"
},
{
"columnId": 5280637475835780,
"displayValue": "A",
"value": "A"
},
{
"columnId": 3028837662150532
},
{
"columnId": 7532437289521028
},
{
"columnId": 6406537382678404
}
]
},
{
"id": 3672915079860100,
"sheetId": 3710457038720900,
"rowNumber": 62,
"siblingId": 1421115266174852,
"expanded": true,
"locked": false,
"lockedForUser": false,
"createdAt": "2025-03-13T07:48:44Z",
"modifiedAt": "2025-03-13T07:48:44Z",
"cells": [
{
"columnId": 777037848465284,
"displayValue": "18",
"value": "18"
},
{
"columnId": 5280637475835780,
"displayValue": "A",
"value": "A"
},
{
"columnId": 3028837662150532
},
{
"columnId": 7532437289521028
},
{
"columnId": 6406537382678404
}
]
},
{
"id": 858165312753540,
"sheetId": 3710457038720900,
"rowNumber": 63,
"siblingId": 3672915079860100,
"expanded": true,
"locked": false,
"lockedForUser": false,
"createdAt": "2025-03-13T07:48:44Z",
"modifiedAt": "2025-03-13T07:48:44Z",
"cells": [
{
"columnId": 777037848465284,
"displayValue": "20",
"value": "20"
},
{
"columnId": 5280637475835780,
"displayValue": "A",
"value": "A"
},
{
"columnId": 3028837662150532
},
{
"columnId": 7532437289521028
},
{
"columnId": 6406537382678404
}
]
},
{
"id": 3109965126438788,
"sheetId": 3710457038720900,
"rowNumber": 64,
"siblingId": 858165312753540,
"expanded": true,
"locked": false,
"lockedForUser": false,
"createdAt": "2025-03-13T07:48:44Z",
"modifiedAt": "2025-03-13T07:48:44Z",
"cells": [
{
"columnId": 777037848465284,
"displayValue": "22",
"value": "22"
},
{
"columnId": 5280637475835780,
"displayValue": "A",
"value": "A"
},
{
"columnId": 3028837662150532
},
{
"columnId": 7532437289521028
},
{
"columnId": 6406537382678404
}
]
},
{
"id": 1984065219596164,
"sheetId": 3710457038720900,
"rowNumber": 65,
"siblingId": 3109965126438788,
"expanded": true,
"locked": false,
"lockedForUser": false,
"createdAt": "2025-03-13T07:48:44Z",
"modifiedAt": "2025-03-13T07:48:44Z",
"cells": [
{
"columnId": 777037848465284,
"displayValue": "24",
"value": "24"
},
{
"columnId": 5280637475835780,
"displayValue": "A",
"value": "A"
},
{
"columnId": 3028837662150532
},
{
"columnId": 7532437289521028
},
{
"columnId": 6406537382678404
}
]
}
]
}

dmillerw

Interestingly, this does work as expected when updating rows… just not when adding

Georgie

Hi @dmillerw,

On reviewing the Add Rows section of the API documentation, it looks to me like the response you’ve received is what’s expected when allowPartialSuccess=true. That is, the response is showing an array of all the failed items.

As per the API documentation, as seen in the screenshot below, I’d try setting allowPartialSuccess to false, since this will mean that the response will describe the error in more detail.

Does that work?

Georgie

dmillerw

Hi Georgie,

Greatly appreciate the response.

The response I'm getting when ADDING rows in bulk is technicially correct, as I do receive an array of what went wrong. However it's missing the 'index' field stating WHICH row failed to be added, making it more or less useless for my purposes, since I don't know which rows I sent failed.

Please see the images below comparing the results of the 'Add' and 'Update' responses when allowPartialSuccess is true. Obviously I don't expect to get the rowId back since it doesn't exist, but I would still expect the 'index' field.

Add Rows

Update Rows

The docs imply that 'index' will be returned regardless, as only 'rowId' is marked as nullable