Up - API Reference - Home
We can use Excel to do bulk-updates for records. First, you download an Excel file that is prepared with record, definition, and field information. Use Excel to modify the data, then upload it to propagate the changes.
The download step can be found in excel/children. This is the upload step.
Requires JWT.
Upload a previously downloaded Excel sheet to update records in bulk.
For uploading, we do NOT post JSON, but multipart formdata.
If the uploaded file doesn't look like a previously Unicat-generated Excel file, we return Bad Request.
We analyze the uploaded file to see if record, definition, and field information is correct. If not, we bail out early. This will return an error, but we still include a report. The report will highlight the error, with an added comment to indicate what's wrong.
We return the report in the same format as the uploaded file, but with errors and warnings highlighted and commented. You can fix the errors in that file and re-upload it.
If initial analysis is fine, then we update all records, and create new ones (at the end) for rows that have data but no record gid. We also delete records that were removed from the Excel file, or that were set to 'deleted' style (strike-through). This is not a permanent delete. The record is added to the report with the 'deleted' style'. If a record has status 'deleted' in Unicat, but is in the Excel file without the 'deleted' style, it will be undeleted.
Validation occurs on each field before updating a record. We don't update fields that have a validation error, these are reported. We do update fields that yield validation warnings, and we include them in the reporting too. These errors or warnings are just for the field updates, the /records/excel/update call will return success.
We only have to supply the file, all necessary info is included in the file itself.
POST /api/p/<project gid>/records/excel/update
Authorization: Bearer <JWT>
Content-Type: multipart/form-data; charset=utf-8; boundary=__GENERATED_MULTIPART_BOUNDARY__
--__GENERATED_MULTIPART_BOUNDARY__
Content-Disposition: form-data; name="upload_file"; filename="cc-groupname-children.xlsx"
Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
<cc-groupname-children.xlsx file data>
--__GENERATED_MULTIPART_BOUNDARY__--
upload_file the file data for upload.
language return data for this (project) language.
languages return data for these (project) languages.
If you request language and/or languages, you get the records' title and fields for the combined set. If you request neither, you'll get all available languages.
Authorization: <JWT>
{
"success": true,
"result": {
"records": ["7f226a42-dbfe-42bc-ad75-e56c77b5c997", …],
"report.excel": "cc-update-report.….xlsx",
"report.validation": [
{
"cell": "E5",
"field": "2409a281-b786-4d4a-8bb7-aa7c4179695b",
"field_name": "price",
"language": "nl",
"result": "ERROR",
"keys": [
"invalid_input"
],
"message_key": "Invalid input for type '{type}'",
"message_replacements": {
"type": "decimal"
}
},
]
},
"data": {
"files": {
"cc-update-report.….xlsx": "UEsDBBQAAAAIAANznFQHQU1igQAAALEAAAAQAAA…"
},
"records": {
"7f226a42-dbfe-42bc-ad75-e56c77b5c997": {
"gid": "7f226a42-dbfe-42bc-ad75-e56c77b5c997",
…,
"fields": {
"nl": {
"net_volume__l": "174",
"name": "Caravell ijsconservator 174 liter",
"packing": "1",
"artnr": "CMS 225-945",
…
}
}
}
}
}
}
report.excel the key (filename) for the report.
records the updated and created records.
report.validation validation info for updated fields
Each validation result has a number of fields:
cell the Excel cell which triggered the validation
field the field's gid
field_name the field's name
language the field's language
result either SUCCESS, REPORT (warning), ERROR, or NA (not applicable)
keys fixed values for errors, e.g. invalid_input, min_items, or restrict_to_values
message_key a user-presentable message, with placeholders for replacements - these are between braces
message_replacements replacement values for placeholders in the message_key.
files File data, base-64 encoded. Unordered.
records Record info with hierarchy, definition, field values. Unordered.
For Excel files, the content-type is application/vnd.openxmlformats-officedocument.spreadsheetml.sheet. You can construct a dataurl by prepending data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64, to the base-64 encoded data.
400 Bad request - missing parameters or wrong type or value, or not a Unicat Excel worksheet
401 Unauthorized - missing or expired JWT
403 Forbidden - not a member of this project
1005 Excel worksheet invalid - record/definition/fields mismatch, duplicate records
HTTP/1.1 422 Unprocessable entity
{
"success": false,
"result": {
"code": 1005,
"message": "Excel worksheet invalid",
"info": {
"report.excel": "cc-update-report.….xlsx",
"report.validation": [
{
"result": "ERROR",
"keys": ["duplicate_record"],
"message_key": "Duplicate record gid '{record}'",
"message_replacements": {
"record": "109a2ffe-b0fa-48cf-8fe5-a7dbd0b4147b"
}
}
]
}
},
"data": {
"files": {
"cc-update-report.….xlsx": "UEsDBBQAAAAIAANznFQHQU1igQAAALEAAAAQAAA…"
},
}
}
The validation report for invalid worksheets are much the same as those for fields (see above), but without the field-specific properties.