Sample Code

Date validation

Document -> Sample Code for Using APIs -> Sample code for validation -> Date validation

In the method loadData of SheetAPI, it allows user add date validation to the sheet. .

Here comes the code example


SHEET_API.loadData(SHEET_API_HD, {
    fileName: 'Validation Json File',
    sheets: [{
        name: 'First tab',
        color: 'orange',
        id: 1
    },{
        name: 'Second tab',
        id: 2
    }],
    floatings: [{
         sheet:1, 
         name:"validation1",
         ftype:"cdt",
         json: "{"name":"vd","rng":[{"span":[1,2,2,4,4],"type":1}],"opt":{"dt":2,"op":0,"mind":"2015-01-09","maxd":"2015-01-12","hint":"Date between","allow":true},"id":"validation-1"}"
    }],
    cells: [
         {sheet: 1, row: 2, col: 2, json: {data:\"2015-01-11\",fm:\"date\",dfm:\"Y-m-d\"}},
         {sheet: 1, row: 2, col: 3, json: {data:\"2015-01-12\",fm:\"date\",dfm:\"Y-m-d\"}},
         {sheet: 1, row: 2, col: 4, json: {data:\"2015-02-01\",fm:\"date\",dfm:\"Y-m-d\"}},
         {sheet: 1, row: 3, col: 2, json: {data:\"2015-01-01\",fm:\"date\",dfm:\"Y-m-d\"}},
         {sheet: 1, row: 3, col: 3, json: {data:\"2015-01-17\",fm:\"date\",dfm:\"Y-m-d\"}},
         {sheet: 1, row: 3, col: 4, json: {data:\"2015-01-15\",fm:\"date\",dfm:\"Y-m-d\"}},
         {sheet: 1, row: 4, col: 2, json: {data:\"2015-01-10\",fm:\"date\",dfm:\"Y-m-d\"}},
         {sheet: 1, row: 4, col: 3, json: {data:\"2015-12-30\",fm:\"date\",dfm:\"Y-m-d\"}},
         {sheet: 1, row: 4, col: 4, json: {data:\"2015-01-06\",fm:\"date\",dfm:\"Y-m-d\"}}
    ]
});

The above code will insert a range of cells from B2:D4 by adding text validation.

For validation, 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.

Between or Not Between

To add date between or not between validation, the following json object need to be added into floatings.

{
     sheet:1,              // the sheet id of added condition
     name:"validation1",   // name should be unique
     ftype:"cdt",          // define validation type
     json: "{name:\"vd\",rng:[{span:[1,2,2,4,4],type:1}],opt:{dt:2,op:0,\"mind\":\"2014-01-09\",\"maxd\":\"2014-01-12\",hint:\"Date between\",allow:true}},\"id\":\"validation1\"}"
  }

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


  name       // vd
  span       // [sheetId, x1, y1, x2, y2] which define sheetId and a range of cells need to be added validation
  opt 
      dt        // data type - should be set as 2 if it is date validation
      op        // 0 means Date Between. 
                // 1 means Not Between. 
      mind      // the minimum date to be compared.
      maxd      // the maximum date to be compared.
      hint      // the display message for invalid data
      allow     // true or false. If it is true, it means allow enter invalid data, but show hint if it is invalid.
      ignoreBlank  // true - default is false if it is not set. If cell is blank, not apply validation if ignoreBlank is set true.
  id         // unique in this sheet file

Is valid date

To add date validation, the following json object need to be added into floatings.

{
     sheet:1,              // the sheet id of added condition
     name:"validation1",   // name should be unique
     ftype:"cdt",          // define validation type
     json: "{name:\"vd\",rng:[{span:[1,2,2,4,4],type:1}],opt:{dt:2,op:12,hint:\"Valid date\",allow:true}},\"id\":\"validation1\"}"
  }

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


  name       // vd
  span       // [sheetId, x1, y1, x2, y2] which define sheetId and a range of cells need to be added validation
  opt 
      dt        // data type - should be set as 2 if it is date validation
      op        // 12 means Is valid date. 
      hint      // the display message for invalid data
      allow     // true or false. If it is true, it means allow enter invalid data, but show hint if it is invalid.
      ignoreBlank  // true - default is false if it is not set. If cell is blank, not apply validation if ignoreBlank is set true.
  id         // unique in this sheet file

Equal, Before, On or Before, After, On or After

To add date validation, the following json object need to be added into floatings.

{
     sheet:1,              // the sheet id of added condition
     name:"validation1",   // name should be unique
     ftype:"cdt",          // define validation type
     json: "{name:\"vd\",rng:[{span:[1,2,2,4,4],type:1}],opt:{dt:2,op:6,date:\"2015-01-12\",hint:\"equal date\",allow:true}},\"id\":\"validation1\"}"
  }

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


  name       // vd
  span       // [sheetId, x1, y1, x2, y2] which define sheetId and a range of cells need to be added validation
  opt 
      dt        // data type - should be set as 2 if it is date validation
      op        // 6 means Equal the compared date. 
                // 13 means Before the compared date. 
                // 14 means On or Before the compared date. 
                // 15 means After the compared date. 
                // 16 means On or After the compared date. 
      date      // the compared date
      hint      // the display message for invalid data
      allow     // true or false. If it is true, it means allow enter invalid data, but show hint if it is invalid.
      ignoreBlank  // true - default is false if it is not set. If cell is blank, not apply validation if ignoreBlank is set true.
  id         // unique in this sheet file