Null-Input (2D array)
null input inside two-dimensional array (for values, number-format, formula) is ignored in the update API. No update will take place when null is sent.
Only some parts of the Range Number Format are set while retaining the existing Number Format on the remaining part (by passing nulls).
range.values = [["Eurasia", "29.96", "0.25", "15-Feb" ]];
range.numberFormat = [[null, null, null, "m/d/yyyy;@"]];
null input for a property
null is not a valid single input for the entire property. For example, the following is not valid as the entire values cannot be set to null or ignored.
The following is not valid either as null is not a valid color value.
range.format.fill.color = null;
Representation of formatting properties that consists of non-uniform values would result in the return of a null value in the response.
An Excel Range can consist of one of more cells. In cases where the individual cells contained in the Range specified don't have uniform formatting values, the range level representation will be undefined.
"size" : null,
"color" : null,
Blank Input and Output
Blank values in update requests are treated as instruction to clear or reset the respective property. Blank value is represented by two double quotation marks with no space in-between. ""
For values, the range value is cleared out. This is the same as clearing the contents in the application.
For numberFormat, the number format is set to General.
For formula and formulaLocale, the formula values are cleared.
For read operations, expect to receive blank values if the contents of the cells are blanks. If the cell contains no data or value, then the API returns a blank value.
range.values = [["", "some", "data", "in", "other", "cells", ""]];
range.formula = [["", "", "=Rand()"]];
Unbounded Range (Read)
Unbounded range address contains only column or row identifiers and unspecified row identifier or column identifiers (respectively), such as:
C:C, A:F, A:XFD (contains unspecified rows)
2:2, 1:4, 1:1048546 (contains unspecified columns)
When the API makes a request to retrieve an unbounded Range (e.g., getRange('C:C'), the response returned contains null for cell level properties such as values, text, numberFormat, formula, etc.. Other Range properties such as address, cellCount, etc. will reflect the unbounded range.
Unbounded Range (Write)
Setting cell level properties (such as values, numberFormat, etc.) on unbounded Range is not allowed as the input request might be too large to handle.
The following is not a valid update request because the requested range is unbounded. This will return an error.
var range = context.workbook.worksheets.getActiveWorksheet().getRange("A:B");
range.values = 'Due Date';
Large Range implies a Range whose size is too large for a single API call.
Many factors such as number of cells, values, numberFormat, formulas, etc. contained in the range can make the response so large that it becomes unsuitable for API interaction.
The API makes a best attempt to return or write to the requested data.
The large size involved might result in an error.
To avoid such a condition, using read or write with smaller range sizes
Single Input Copy
To support updating a range with the same values or number-format or applying same formula across a range, the following convention is used in the set API. In Excel, this behavior is similar to inputting values or formulas to a range in the CTRL+Enter mode.
The API will look for a single cell value and, if the target range dimension doesn't match the input range dimension, it will apply the update to the entire range in the CTRL+Enter model with the value or formula provided in the request.
© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions Limited TopPrevNext