Skip to main content
Call this API to update cell information, including cell values, background colors, hyperlinks, font sizes, horizontal alignment, vertical alignment, and more.

​
Request

​
Basic information

FieldValue
HTTP URLhttps://api.dingtalk.io/v1.0/doc/workbooks/{workbookId}/sheets/{sheetId}/ranges/{rangeAddress}
HTTP MethodPUT
Supported app typesappType-Internal app　appType-Third-party enterprise app
Permissions requiredpermission-Document.Workbook.Write-DingTalk Spreadsheet write permission

​
Request headers

NameTypeRequiredDescription
x-acs-dingtalk-access-tokenStringYesThe access credential for calling this API. Obtain it as follows: - For an internal app, call the Obtain the access token of an internal app API. - For a third-party enterprise app, call the Obtain the access token of an authorized organization for a third-party app API.

​
Path parameters

NameTypeRequiredDescription
workbookIdStringYesThe spreadsheet file ID. The nodeId(dentryUuid) returned by the Knowledge Base API is the spreadsheet workbookId. Obtain it by calling the Get a node API or the Create a Knowledge Base document API.
sheetIdStringYesThe worksheet ID or name. - For an internal app, call the Get all worksheets API to obtain the id or name value. - For a third-party enterprise app, call the Create a worksheet API to obtain the id and name.
rangeAddressStringYesThe range address.

​
Query parameters

NameTypeRequiredDescription
operatorIdStringYesThe unionId of the operator. Call the Query user details API to obtain it. If the operator has no permission, the API returns the error The operator has no permission.

​
Request body

NameTypeRequiredDescription
horizontalAlignmentsArray of ArrayNoHorizontal alignment. Valid values: left (align left), center (center align), right (align right), general (default alignment). Pass the values as a two-dimensional array based on the range address. See the following examples: The two-dimensional array contains one element per row in the range, and each element contains one value per column in the range. Example 1: When the range address is A1:B3 (a three-row, two-column table), the value format is: { "horizontalAlignments": [ ["left", "general"], ["center", "general"], ["right", "general"] ] } Example 2: When the range address is A1:C3 (a three-row, three-column table), the value format is: { "horizontalAlignments": [ ["left", "left", "general"], ["center", "center", "general"], ["right", "right", "general"] ] }
numberFormatStringNoThe number format.---------NameNumber formatExampleGeneral”General”Text”@“Number”#,##0”1,234Number (with decimals)”#,##0.00”1,234.56Percentage”0%“12%Percentage (with decimals)“0.00%“12.34%Scientific notation”0.00E+00”1.01E+03RMB”¥#,##0”¥1,234RMB (with decimals)¥#,##0.00”¥1,234.56USD”$#,##0”$1,234USD (with decimals)”$#,##0.00”$1,234.56Date”yyyy/m/d”2022/1/1Date (Chinese)“yyyy年m月d日”2022年1月1日Date (Chinese, year and month)“yyyy年m月”2022年1月Time”hh:mm:ss”00:00:00Date and time”yyyy/m/d hh:mm:ss”2022/1/1 00:00:00
wordWrapStringNoThe text wrap mode. Valid values: - overflow: overflow - clip: clip - autoWrap: wrap text
valuesArray of ArrayNoThe cell values. Pass the values as a two-dimensional array based on the range address. See the following examples: The two-dimensional array contains one element per row in the range, and each element contains one value per column in the range. Example 1: When the range address is A1:B3 (a three-row, two-column table), the value format is: { "values": [ ["1", "2"], ["3", "4"], ["5", "6"] ] } Example 2: When the range address is A1:C3 (a three-row, three-column table), the value format is: { "values": [ ["1","2","3"], ["4","5","6"], ["7","8","9"] ] }
complexValuesArray of ArrayNoThe cell values. Pass the values as a two-dimensional array based on the range address. Use this field to fill the corresponding cells with rich text content. See the appendix that follows for details.
backgroundColorsArray of ArrayNoThe background colors, specified as hexadecimal values. Pass the values as a two-dimensional array based on the range address. See the following examples: The two-dimensional array contains one element per row in the range, and each element contains one value per column in the range. Example 1: When the range address is A1:B3 (a three-row, two-column table), the value format is: { "backgroundColors": [ ["#ff0000", "#00ff00"], ["#f0f0f0", "#0000ff"], ["#f0f0f0", "#0000ff"] ] } Example 2: When the range address is A1:C3 (a three-row, three-column table), the value format is: { "backgroundColors": [ ["#ff0000","#ff0000","#ff0000"], ["#ff0000","#ff0000","#ff0000"], ["#ff0000","#ff0000","#ff0000"] ] }
fontColorsArray of ArrayNoThe font colors, specified as hexadecimal values. Pass the values as a two-dimensional array based on the range address.
fontSizesArray of ArrayNoThe font sizes. Pass the values as a two-dimensional array based on the range address. See the following examples: The two-dimensional array contains one element per row in the range, and each element contains one value per column in the range. Example 1: When the range address is A1:B3 (a three-row, two-column table), the value format is: { "fontSizes": [ [14, 14], [15, 15], [10, 10] ] } Example 2: When the range address is A1:C3 (a three-row, three-column table), the value format is: { "fontSizes": [ [14, 14, 14], [15, 15, 15], [10, 10, 10] ] }
fontWeightsArray of ArrayNoThe font weights. Valid values: - bold: bold - normal: normal
hyperlinksArray of ArrayNoThe hyperlinks. Pass the values as a two-dimensional array based on the range address. See the following examples: The two-dimensional array contains one element per row in the range, and each element contains one value per column in the range. Example 1: When the range address is A1:B3 (a three-row, two-column table), the value format is: { "hyperlinks": [ [ { "type": "path", "link": "https://www.dingtalk.io", "text": "test" }, { "type": "sheet", "link": "Sheet2", "text": "test" } ], [ { "type": "range", "link": "Sheet2!A4", "text": "test" }, { "type": "path", "link": "https://www.dingtalk.io", "text": "test" } ], [ { "type": "range", "link": "Sheet2!A4", "text": "2" }, { "type": "sheet", "link": "Sheet2", "text": "test" } ] ] } Example 2: When the range address is A1:C3 (a three-row, three-column table), the value format is: { "hyperlinks": [ [ { "type": "path", "link": "https://www.dingtalk.io", "text": "test" }, { "type": "sheet", "link": "Sheet2", "text": "test" },{ "type": "path", "link": "https://www.dingtalk.io", "text": "test" } ], [ { "type": "range", "link": "Sheet2!A4", "text": "test" }, { "type": "path", "link": "https://www.dingtalk.io", "text": "test" },{ "type": "path", "link": "https://www.dingtalk.io", "text": "test" } ], [ { "type": "range", "link": "Sheet2!A4", "text": "2" }, { "type": "sheet", "link": "Sheet2", "text": "test" },{ "type": "path", "link": "https://www.dingtalk.io", "text": "test" } ] ] }
verticalAlignmentsArray of ArrayNoVertical alignment. Valid values: top (align top), middle (center vertically), bottom (align bottom). Pass the values as a two-dimensional array based on the range address. See the following examples: The two-dimensional array contains one element per row in the range, and each element contains one value per column in the range. Example 1: When the range address is A1:B3 (a three-row, two-column table), the value format is: { "verticalAlignments": [ ["top", "top"], ["middle", "middle"], ["bottom", "bottom"] ] } Example 2: When the range address is A1:C3 (a three-row, three-column table), the value format is: { "verticalAlignments": [ ["top", "top", "top"], ["middle", "middle", "middle"], ["bottom", "bottom", "bottom"] ] }

​
Request example

HTTP 
PUT /v1.0/doc/workbooks/e54Lqxxxxx/sheets/Sheet1/ranges/A1:B1?operatorId=ppgAQuxxxxx HTTP/1.1
Host:api.dingtalk.io
x-acs-dingtalk-access-token:f86e0bxxx
Content-Type:application/json

{
  "horizontalAlignments" : [ [ "general" ] ],
  "numberFormat" : "@",
  "wordWrap" : "autoWrap",
  "values" : [ [ "text" ] ],
  "complexValues" : [ [ "text" ] ],
  "backgroundColors" : [ [ "#ff0000" ] ],
  "fontColors" : [ [ "#000000" ] ],
  "fontSizes" : [ [ 14 ] ],
  "fontWeights" : [ [ "bold" ] ],
  "hyperlinks" : [ [ {
    "type" : "path",
    "link" : "https://www.dingtalk.io",
    "text" : "DingTalk"
  } ] ],
  "verticalAlignments" : [ [ "middle" ] ]
}

​
Response

​
Response body

NameTypeDescription
a1NotationStringThe address of the updated range.

​
Response body example

HTTP/1.1 200 OK
Content-Type:application/json

{
  "a1Notation" : "A1"
}

​
Error codes

If the API call fails, look up the error message in the Global error codes document for a solution.
HttpCodeError codeError messageDescription
400invalidRequest.inputArgs.invalid%sThe request parameters are invalid. Check the error message.
400invalidRequest.inputArgs.workbookIdIllegalThe workbookId is illegal.The workbookId is invalid.
400invalidRequest.resource.notWorkbook%sUnsupported document type. Check the workbookId.
403forbidden.accessDeniedThe operator has no permission.The current user does not have permission to perform this action.
403forbidden.acrossOrg%sThe request is invalid. Verify that the document being accessed belongs to the organization specified by the access token.
403forbidden.operationIllegal%sThe requested action is invalid. Check the error message.
403forbidden.document.sizeOverLimitThe document size is over limit and the server is unable to complete your request. Retry is unlikely to work unless the document size is decreased.The spreadsheet content is too large. Try reducing the content.
404invalidRequest.resource.notFound%sThe request failed. The requested resource cannot be found.
500serviceBusyThe server is busy and unable to complete your request. Please try again later.The service is busy. Try again later.
500internalErrorThe server encountered an internal error and was unable to complete your request. Please try again later.The server encountered an internal error. Try again later.