In the method loadData of SheetAPI, it allows user add caluclate condition to the sheet. .
Here comes the code example
SHEET_API.loadData(SHEET_API_HD, {
fileName: 'Condition Json File',
sheets: [{
name: 'First tab',
color: 'orange',
id: 1
},{
name: 'Second tab',
id: 2
}],
floatings: [{
sheet:1,
name:"condition1",
ftype:"cdt",
json: "{"name":"boolstyle","rng":[{"span":[1,2,2,4,4],"type":1}],"opt":{"type":"top","base":20,"style":{"cbgc":"rgb(248,105,107)","ccolor":"rgb(150,0,0)"}},"id":"condition-4"}"
}],
cells: [
{sheet: 1, row: 2, col: 2, json: {data:100}},
{sheet: 1, row: 2, col: 3, json: {data:120}},
{sheet: 1, row: 2, col: 4, json: {data:220}},
{sheet: 1, row: 3, col: 2, json: {data:210}},
{sheet: 1, row: 3, col: 3, json: {data:350}},
{sheet: 1, row: 3, col: 4, json: {data:200}},
{sheet: 1, row: 4, col: 2, json: {data:150}},
{sheet: 1, row: 4, col: 3, json: {data:90}},
{sheet: 1, row: 4, col: 4, json: {data:350}}
]
});
The above code will insert a range of cells from B2:D4 by adding calculate condition with top 20%.
For condition, loadData json object includes at least 4 names: fileName, sheets, floatings and cells.
| Name | Property | Example | Description |
|---|---|---|---|
| File name | fileName | fileName: 'Example Sheet' | Define the file name for this sheet file. |
| Sheet object | sheets | sheets: [{},{}] | Set defined sheet tabs information for this file. |
| Floating object | floatings | floatings: [{},{}] | Set defined floatings information for this file. |
| Cell object | cells | cells: [{},{}] | Set defined cell information for each tab. |
You can also call setCondition API and use the following code to update the condition in the existing sheet.
SHEET_API.setCondition(SHEET_API_HD, [[1, 25, 3, 27, 5]], 'boolstyle', {
type:"max",
base: 10,
style:{"cbgc":"rgb(248,105,107)","ccolor":"rgb(150,0,0)"}
});
To list top 10% value, the following json object need to be added into floatings. You can change base value to get different top percentage number.
{
sheet:1, // the sheet id of added condition
name:"condition1", // name should be unique
ftype:"cdt", // define condition type
json: "{\"name\":\"boolstyle\",\"rng\":[{\"span\":[1,2,2,4,4],\"type\":1}],\"opt\":{\"type\":\"top\",\"base\":10,\"style\":{\"cbgc\":\"rgb(248,105,107)\",\"ccolor\":\"rgb(150,0,0)\"}},\"id\":\"12345670\"}"
}
For each json object inside condition floating, it is a string with the following format.
name // boolstyle
rng
span // [sheetId, x1, y1, x2, y2] which define sheetId and a range of cells need to be added condition
type // 1
opt
type // top
base // percentage number
style // referred to the decorated color.
cbgc // cell background color if condition meet
ccolor // cell font color if condition meet
id // unique in this sheet file
To list top 10 value, the following json object need to be added into floatings. You can change base to get different top number.
{
sheet:1, // the sheet id of added condition
name:"condition1", // name should be unique
ftype:"cdt", // define condition type
json: "{\"name\":\"boolstyle\",\"rng\":[{\"span\":[1,2,2,4,4],\"type\":1}],\"opt\":{\"type\":\"max\",\"base\":10,\"style\":{\"cbgc\":\"rgb(248,105,107)\",\"ccolor\":\"rgb(150,0,0)\"}},\"id\":\"12345670\"}"
}
For each json object inside condition floating, it is a string with the following format.
name // boolstyle
rng
span // [sheetId, x1, y1, x2, y2] which define sheetId and a range of cells need to be added condition
type // 1
opt
type // max
base // number - for example: 5 - this means top 5 items.
style // referred to the decorated color.
cbgc // cell background color if condition meet
ccolor // cell font color if condition meet
id // unique in this sheet file
To list bottom 10 value, the following json object need to be added into floatings. You can change base to get different top number.
{
sheet:1, // the sheet id of added condition
name:"condition1", // name should be unique
ftype:"cdt", // define condition type
json: "{\"name\":\"boolstyle\",\"rng\":[{\"span\":[1,2,2,4,4],\"type\":1}],\"opt\":{\"type\":\"min\",\"base\":10,\"style\":{\"cbgc\":\"rgb(248,105,107)\",\"ccolor\":\"rgb(150,0,0)\"}},\"id\":\"12345670\"}"
}
For each json object inside condition floating, it is a string with the following format.
name // boolstyle
rng
span // [sheetId, x1, y1, x2, y2] which define sheetId and a range of cells need to be added condition
type // 1
opt
type // min
base // number - for example: 5 - this means bottom 5 items.
style // referred to the decorated color.
cbgc // cell background color if condition meet
ccolor // cell font color if condition meet
id // unique in this sheet file
To list bottom 10% value, the following json object need to be added into floatings. You can change base to get different top number.
{
sheet:1, // the sheet id of added condition
name:"condition1", // name should be unique
ftype:"cdt", // define condition type
json: "{\"name\":\"boolstyle\",\"rng\":[{\"span\":[1,2,2,4,4],\"type\":1}],\"opt\":{\"type\":\"bottom\",\"base\":10,\"style\":{\"cbgc\":\"rgb(248,105,107)\",\"ccolor\":\"rgb(150,0,0)\"}},\"id\":\"12345670\"}"
}
For each json object inside condition floating, it is a string with the following format.
name // boolstyle
rng
span // [sheetId, x1, y1, x2, y2] which define sheetId and a range of cells need to be added condition
type // 1
opt
type // bottom
base // percentage number - for example: 5 - this means bottom 5% items.
style // referred to the decorated color.
cbgc // cell background color if condition meet
ccolor // cell font color if condition meet
id // unique in this sheet file
To list above average value, the following json object need to be added into floatings.
{
sheet:1, // the sheet id of added condition
name:"condition1", // name should be unique
ftype:"cdt", // define condition type
json: "{\"name\":\"boolstyle\",\"rng\":[{\"span\":[1,2,2,4,4],\"type\":1}],\"opt\":{\"type\":\"average\",\"base\":0,\"style\":{\"cbgc\":\"rgb(248,105,107)\",\"ccolor\":\"rgb(150,0,0)\"}},\"id\":\"12345670\"}"
}
For each json object inside condition floating, it is a string with the following format.
name // boolstyle
rng
span // [sheetId, x1, y1, x2, y2] which define sheetId and a range of cells need to be added condition
type // 1
opt
type // average
base // 0 - this means above
style // referred to the decorated color.
cbgc // cell background color if condition meet
ccolor // cell font color if condition meet
id // unique in this sheet file
To list below average value, the following json object need to be added into floatings.
{
sheet:1, // the sheet id of added condition
name:"condition1", // name should be unique
ftype:"cdt", // define condition type
json: "{\"name\":\"boolstyle\",\"rng\":[{\"span\":[1,2,2,4,4],\"type\":1}],\"opt\":{\"type\":\"average\",\"base\":1,\"style\":{\"cbgc\":\"rgb(248,105,107)\",\"ccolor\":\"rgb(150,0,0)\"}},\"id\":\"12345670\"}"
}
For each json object inside condition floating, it is a string with the following format.
name // boolstyle
rng
span // [sheetId, x1, y1, x2, y2] which define sheetId and a range of cells need to be added condition
type // 1
opt
type // average
base // 1 - this means below
style // referred to the decorated color.
cbgc // cell background color if condition meet
ccolor // cell font color if condition meet
id // unique in this sheet file