Sample Code

Data bar condition

Document -> Sample Code for Using APIs -> Sample code for the condition -> Data bar condition

In the method loadData of SheetAPI, it allows user add data bar 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":"colorbar","rng":[{"span":[1,2,2,4,4],"type":1}],"opt":{"pos":"rgb(248,105,107)","neg":"rgb(150,0,0)"},"id":"condition-5"}"
    }],
    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 colorful bar condition.

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, 3, 3, 5, 3]], 'colorbar', {
    neg: "rgb(255,127,0)",
    pos: "rgb(0,128,255)"
});

More detail

To add colorful bar, the following json object need to be added into floatings.

{
     sheet:1,             // the sheet id of added condition
     name:"condition10",   // name should be unique
     ftype:"cdt",         // define condition type
     json: "{\"name\":\"colorbar\",\"rng\":[{\"span\":[1,2,2,4,4],\"type\":1}],\"opt\":{\"pos\":\"rgb(255,128,0)\",\"neg\":\"rgb(0,127,255)\"},\"id\":\"12345670\"}"
  }

For each json object inside condition floating, it is a string with the following format.


  name       // colorbar
  rng
      span      // [sheetId, x1, y1, x2, y2] which define sheetId and a range of cells need to be added condition
      type      // 1
  opt        
      pos       // positive number color  
      neg       // negative number color  
  id         // unique in this sheet file