Update a cell range

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

Request

Basic information

Field	Value
HTTP URL	`https://api.dingtalk.io/v1.0/doc/workbooks/{workbookId}/sheets/{sheetId}/ranges/{rangeAddress}`
HTTP Method	PUT
Supported app types	appType-Internal app　appType-Third-party enterprise app
Permissions required	permission-Document.Workbook.Write-DingTalk Spreadsheet write permission

Request headers

Name	Type	Required	Description
x-acs-dingtalk-access-token	String	Yes	The 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

Name	Type	Required	Description
workbookId	String	Yes	The 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.
sheetId	String	Yes	The 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`.
rangeAddress	String	Yes	The range address.

Query parameters

Name	Type	Required	Description
operatorId	String	Yes	The 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

Name	Type	Required	Description
horizontalAlignments	Array of Array	No	Horizontal 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"] ] }`
numberFormat	String	No	The number format.	---	---	---	Name	Number format	Example	General	”General”	Text	”@“	Number	”#,##0”	1,234	Number (with decimals)	”#,##0.00”	1,234.56	Percentage	”0%“	12%	Percentage (with decimals)	“0.00%“	12.34%	Scientific notation	”0.00E+00”	1.01E+03	RMB	”¥#,##0”	¥1,234	RMB (with decimals)	¥#,##0.00”	¥1,234.56	USD	”$#,##0”	$1,234	USD (with decimals)	”$#,##0.00”	$1,234.56	Date	”yyyy/m/d”	2022/1/1	Date (Chinese)	“yyyy年m月d日”	2022年1月1日	Date (Chinese, year and month)	“yyyy年m月”	2022年1月	Time	”hh:mm:ss”	00:00:00	Date and time	”yyyy/m/d hh:mm:ss”	2022/1/1 00:00:00
wordWrap	String	No	The text wrap mode. Valid values: - overflow: overflow - clip: clip - autoWrap: wrap text
values	Array of Array	No	The 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"] ] }`
complexValues	Array of Array	No	The 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.
backgroundColors	Array of Array	No	The 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"] ] }`
fontColors	Array of Array	No	The font colors, specified as hexadecimal values. Pass the values as a two-dimensional array based on the range address.
fontSizes	Array of Array	No	The 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] ] }`
fontWeights	Array of Array	No	The font weights. Valid values: - bold: bold - normal: normal
hyperlinks	Array of Array	No	The 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" } ] ] }
verticalAlignments	Array of Array	No	Vertical 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

Name	Type	Description
a1Notation	String	The 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.

HttpCode	Error code	Error message	Description
400	invalidRequest.inputArgs.invalid	%s	The request parameters are invalid. Check the error message.
400	invalidRequest.inputArgs.workbookIdIllegal	The workbookId is illegal.	The workbookId is invalid.
400	invalidRequest.resource.notWorkbook	%s	Unsupported document type. Check the workbookId.
403	forbidden.accessDenied	The operator has no permission.	The current user does not have permission to perform this action.
403	forbidden.acrossOrg	%s	The request is invalid. Verify that the document being accessed belongs to the organization specified by the access token.
403	forbidden.operationIllegal	%s	The requested action is invalid. Check the error message.
403	forbidden.document.sizeOverLimit	The 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.
404	invalidRequest.resource.notFound	%s	The request failed. The requested resource cannot be found.
500	serviceBusy	The server is busy and unable to complete your request. Please try again later.	The service is busy. Try again later.
500	internalError	The 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.

API calling guide

Authentication & authorization

Contacts

Calendar

Conferencing

AI Table

Docs & files

IM

Request

Basic information

Request headers

Path parameters

Query parameters

Request body

Request example

Response

Response body

Response body example

Error codes

​Request

​Basic information

​Request headers

​Path parameters

​Query parameters

​Request body

​Request example

​Response

​Response body

​Response body example

​Error codes

Request

Basic information

Request headers

Path parameters

Query parameters

Request body

Request example

Response

Response body

Response body example

Error codes