Sample Code

Number validation

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

In the method loadData of SheetAPI, it allows user add number 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":0,"op":0,"min":120,"max":150,"hint":"between 120 and 150","allow":true},"id":"validation-1"}"
    }],
    cells: [
         {sheet: 1, row: 2, col: 2, json: {data:100}},
         {sheet: 1, row: 2, col: 3, json: {data:200}},
         {sheet: 1, row: 2, col: 4, json: {data:300}},
         {sheet: 1, row: 3, col: 2, json: {data:120}},
         {sheet: 1, row: 3, col: 3, json: {data:220}},
         {sheet: 1, row: 3, col: 4, json: {data:150}},
         {sheet: 1, row: 4, col: 2, json: {data:130}},
         {sheet: 1, row: 4, col: 3, json: {data:170}},
         {sheet: 1, row: 4, col: 4, json: {data:110}}
    ]
});

The above code will insert a range of cells from B2:D4 by adding number validation between 120 and 150.

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 validation

To add number 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:0,op:0,min:120,max:150,hint:\"Between 120 and 150\",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 0 if it is number validation
      op        // 0 means Between. 
                // 1 means Not Between. 
      min       // the minimum allowed number
      max       // the maximum allowed number   
      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

Less, less and equal, greater, greater and equal, equal, not equal validation

To add number less than 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:0,op:2,num:120,hint:\"less than 120\",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 0 if it is number validation
      op        // 2 means Less than. 
                // 3 means Less than and Equal. 
                // 4 means Greater than. 
                // 5 means Greater than and Equal. 
                // 6 means Equal. 
                // 7 means Not Equal.
      num       // the number 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