Sample Code

Date occurring condition



In the method loadData of SheetAPI, it allows user add data ocurring 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":"date","base":0,"style":{"cbgc":"rgb(248,105,107)","ccolor":"rgb(150,0,0)"}},"id":"condition-3"}"
    }],
    cells: [
         {sheet: 1, row: 2, col: 2, json: {data:"2014-12-25", fm: "date", "dfm":"Y m d"}},
         {sheet: 1, row: 2, col: 3, json: {data:"2014-12-30", fm: "date", "dfm":"Y m d"}},
         {sheet: 1, row: 2, col: 4, json: {data:"2014-12-20", fm: "date", "dfm":"Y m d"}},
         {sheet: 1, row: 3, col: 2, json: {data:"2014-12-15", fm: "date", "dfm":"Y m d"}},
         {sheet: 1, row: 3, col: 3, json: {data:"2015-01-01", fm: "date", "dfm":"Y m d"}},
         {sheet: 1, row: 3, col: 4, json: {data:"2014-12-27", fm: "date", "dfm":"Y m d"}},
         {sheet: 1, row: 4, col: 2, json: {data:"2014-12-10", fm: "date", "dfm":"Y m d"}},
         {sheet: 1, row: 4, col: 3, json: {data:"2015-01-05", fm: "date", "dfm":"Y m d"}},
         {sheet: 1, row: 4, col: 4, json: {data:"2014-12-26", fm: "date", "dfm":"Y m d"}}
    ]
});
 

The above code will insert a range of cells from B2:D4 by adding date condition occurring at yesterday.

For condition, loadData json object includes at least 4 names: fileName, sheets, floatings and cells.

NameProperty ExampleDescription
File namefileName fileName: 'Example Sheet' Define the file name for this sheet file.
Sheet objectsheets sheets: [{},{}] Set defined sheet tabs information for this file.
Floating objectfloatings floatings: [{},{}] Set defined floatings information for this file.
Cell objectcells 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:"date",
     base: 0,
     style:{"cbgc":"rgb(248,105,107)","ccolor":"rgb(150,0,0)"} 
});
 

Occurring yesterday condition

To add date occurring yesterday condition, 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\":\"date\",\"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      // date 
      base      // 0 - yesterday
      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   
  

Occurring today condition

To add date occurring today condition, 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\":\"date\",\"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      // date 
      base      // 1 - today
      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 
  

Occurring tomorrow condition

To add date occurring tomorrow condition, 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\":\"date\",\"base\":2,\"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      // date 
      base      // 2 - tomorrow
      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 
  

Occurring last 7 days condition

To add date occurring last 7 days condition, 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\":\"date\",\"base\":3,\"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      // date 
      base      // 3 - last 7 days
      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 
  

Occurring last week condition

To add date occurring last week condition, 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\":\"date\",\"base\":4,\"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      // date 
      base      // 4 - last week
      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 
  

Occurring this week condition

To add date occurring this week condition, 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\":\"date\",\"base\":5,\"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      // date 
      base      // 5 - this week
      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 
  

Occurring next week condition

To add date occurring next week condition, 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\":\"date\",\"base\":6,\"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      // date 
      base      // 6 - next week
      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 
  

Occurring last month condition

To add date occurring last month condition, 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\":\"date\",\"base\":7,\"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      // date 
      base      // 7 - last month
      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 
  

Occurring this month condition

To add date occurring this month condition, 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\":\"date\",\"base\":8,\"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      // date 
      base      // 8 - this month
      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 
  

Occurring next month condition

To add date occurring next month condition, 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\":\"date\",\"base\":9,\"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      // date 
      base      // 9 - next month
      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 
  

 

 


Copyright © FeyaSoft Inc. All rights reserved.